Click here to Skip to main content
Click here to Skip to main content

AutoIncrement in a disconnected DataSet

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

Introduction

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.

Background

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).

AutoIncrement

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});
ba1.Fill();

// do here any inserts
// ...

// update
ba1.Update();

Points of interest

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

License

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

Share

About the Author

balazs_hideghety
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...

Comments and Discussions

 
GeneralmySql conversion Pinmembersmitty31877-Jul-09 13:34 
Generaldatabase tables PinmemberMember 126058029-Sep-08 23:44 
GeneralRe: database tables PinmemberMember 126058030-Sep-08 15:15 
GeneralRe: database tables PinmemberMember 126058030-Sep-08 16:15 
QuestionUnique constraint PinmemberMike Gentle1-Oct-06 22:51 
QuestionIs it useful at all ? Pinmembermikali9-Oct-05 20:12 
AnswerRe: Is it useful at all ? Pinmemberbalazs_hideghety11-Oct-05 9:36 
Generalselect @autoIdentity = @@identity Pinmemberkryzchek26-Sep-05 10:28 
GeneralRe: select @autoIdentity = @@identity Pinmemberbalazs_hideghety26-Sep-05 22: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.150327.1 | Last Updated 26 Sep 2005
Article Copyright 2005 by balazs_hideghety
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid