I have a typed-dataset with a dozen or so tables which are hooked together with typical on-to-many and many-to-many relationships. I'm using table adapters to do the CRUD actions.

For updates I was using:

//Update parent
tableadapter_parent.Update(dataset.parenttable);

//Update child
tableadapter_child.Update(dataset.childtable);

But I am getting foreign key reference errors when deleting records because the adapter is trying to delete a row from a parent table but it can't because there is a FK refrence to it in a child table.  But if I reverse the order of the table adapter update calls so child tables are updated before parent tables then the deletes work but the inserts fail because it can't insert a FK reference into a child if it doesn't first exist in the parent.

So I tried the code below; as recommended, I structured it to perform the deletes from child tables first. Then inserts and updates to parent tables. Then insert and update to child tables.  But the insert/update part kills data integrity because, for example, on an parent table insert the primary key value that is actually added to the database may be different than the one supplied by the dataset parent table. So now that the PK for the parent record is different in the database when the child updates it supplies it's FK value (which happens to be obsolete) which references the wrong parent record.

//Delete from child
myChildTable_deletedRecords = dataset.childtable.GetChanges(DataRowState.Deleted);
if(myChildTable_deletedRecords != null)
   tableadapter_child.Update(myChildTable_deletedRecords);

//Delete from parent
myParentTable_deletedRecords = dataset.parenttable.GetChanges(DataRowState.Deleted);
if(myParentTable_deletedRecords != null)
  tableadapter_parent.Update(myParentTable_deletedRecords);

//Insert-Update parent
myParentTable_newRecords = dataset.parenttable.GetChanges(DataRowState.Added);
if(myParentTable_newRecords != null)
  tableadapter_parent.Update(myParentTable_newRecords);
myParentTable_modifiedRecords = dataset.parenttable.GetChanges(DataRowState.Modifed);
if(myParentTable_modifiedRecords != null)
  tableadapter_parent.Update(myParentTable_ModifiedRecords);

//Insert-Update child
myChildTable_newRecords = dataset.childtable.GetChanges(DataRowState.Added);
if(myChildTable_newRecords != null)
  tableadapter_child.Update(myChildTable_newRecords);
myChildTable_modifiedRecords = dataset.childtable.GetChanges(DataRowState.Modified);
if(myChildTable_modifiedRecords != null)
  tableadapter_child.Update(myChildTable_ModifiedRecords);

When I tried a hybrid of the two by explicitly performing deletes such as in the bottom example then performing updates as in the top example I am got delete errors when deleting (error like "delete command updated 0 of expected 1 record").

So, how to I structure my code to perform the tableadapter.Updates to handle inserts, updates and deletes?

Thanks!

In order to do deletes the table adapter update commands need to be called individually for deletes, inserts, and modifications.  However when I put the code in to do this I get insert errors. The deletes work fine. It's the inserts of new data that fail. They fail because the dataset tables get out of sync with the database.

As I understand it, here are the list of steps occurring:
 
1) The app gives the db a parent record with id 5 to insert
2) The db inserts the parent record but with an id of  6 (because 5 was taken)
3) The db gives the app the newly inserted parent record id of 6
4) The app gives the db a child record with parent FK of  5
5) The update fails - or data integrity is gone.
 
My current update code is missing a critical step between steps 3 and 4.  In this example, after step 3 the dataset should update the child datatable's parent FKs to 6.   Code that simply calls tableadapter update on the entire datatable handles this automatically, but you can't do deletes and inserts with it at the same time for the reason mentioned above.

So how do I sync the child FKs to any modified parent PKs?

Thanks

Philip,

First off, I'd recommend avoiding using DataTable.GetChanges for submitting the pending changes, if possible. GetChanges creates a new DataTable with copies of the modified rows from the original DataTable. Submitting updates in this fashion does not mark the modified rows in the original DataTable as unchanged at the end of the call to Update. Using this approach can also cause problems when working with auto-increment columns. GetChanges is more helpful in scenarios involving WebServices, or other components that require passing the contents of the DataSet into another process.

