Returning the identity value from SQL Server to .NET

David Hayden has a good post about getting the value of the identity column in a SQL Server after inserting data in .NET.  Basically you append ” SET @Key = SCOPE_IDENTITY()” to your insert statement, create a parameter for @Key, set the parameter’s direction to be Output, and read the value back.

Of special note is that he used the SQL Server Profiler to watch how a DataGridView did the operation.  The control ran the INSERT statement and then ran SELECT MyKey, MyField1, MyField2 FROM MyTable WHERE MyKey = SCOPE_IDENTITY().  That method also retrieves defaults, calculated fields, and fields affected by triggers so that your view in code matches what is in the database.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s