Synchronize Identity Values Between Database and DataSet During Updates

Problem

Imagine a case where you have fetched records from Employee table (Northwind database of SQL Server) in a DataSet for updating purpose. The Employee table is having a column called EmployeeID which is an identity column. You now added new rows to the DataTable through your application. Now you are ready to update the changes back to the database. Normally you will set InsertCommand property of DataAdapter to a SQL statement that inserts the rows to the database. However, there is one problem here. After inserting the rows in the database your DataTable does not automatically reflects the identity values of EmployeeID column as assigned by the database. You need to fill the DataSet again to get them back so that you can again bind various controls or use it in your code. This means there are two calls to the database - one to insert the rows and other to refill the dataset with latest values. This is certainly not a good thing especially when you are frequently updating your data and no. of users are more.

Solution

The above problem can be solved by the clever use of stored procedures and output parameters. Instead of using INSERT statement to update the database we create a stored procedure called InsertRow that looks as shown below:

CREATE PROCEDURE dbo.InsertRow
(
@fname varchar(50),
@lname varchar(50),
@empid int output
)
AS
Insert into employees(firstname,lastname) values(@fname,@lname);
set @empid=@@identity;
RETURN 

Here, we added an OUTPUT parameter @empid to the stored procedure. This parameter is set to the @@identity value after the insert is done. Since this an output parameter after calling the Update method of DataAdapter it automatically populates the EmployeeID column of the DataTable. This way in a single database call the DataTable identity values can be synchronized with the database values.

The complete source code of a sample application is included with this article for your reference.