I'm having some problem getting the value of SCOPE_IDENTITY() immediatly following a paremetrize query, but works fine after a straight hard coded command.
SAMPLE #1
// Insert command hard coded parameter
IDbCommand dbCommand = this.DataFactory.CreateCommand();
dbCommand.CommandType = CommandType.Text;
dbCommand.CommandText = string.Format(
"INSERT INTO [Severity] ([Descriptor]) VALUES ('{0}')",
ColumnNames.Descriptor);
// Execute the parametrize query
dbCommand.ExecuteNonQuery();
// Get the SCOPE_IDENTITY()
IDbCommand dbScopeCommand = this.DataFactory.CreateCommand();
dbScopeCommand.CommandType = CommandType.Text;
dbScopeCommand.CommandText = "SELECT SCOPE_IDENTITY()";
this.SeverityId = int.Parse(dbScopeCommand.ExecuteScalar().ToString());
----------------------------------------------------------------------
SAMPLE #2
// Insert command using a parametrized query
IDbCommand dbCommand = this.DataFactory.CreateCommand();
dbCommand.CommandType = CommandType.Text;
dbCommand.CommandText = "INSERT INTO [Severity] ([Descriptor]) VALUES (@p1)";
// Add the parameter
IDbDataParameter p = dbCommand.CreateParameter();
p.ParameterName = "@p1";
p.DbType = DbType.String;
p.Value = this.Descriptor;
p.Direction = ParameterDirection.Input;
dbCommand.Parameters.Add(p);
// Execute the parametrize query
dbCommand.ExecuteNonQuery();
// Get the SCOPE_IDENTITY()
IDbCommand dbScopeCommand = this.DataFactory.CreateCommand();
dbScopeCommand.CommandType = CommandType.Text;
dbScopeCommand.CommandText = "SELECT SCOPE_IDENTITY()";
this.SeverityId = int.Parse(dbScopeCommand.ExecuteScalar().ToString());
----------------------------------------------------------------------
In both cases the insert command runs with no problem BUT the SCOPE_IDENTITY() command will fail and will throw an "Input string was not in a correct format" with I run SAMPLE #2 using the parametrize query. WTF ??? The value returned in sample #2 is always 'null'.
Csla.DataPortalException: DataPortal.Update failed ---> Csla.Server.CallMethodException: DataPortal_Insert method call failed ---> System.FormatException: Input string was not in a correct format.
at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)
at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info)
at System.Int32.Parse(String s)
Any ideas?
BTW, if I use @@IDENTITY instead of SCOPE_IDENTITY() it works. But If I use @@IDENTITY I will run into other issues down the road with insert triggers an al - so SCOPE_IDENTITY() is by far the right solution.
Thanks
Okay, this one works but not very elegant in my opinion.
// Insert command using a parametrized query
IDbCommand dbCommand = this.DataFactory.CreateCommand();
dbCommand.CommandType = CommandType.Text;
dbCommand.CommandText = "INSERT INTO [Severity] ([Descriptor]) VALUES (@p1); SELECT SCOPE_IDENTITY()";
// Add the parameter
IDbDataParameter p = dbCommand.CreateParameter();
p.ParameterName = "@p1";
p.DbType = DbType.String;
p.Value = this.Descriptor;
p.Direction = ParameterDirection.Input;
dbCommand.Parameters.Add(p);
// Execute the parametrize query
dbCommand.ExecuteNonQuery();
this.SeverityId = int.Parse(dbCommand.ExecuteScalar().ToString());
-----------------------
What I did is very simple, i've combined both command into so that both are ran within the same scope context.
Is there a better way retain the scope context between 2 individual IDbCommand?
Thanks guys for your input here - it is appreciated.
ajj3085:
Is there a reason you're not using a stored procedure? I'm not sure why the commands in #2 aren't running in the same scope; perhaps parametizing things creates another context? If you can though I'd recommend putting the insert into an sp which has an output parameter through which you can pass the value from scope_identity().
The reason I'm not using store proc is that I'm currently writting a DAL that will enable our CSLA based application to connect to more than one database like SQL Server, Oracle, and Firebird just to name a few. I'm a big fan of code generation and the DAL layer is generated in .Net code only - so no store procs here. While this approach will bring forward some issues like the one I've just experienced, having the entire DAL generated in .Net code will allow for strongly typed data entity that maps back top the database tables and columns. Thus, having a compile error instead of a runtime error when the data schema is altered due to new features or other reasons.
Andy,
Sounds good, but what do you do when you encounter a data type not supported in both databases. For example a timestamp data type exists in SQL Server but does not not exists is Oracle. What did you do to get around this issue - or was it not an issue for you guys.
I got an answer to this question from Erland Sommarskog - a long time SQL Server MVP.
<quote>
Apparently, you send an unparameterised command
with ExecuteNonQuery, and in that case it will work. But if you would
starting using parameters, it would break.
scope_idenity() returns the most recently generated IDENTITY value in
the current scope. "scope" is a "stored procedure", "block of dynamic
SQL" etc. And the top level of a connection is also a scope.
As long as you don't use parameters, ADO .Net will send bare SQL commands
to SQL Server and they will thus execute in the top-level scope. But
if you use parameterised commands, ADO .Net implements this by calling
sp_executesql, and thus the INSERT commands excutes in a lower-level
scope, and thuse the call to scope_idenity will not return any value.
From this it may sound that using parameters with ADO .Net is a bad
idea, but in fact it is a very good idea. Parameterised commands
increases the chances for cache reuse, and decreases the risk for
the cache being cluttered. Parameterised commands is also the basic
protection against SQL injection.
</quote>
So you can send sequential commands across the same connection and it will work correctly - and a transaction uses the same connection so it works too.
Joe
Copyright (c) Marimer LLC