How to update Dataset Parent & Child tables with Autogenerated Identity Key?

Question

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

Answer 1

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:

• MSDN: Retrieving Identity or Autonumber Values (ADO.NET)
• MSDN: Managing an @@IDENTITY Crisis
• Retrieving Identity or Autonumber Values into Datasets
• C# Learnings: Updating identity columns in a Dataset

Answer 2

Couple of things to point out.

1. Yes, you definitely need relations assigned for both tables. You can check from xsd editor (double click your xsd file). By default the relation is set as 'relation only' which doesn't has any 'update rule'. Edit this relation by going into 'edit relation' and select 'Foreign Key Constraint Only' or 'Both~~~' one. And need to set 'Update Rule' as Cascade! 'Delete Rule' is up to you.

2. Now when you use a new parent table row's ID (AutoIncrement) for new child table rows as a foreign key, You have to add the parent row into the table first before you use the new parent row's ID around.

3. As soon as you call Update for the parent table using tableadapter, the associated child table's new rows will have correct parentID AUTOMATICALLY.

My simple code snippets:

'--- Make Parent Row
Dim drOrder as TOrderRow = myDS.TOder.NewTOrderRow
drOrder.SomeValue = "SomeValue"
myDS.TOrder.AddTOrderRow(drOrder) '===> THIS SHOULD BE DONE BEFORE CHILD ROWS

'--- Now Add Child Rows!!! there are multiple ways to add a row into tables....
myDS.TOrderDetail.AddTOrderDetailRow(drOrder, "detailValue1")
myDS.TOrderDetail.AddTOrderDetailRow(drOrder, "detailvalue2")
'....
'....

'--- Update Parent table first
myTableAdapterTOrder.Update(myDS.TOrder)
'--- As soon as you run this Update above for parent, the new parent row's AutoID(-1)
'--- will become real number given by SQL server. And also new rows in child table will
'--- have the updated parentID

'--- Now update child table
myTableAdapterTOrderDetail.Update(myDS.TOrderDetail)

I hope it helps!