CODEWITHSUNDEEP
c#sqlcompact-frameworksql-server-ce
meds
meds

Reputation: 22966

Getting the primary key of a newly inserted row in SQL Server CE and C#?

I'm doing an insert into a SQL Server CE table where the primary key is auto-generated, how would I go about getting the primary key after the insertion completes?

Upvotes: 2

Views: 5229

Answers (3)

vijay
vijay

Reputation: 58

you can find your primary key is an identity field from table

SELECT IDENT_CURRENT(‘tablename’)

http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

Upvotes: -1

David Thielen
David Thielen

Reputation: 33046

You have to be careful doing this. What you need to do is in a transaction set your select to:

IDbCommand.CommandText += "; select scope_identity()";
object rtn = cmd.ExecuteScalar();
return (long)(decimal)rtn;

A single select inside a transaction is key because calling select scope_identity() is not the primary key of your insert, it is the most recent insert. So you can otherwise end up with the primary key of another insert that occurred after your insert, but before your request for the most recent PK insert.

Note: The scope_identity() may (repeat may) now be tied to the IDbCommand, but in the past it was not and following the above is safe.

Upvotes: -1

keith
keith

Reputation: 3110

Assuming your primary key is an identity field, you can use the query SELECT @@IDENTITY after you insert your new row.

Upvotes: 7

Related Questions