Click here to Skip to main content
11,805,035 members (64,293 online)
Click here to Skip to main content

AutoIncrement in a disconnected DataSet

, 25 Sep 2005 64.5K 600 28
Rate this:
Please Sign up or sign in to vote.
Working with disconnected DataSets that contain autoincrement count.


When you work with relational data you often have to work with DataSets. The .NET DataSet supports AutoIncrement columns. It's quite easy to manage updates to a single table with such an identity column. Problem arises when you try to add (insert) new records to a DataSet where the tables have parent-child relationships.

Even if a database is properly designed, you can encounter the following major problems when using DataSets:

  1. The order of updating the tables must be known (you cannot update the child, while there's no parent information in the database).
  2. When there are more client applications connected to the same database with disconnected DataSets, upon update the database will generate its own identity values - which may differ from the identity values in the disconnected DataSet. So, further updates to a child table may result in error or in corrupted data.


There were a few articles on this problem, but I couldn't find an elegant and easy to use solution. So, I decided to create some kind of a component - a base class, which is easy to use, and which presents a solution to this problem.

The ideas given in other articles were quiet OK, but they had some errors:

  • First, we don't need a stored procedure (with output parameter) to acquire the identity number generated by the SQL database.
  • Second, we don't have to use the OrdersDataAdapter_OnRow event to update the identities in our DataSets when using MS Access.

The next step

The solution is simple. I've generated a class, which has a connection string and a DataSet or DataTable as input parameter. This class will generate the SELECT, INSERT, UPDATE and DELETE statements for the table (by using SqlCommandBuilder).


The idea is to modify the INSERT statement so that we can acquire the identity generated by the database. For this we have to:

  • Find the AutoIncrement column in the table.
  • Add an output parameter @autoIdentity and extend the INSERT statement with : "select @autoIdentity = @@identity" - also we have to set the UpdateRowSource.Both parameter.

This solves the problem of AutoIncrement fields. If the parent field's AutoIncrement values are updated, this change is reflected in all the child foreign keys.

Order of update

I found an article (possibly on CodeProject) where someone was complaining about the update. The problem is that the update-order cannot be arbitrary. First the parent table must be updated, then the child. So, either you keep track of the relationships, or you create an algorithm which examines the correct order of update. This code was checked with the DataSet in picture, and also with a bigger system, where user info was stored. This problem was solved by using the DataTable.ParentRelations property.

The easiest solution:

  • First, find the master parents (parent with no parents).
  • Second, find the tables that can be updated (possible candidates are child tables of the master table). If a child table is found and can be updated, then try to find an updateable table inside the child of this table...(recursion).

So upon initialization, all the SQL queries and also the update order is calculated. I've finally added some functions:

  • Fill
  • Update

There is also a function to define filters for the fill (for the query). Also, a property IsUpToDate, which returns true, if the table is not modified (no changes after the fill).

Using the code

// init and fill
ds1 = new Dataset1();
ba1 = new BaseAccessor(
     System.Configuration.ConfigurationSettings.AppSettings["ConnStr"], ds1);
ba1.AddFilter(ds1.Orders, "uid = @i_a", new object[] {55});
ba1.AddFilter(ds1.OrderedItems, "orderUid = @i_a", new object[] {55});

// do here any inserts
// ...

// update

Points of interest

This class can be used as a base class for data manipulations.


This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


About the Author

Web Developer
Slovakia Slovakia
Since 1999 I work in IT. Worked 2-3 yrs with Borland Builder C++. Since .NET appeared, I program in C#, ASP.NET.

You may know the current technologies, but still there's a lot of experience to gain. IT's evolving all the time.

From 2006 I'm a MCP. Now I'm focusing on technologies like: NHibernate, NSpring...

You may also be interested in...

Comments and Discussions

GeneralmySql conversion Pin
smitty31877-Jul-09 12:34
membersmitty31877-Jul-09 12:34 
Generaldatabase tables Pin
Member 126058029-Sep-08 22:44
memberMember 126058029-Sep-08 22:44 
GeneralRe: database tables Pin
Member 126058030-Sep-08 14:15
memberMember 126058030-Sep-08 14:15 
GeneralRe: database tables Pin
Member 126058030-Sep-08 15:15
memberMember 126058030-Sep-08 15:15 
QuestionUnique constraint Pin
Mike Gentle1-Oct-06 21:51
memberMike Gentle1-Oct-06 21:51 
QuestionIs it useful at all ? Pin
mikali9-Oct-05 19:12
membermikali9-Oct-05 19:12 
AnswerRe: Is it useful at all ? Pin
balazs_hideghety11-Oct-05 8:36
memberbalazs_hideghety11-Oct-05 8:36 
Generalselect @autoIdentity = @@identity Pin
kryzchek26-Sep-05 9:28
memberkryzchek26-Sep-05 9:28 
GeneralRe: select @autoIdentity = @@identity Pin
balazs_hideghety26-Sep-05 21:07
memberbalazs_hideghety26-Sep-05 21:07 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.151002.1 | Last Updated 26 Sep 2005
Article Copyright 2005 by balazs_hideghety
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid