ADO.NET 3.5 Cookbook, 2nd Edition

5.2. Getting an Identity Column Value from SQL Server

Problem

When you add a row into a SQL Server table that has an identity column, the value assigned to the column in the DataTable is replaced by a value generated by the database. You need to retrieve the new value to keep the DataTable synchronized with the database.

Solution

There are two ways to synchronize identity values generated by the data source: use either the first returned record or the output parameters of a stored procedure. This solution demonstrates both approaches.

The solution uses one table named GetSqlServerIdentityValue with the schema shown in Figure 5-2. The Id column is an identity column with a seed of 1 and an increment of 1.

Figure 5-2. Schema of table GetSqlServerIdentityValue

The T-SQL DDL to create the table GetSqlServerIdentityValue is shown in Example 5-2.

Example 5-2. Create table GetSqlServerIdentityValue

USE AdoDotNet35Cookbook
GO
CREATE TABLE GetSqlServerIdentityValue (
    Id int IDENTITY(1,1) NOT NULL,
    Field1 nvarchar(50) NULL,
    Field2 nvarchar(50) NULL,
  CONSTRAINT PK_GetSqlServerIdentityValue PRIMARY KEY CLUSTERED
    ( Id ASC )
)

For the purposes of this example, a single record is added to the GetSqlServerIdentityValue table. The T-SQL to add the initial record into the table GetSqlServerIdentityValue is shown in Example 5-3.

Example 5-3. Insert initial records into table GetServerIdentityValue

USE AdoDotNet35Cookbook ...