.net – How to update Dataset Parent & Child tables with Autogenerated Identity Key

ado.netdatasetnetvb.net

I am using ADO.NET Datasets in my VB Applications. I have a typed dataset with one Parent table and many child tables. I want to generate Identity Key when I insert data into Parent Table and then update the data in all child tables with the Same key (As Foregin key).

At last, I want to update the dataset in Database(SQL Server08).

Well, the above thing can be possible by first inserting Parent Table in Database directly, get the Identity column and than use to for Child tables.

But I want to this as an automatic operation (Like LINQ to SQL which takes care of Primary & Foreign key in datacontext.)

I such thing possible in Dataset which takes care of Autogenerated column for Parent and child tables?

Thanks,

ABB

Best Answer

I think this should be more obvious and should work without any tweaking. But still, it's pretty easy.

The solution has two parts:

  1. Create DataRelation between child and parent tables and set it to cascade on updates. That way whenever parent Id changes, all children will be updated.

    Dim rel = ds.Relations.Add(parentTab.Columns("Id"), childTab.Columns("ParentId"))
rel.ChildKeyConstraint.UpdateRule = Rule.Cascade

  2. Dataset insert and update commands are two-way: If there are any output parameters bound or any data rows returned, they will be used to update dataset row that caused the update.

    This is most useful for this particular problem: getting autogenerated columns back to application. Apart from identity this might be for example a timestamp column. But identity is most useful.

    All we need to do is set insert command to return identity. There are several ways to do it, for example:

    a) Using stored procedure with output parameter. This is the most portable way among "real" databases.

    b) Using multiple SQL statements, with last one returning inserted row. This is AFAIK specific to SQL Server, but the simplest:

    insert into Parent (Col1, Col2, ...) values (@Col1, @Col2, ...);
select * from Parent where Id = SCOPE_IDENTITY();

After setting this up, all you need to do is create parent rows with Ids that are unique (within single dataset) but impossible in the database. Negative numbers are usually a good choice. Then, when you save dataset changes to database, all new parent rows will get real Ids from database.

Note: If you happen to work with database without multiple statements supports and without stored procedures (e.g. Access), you will need to setup event handler on RowUpdated event in parent table adapter. In the hanler you need to get identity with select @@IDENTITY command.

Some links:

Related Topic