Sql Server SCOPE_IDENTITY()

[Carlo Razzeto]

Hello, I'm currenly in the process of writing a Database abstraction class
that will support MySql, MS-SQL and OLEDB. In the abstract base class, I
have a public read only property LastAutoID UInt64 deffined. The field
behind this property is updated every time the end program calls the
ExecuteNonQuery() method (directly or indirectly).

My problem is that the SqlServer specific class is failing to retrive the
auto id, for some reason the query keeps returning DB null when I try to
populate the auto id field, forcing me to set 0. So far I've used the Sql
Server Profiler utility to confirm the fact that indeed, when I execute the
SCOPE_IDENTITY query it is on the same connection as the INSERT query (which
makes sense as by default my class disables Connection Pooling and enables a
Singleton pattern, never closes a connection unless requested by the end
programmer and reference count on the connection buffer is 0).

Now, for the wierd part... If I use the @@IDENTITY method in the query, I
get the auto number. Now, here's everything else that might be important.

First, after doing some reasearch I found that it is adventagous to cast the
result of the SCOPE_IDENTITY method as an int, so I tried that and casting
it as a bigint with no results.

Next, I realized that in my testing I was not specificlly naming at table, I
was just opening a connection and specifying the table using
<database>..<table>, so I switched over to connecting specifically to the
database and not specifying the database in the insert query, however that
produced no better results.

I've also posted this question to
microsoft.public.sqlserver.programming and gotten good advice, however
nothing that helps.

 

