AutoIncrement in a disconnected DataSet

• Download source files - 28.1 Kb

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.