Synchronize Identity Values Between Database and DataSet During Updates


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.


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:

@fname varchar(50),
@lname varchar(50),
@empid int output
Insert into employees(firstname,lastname) values(@fname,@lname);
set @empid=@@identity;

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.


Bipin Joshi is an independent software consultant and trainer by profession specializing in Microsoft web development technologies. Having embraced the Yoga way of life he is also a yoga mentor, meditation teacher, and spiritual guide to his students. He is a prolific author and writes regularly about software development and yoga on his websites. He is programming, meditating, writing, and teaching for over 27 years. To know more about his private online courses on ASP.NET and meditation go here and here.

Posted On : 11 January 2004

Tags : ADO.NET Data Access SQL Server