A better option is to use the overloaded Select method on the DataTable, which will return an array of DataRows, without making a separate copy of the DataRows. The Select method accepts parameters for a filter (such as "WHERE Country = 'Canada'"), a sort order (such as "City DESC") and row states. You can pass empty strings for the first two parameters if you are not interested in using a filter or sort order. You can use this method as shown here to return just the deleted rows:

DataTable.Select("", "", DataViewRowState.Deleted)

The Update method on the DataAdapter and TableAdapter classes is overloaded to handle an array of DataRows, so your code would look like:

//Delete from child
tableadapter_child.Update(myChildTable.Select("", "",
                          DataViewRowState.Deleted);

It sounds like you have the logic (top-down for inserts and updates, bottom-up for deletes) correct. I didn't see a reason for the DBConcurrencyException you described.

Now, getting back to the auto-increment issue.  If you're working with a SQL Server database, the TableAdapter Configuration Wizard should have auto-configured the TableAdapter to retrieve the new auto-increment value after submitting the change to the database. If you're working with similar functionality for another database (Jet or MySQL auto-increment columns, Oracle sequences, etc.), you'll need to supply your own logic to retrieve this information.

By using DataTable.Select rather than DataTable.GetChanges, the newly retrieved auto-increment values will be stored in your original DataSet rather than a seprate copy that contains only the changed rows. Once you've retrieved these values into your DataSet, the goal is to have those new values cascade down to the related child rows.

The strongly typed DataSet automatically creates DataRelations to help you move from parent rows to child rows (or from child rows to parent rows). However, the wizards that create these DataRelations in Visual Studio .NET 2005 do not create ForeignKeyConstraints for the DataRelations. It's really the ForeignKeyConstraint that handles cascading changes to related rows.

To create a ForeignKeyConstraint for your DataRelation, select the DataRelation in the strongly typed DataSet designer and edit the DataRelation (by double-clicking on the DataRelation, or using the Visual Studio .NET menu). On the dialog that appears, select "Both Relation and Foreign Key Constraint" and set the Update and Delete Rules to Cascade.

Now when your TableAdapter retrieves new auto-increment values after submitting pending inserted parent rows, those new values will automatically cascade down to the related child rows.

I'd also strongly recommend having ADO.NET generate negative placeholder values for auto-increment columns for pending inserts (-1, -2, -3, ...). Select the auto-increment column in the strongly typed DataSet designer. Then, in the Properties window, set the AutoIncrementSeed and AutoIncrementStep properties to -1. This approach ensures that the placeholder values that ADO.NET generates will not conflict with values that already exist in the database. Another benefit to this approach is that it avoids the possibility of violating constraints within the DataSet.

I hope this information proves helpful.

Works like a charm! Thank you David.  This is exactly what we were looking for. 

You may want to talk to whoever's incharge of the following document as it
had us going down the wrong road for several weeks.

http://msdn2.microsoft.com/en-us/library/ms171933.aspx

Hi David,

This is a very good solution.

How do can add transaction control to it? For example, after one table's data is updated, and its tableadapter marks those rows from added to unchanged. But then for some reason, the whole transaction rolls back. How do we reset the rows in the first dataset back to added and inform the user?

Thanks.

Eric

The DataAdapter (or TableAdapter) implicitly calls the AcceptChanges method on a DataRow if it determines that it successfully submitted the pending changes in that DataRow.

I hope this information proves helpful.

The DataSet is unaware of the transaction. If you might need to "roll back" the DataSet to a known state, you'll need to save the state when you start the transaction. You can save it to another variable using Copy, or write the contents to a file using WriteXml, etc. If you roll back the transaction, you'll need to use the copy of the DataSet you stored.

I hope this information proves helpful.

I was struggling on this until I found the new TransactionScope

I wrapped the various tableadapter Update calls inside a

using (System.Transactions.TransactionScope trans = new System.Transactions.TransactionScope())

{

// do my tableadapter updates here

}

worked for me

ok, I followed your advice and here's what happened - I've got a simple parent-child dataset. I insert a parent row and a child row. When I do the tableadapter.update(ParentTable.select("","",dataviewrowstate.added) it clears the .added flag for the ChildTable. The upshot is, the parent gets added but the child doesn't.

Any ideas?

John,

It sounds like the ForeignKeyConstraint that's associated with your DataRelation has its AcceptRejectRule property set to Cascade.

The DataAdapter (and TableAdapter) implicitly calls AcceptChanges on a DataRow during Update if it determines that it successfully submitted the pending change stored in that DataRow. This helps make sure that multiple calls to DataAdapter.Update won't try to submit the same pending changes multiple times. As a result, the RowState of the DataRow will be set to Unchanged after submitting the pending insert.

The ForeignKeyConstraint class' AcceptRejectRule will cascade the call to AcceptChanges (or RejectChanges) if the property is set to Cascade. It sounds like that's what's happening here. If you set the property to None (its default), you should see the desired behavior.

I hope this information proves helpful.

Bingo! Good call. I was tearing my hair out.

Thanks.

No problem. Hair is a precious thing.

I too get foreign key constraints when performing an update of deleted records. In my case however there is only one datatable containing a tree structure (a foreign key to itself). So changing the order of the datatables is not applicable.

Is there a way to change the order of the containing (typed) datarows?

I can put a cascade rule on the foreign key in the database, but won't that conflict with the data in the datatable (concurrency errors).

Extra side note: Sadly we have to use Oracle and not SQL Server.

Now, getting back to the auto-increment issue. If you're working with a SQL Server database, the TableAdapter Configuration Wizard should have auto-configured the TableAdapter to retrieve the new auto-increment value after submitting the change to the database. If you're working with similar functionality for another database (Jet or MySQL auto-increment columns, Oracle sequences, etc.), you'll need to supply your own logic to retrieve this information.

Hi - I am having the same issue, but I am using Jet. You mentioned that I need to supply my own logic for this. I tried to Fill the master adapter after the Update, but could not because of the child records. How should I go about doing this with Jet?

Scott

Hi David,

in the tentative to understand how it works the "cascade" behaviour of ForeignKeyConstraint, i tried to follow the your suggestions. So in the VB 2005 Express edition DataSet designer I've added two table adapters:

Claudio,

TableAdapters offer additional Insert, Update and Delete methods that execute INSERT, UPDATE and DELETE queries directly against the database. Your code inserted a row into the parent table, a related child row into the child table, and then deleted the row in the parent table. In the code you've posted, you're not using the DataSet to submit pending changes. You're only using it to retrieve the contents of the database after calling the Insert and Delete methods on your TableAdapters.

The simple answer is to define a foreign key constraint in your database. You could enable cascading referential integrity on the foreign key constraint so a query to delete a row in the parent table automatically deletes the corresponding child rows.

As for the DataRelations in the DataSet, you can configure a DataRelation so that when you mark a parent DataRow as a pending deletion, the DataRelation will also mark the corresponding child DataRows as pending deletions. You could then submit the changes stored in the DataSet in the order described earlier in this thread.

I hope this information proves helpful.

Hello David,

Thanks for your post, i am trying to follow it, but still can't get thing to work fine. I am using

Microsoft Visual Studio 2005 Version 8.0.50727.51 (QFE.050727-5100)
Microsoft .NET Framework Version 2.0.50727

My tables are in an access DB, which i access with this connect string

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\db.mdb"

I have a typed Dataset, for table 'Inspection', the Id column is marked as PK and autoincrement =true, step= -1, seed= -1.

and my code goes like this

InspectionTableAdapter inspectionTableAdapter = new InspectionTableAdapter();

DataRow[] inspection_newRecords = inspectionDS._Inspection.Select("", "", DataViewRowState.Added);