[Sahil Malik [MVP C#]]

Well, first of all, inventing your own connection pooling mechanism is a bad
design for multiple reasons. They are written down in various places in my
book, or search my blog for "Global SqlConnection is bad".

Secondly, it's a bit difficult to comment without looking at your actual
code. From the very little I know of your code, I'd recommend trying
replacing ExecuteNonQuery with ExecuteScalar.
Also, do you have any triggers defined on the table? What else depends on
that table at the database level?

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
__________________________________________________________

 

[Carlo Razzeto]

No triggers or anything like that, just a table with an auto-identity. I
chose to seceratly populate the field when the user calls ExecuteNonQuery()
because I beleive that this is when it most likely a new auto id will be
generated. Internally right now I use ExecuteReader ATM, but execute scalar
would work as well. If you are interested in looking at the code here is a
link.

http://euclid.nmu.edu/~crazzeto/code/DBProvider/

 

[AMDRIT]

SCOPE_INDENTITY() was intended to be called in the same call as the Insert
or Update, while @@Identity will retirive the last identity updated in your
connection (across all tables.) By executing a reader after the insert or
update, you have changed the scope and will therefore recieve a null value.
If you execute the reader and issue a select @@Identity, you will get the
last identity updated on any scope, sesssion and/or table since your call
(Perhaps the best reason for a stored procedure.)

If you use an executescalar as Sahil is suggesting then you would be able to
call a stored procedure and return the scope_identity(). ExecuteNonQuery()
will just return the rows affected, ExecuteReader changes the scope.
ExecuteScalar is the happy combination of the two.

ms-help://MS.VSCC.2003/MS.MSDNQTR.2003FEB.1033/cpref/html/frlrfSystemDataIDbCommandClassExecuteNonQueryTopic.htm
Executes an SQL statement against the Connection object of a .NET Framework
data provider, and returns the number of rows affected.

ms-help://MS.VSCC.2003/MS.MSDNQTR.2003FEB.1033/cpref/html/frlrfSystemDataIDbCommandClassExecuteScalarTopic.htm
Executes the query, and returns the first column of the first row in the
resultset returned by the query. Extra columns or rows are ignored.

SCOPE_IDENTITY
Returns the last IDENTITY value inserted into an IDENTITY column in the same
scope. A scope is a module -- a stored procedure, trigger, function, or
batch. Thus, two statements are in the same scope if they are in the same
stored procedure, function, or batch.

@@IDENTITY
Returns the last-inserted identity value.

IDENT_CURRENT
Returns the last identity value generated for a specified table in any
session and any scope.

 

[Guest]

SCOPE_IDENTITY() cannot be called after the fact ... period. It MUST, yes
MUST, be called in the same call batch. This means you cannot
ExecuteNonQuery() and come back and get the identity value.

You CAN use @@IDENTITY, but prayer is good when you head this route, as
someone else could come in between your call and your asking for identity
value, unless you wrap it in a transaction that locks the table. If you think
this one through, locking is a bad idea for a number of reasons.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

 

[Carlo Razzeto]

Thanks cowboy and AMDRIT for coming back with this information. I was
suspecting this but could not find the documetation to prove it. Kind of
annoying that MS works this like this, but I guess you have to work with
what you got. At least now I know what direction to go in with my
abstraction library.

 

[Otis Mukinfus]

Hello, I'm currenly in the process of writing a Database abstraction class
that will support MySql, MS-SQL and OLEDB. In the abstract base class, I
have a public read only property LastAutoID UInt64 deffined. The field
behind this property is updated every time the end program calls the
ExecuteNonQuery() method (directly or indirectly).

My problem is that the SqlServer specific class is failing to retrive the
auto id, for some reason the query keeps returning DB null when I try to
populate the auto id field, forcing me to set 0. So far I've used the Sql
Server Profiler utility to confirm the fact that indeed, when I execute the
SCOPE_IDENTITY query it is on the same connection as the INSERT query (which
makes sense as by default my class disables Connection Pooling and enables a
Singleton pattern, never closes a connection unless requested by the end
programmer and reference count on the connection buffer is 0).

Now, for the wierd part... If I use the @@IDENTITY method in the query, I
get the auto number. Now, here's everything else that might be important.

First, after doing some reasearch I found that it is adventagous to cast the
result of the SCOPE_IDENTITY method as an int, so I tried that and casting
it as a bigint with no results.

Next, I realized that in my testing I was not specificlly naming at table, I
was just opening a connection and specifying the table using
<database>..<table>, so I switched over to connecting specifically to the
database and not specifying the database in the insert query, however that
produced no better results.

I've also posted this question to
microsoft.public.sqlserver.programming and gotten good advice, however
nothing that helps.

Does this not work?

private Person _insertPerson(Person person)
{
SqlConnection cn = new SqlConnection(
@"Data Source=luke\;Initial Catalog=Test;Integrated
Security=True");
SqlCommand cmd = new SqlCommand("InsertPerson", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@LastName", person.LastName);
cmd.Parameters.AddWithValue("@Firstname", person.FirstName);
cmd.Parameters.AddWithValue("@MiddleName", person.MiddleName);
cmd.Parameters.Add("@Return", System.Data.SqlDbType.Int);
cmd.Parameters["@Return"].Direction =
ParameterDirection.ReturnValue;
cn.Open();
cmd.ExecuteNonQuery();
person.ID = (int)cmd.Parameters["@Return"].Value;
cn.Close();
return person;
}


Otis Mukinfus
http://www.otismukinfus.com
http://www.tomchilders.com

 

[David Sceppa [MSFT]]

SCOPE_IDENTITY() cannot be called after the fact ... period. It MUST,
yes MUST, be called in the same call batch. This means you cannot
ExecuteNonQuery() and come back and get the identity value.

Maybe I missed something. The following code works for me w/ SQL
Server 2000:

using (SqlConnection cn = new SqlConnection(strConn))
{
cn.Open();
SqlCommand cmd = cn.CreateCommand();
cmd.CommandText = "INSERT INTO Orders (CustomerID, OrderDate) " +
" VALUES ('ALFKI', GetDate())";
cmd.ExecuteNonQuery();

cmd.CommandText = "SELECT SCOPE_IDENTITY()";
Console.WriteLine(cmd.ExecuteScalar());
}

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2006 Microsoft Corporation. All rights reserved.

 

[Otis Mukinfus]

Maybe I missed something. The following code works for me w/ SQL
Server 2000:

using (SqlConnection cn = new SqlConnection(strConn))
{
cn.Open();
SqlCommand cmd = cn.CreateCommand();
cmd.CommandText = "INSERT INTO Orders (CustomerID, OrderDate) " +
" VALUES ('ALFKI', GetDate())";
cmd.ExecuteNonQuery();

cmd.CommandText = "SELECT SCOPE_IDENTITY()";
Console.WriteLine(cmd.ExecuteScalar());
}

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2006 Microsoft Corporation. All rights reserved.

David,

I have two copies of your first book on ADO.NET. One at home and one at the
office. Very good book.

Question:

What is the reason for doing it the way you do above instead of getting it back
with an output parameter from the same stored procedure? Just curious.

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com

 

[AMDRIT]

The original poster had stated that he was implementing a singleton pattern
for data connection. Compare the two code snippets, notice that snippet
works as you suggest, while the single instance (snippet 1) doesn't produce
expected results.


Dim strConn As String = "Data Source=localhost;Initial
Catalog=master;User ID=myuser;Password=mypassword;Persist Security
Info=False;"
Dim cn As Data.SqlClient.SqlConnection

cn = New Data.SqlClient.SqlConnection(strConn)

Dim sSQL1 As String = "CREATE TABLE #Orders ( OrderID smallint
IDENTITY(1, 1) PRIMARY KEY CLUSTERED, CustomerID varchar(32), OrderDate
datetime)"
Dim sSQL2 As String = "DROP TABLE #Orders"

Using cn
cn.Open()
Dim cmdFirst As Data.SqlClient.SqlCommand = cn.CreateCommand
Dim cmdSecond As Data.SqlClient.SqlCommand = cn.CreateCommand

cmdFirst.CommandText = sSQL1
cmdFirst.ExecuteNonQuery()

cmdFirst.CommandText = "INSERT INTO #Orders (CustomerID, OrderDate)
VALUES ('" & System.Guid.NewGuid.ToString("N") & "', GetDate())"
cmdFirst.ExecuteNonQuery()

cmdSecond.CommandText = "INSERT INTO #Orders (CustomerID, OrderDate)
VALUES ('" & System.Guid.NewGuid.ToString("N") & "', GetDate())"
cmdSecond.ExecuteNonQuery()

cmdFirst.CommandText = "SELECT SCOPE_IDENTITY()"
Console.WriteLine(cmdFirst.ExecuteScalar())

cmdSecond.CommandText = "SELECT SCOPE_IDENTITY()"
Console.WriteLine(cmdSecond.ExecuteScalar())

cmdSecond.CommandText = sSQL2
cmdSecond.ExecuteNonQuery()

End Using


---=== Snippet 2 ===---

Dim strConn As String = "Data Source=localhost;Initial
Catalog=master;User ID=myuser;Password=mypassword;Persist Security
Info=False;"
Dim cn1 As Data.SqlClient.SqlConnection
cn1 = New Data.SqlClient.SqlConnection(strConn)

Dim cn2 As Data.SqlClient.SqlConnection
cn2 = New Data.SqlClient.SqlConnection(strConn)

Dim sSQL1 As String = "CREATE TABLE ##Orders ( OrderID smallint
IDENTITY(1, 1) PRIMARY KEY CLUSTERED, CustomerID varchar(32), OrderDate
datetime)"
Dim sSQL2 As String = "DROP TABLE ##Orders"


cn1.Open()
Dim cmdFirst As Data.SqlClient.SqlCommand = cn1.CreateCommand

cn2.Open()
Dim cmdSecond As Data.SqlClient.SqlCommand = cn2.CreateCommand

cmdFirst.CommandText = sSQL1
cmdFirst.ExecuteNonQuery()

cmdFirst.CommandText = "INSERT INTO ##Orders (CustomerID, OrderDate)
VALUES ('" & System.Guid.NewGuid.ToString("N") & "', GetDate())"
cmdFirst.ExecuteNonQuery()

cmdSecond.CommandText = "INSERT INTO ##Orders (CustomerID, OrderDate)
VALUES ('" & System.Guid.NewGuid.ToString("N") & "', GetDate())"
cmdSecond.ExecuteNonQuery()

cmdSecond.CommandText = "SELECT SCOPE_IDENTITY()"
Console.WriteLine(cmdSecond.ExecuteScalar())

cmdFirst.CommandText = "SELECT SCOPE_IDENTITY()"
Console.WriteLine(cmdFirst.ExecuteScalar())

cmdSecond.CommandText = sSQL2
cmdSecond.ExecuteNonQuery()

cn1.Dispose()
cn2.Dispose()

SCOPE_IDENTITY() cannot be called after the fact ... period. It MUST,
yes MUST, be called in the same call batch. This means you cannot
ExecuteNonQuery() and come back and get the identity value.

Maybe I missed something. The following code works for me w/ SQL
Server 2000:

using (SqlConnection cn = new SqlConnection(strConn))
{
cn.Open();
SqlCommand cmd = cn.CreateCommand();
cmd.CommandText = "INSERT INTO Orders (CustomerID, OrderDate) " +
" VALUES ('ALFKI', GetDate())";
cmd.ExecuteNonQuery();

cmd.CommandText = "SELECT SCOPE_IDENTITY()";
Console.WriteLine(cmd.ExecuteScalar());
}

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2006 Microsoft Corporation. All rights reserved.

 

[David Sceppa [MSFT]]

I was merely disagreeing with the statement that SCOPE_IDENTITY could
not be used in a separate call.

In the code samples you provided, SCOPE_IDENTITY is behaving
appropriately in both cases, returning the last identity value generated
for the current session. As you pointed out, the original poster's
scenario uses a single connection. In that scenario, if you need to
retrieve the identity value generated for each row, you should retrieve the
identity value generated for the first row before submitting the second.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2006 Microsoft Corporation. All rights reserved.

 

[David Sceppa [MSFT]]

Otis,

Thank you for the kind words.

As you noted, you could also use a stored procedure to handle the
scenario. I used the separate queries to demonstrate that you could call
SCOPE_IDENTITY in a separate call after submitting the new row. A previous
post had stated that this was not possible, though I may have misunderstood.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2006 Microsoft Corporation. All rights reserved.