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.
Bipin Joshi is an independent software consultant, trainer, author, yoga mentor, and meditation teacher. He has been programming, meditating, and teaching for 24+ years. He conducts instructor-led
online training courses in ASP.NET family of technologies for individuals and small groups. He is a published author and has authored or co-authored books for Apress and Wrox press. Having embraced the Yoga way of life he also teaches
Ajapa Yoga to interested individuals. To know more about him click
here.
Get connected :
Facebook Twitter LinkedIn YouTube