15.6 Updating Data in Related Tables

15.6 Updating Data in Related Tables

To avoid referential integrity problems when updating the data source from a DataSet containing related rows, especially in situations involving batch updates, the rows must be updated in the following order:

1. Deleted grandchild rows

2. Deleted child rows

3. Deleted parent rows

4. Updated parent rows

5. Inserted parent rows

6. Updated child rows

7. Inserted child rows

8. Updated grandchild rows

9. Inserted grandchild rows

To obtain the set of deleted rows, pass DataViewRowState.Deleted to the DataTable.Select( ) method. To obtain the set of inserted rows, pass DataViewRowState.Added to the DataTable.Select( ) method. To obtain the set of modified rows, pass DataViewRowState.ModifiedCurrent to the DataTable.Select( ) method.

There are few other considerations involving the primary key. If the primary key can't be modified once added, the updated and inserted rows can be processed in the same statement. If, on the other hand, the primary key can be modified, the database must cascade the updated primary key values to the child records; otherwise a referential integrity violation will occur. The UpdateCommand property of child tables must accept either the Original or the Current value of the foreign key if it is used as part of a concurrency handling process. Finally, if the primary key is an AutoIncrement value, and the value is generated by the database, the InsertCommand must return the primary key value from the data source and use it to update the value in the DataSet . The DataSet then automatically cascades this new value to the child records.

Example 15-4 demonstrates the ordering of updates using a parent and child table; because there are no grandchild records, only six update commands instead of nine are required. As discussed earlier, the code uses the Select( ) method of the tables with the DataViewRowState argument to select the subset of records to update.

Example 15-4. Update ordering

 SqlDataAdapter daOrders; SqlDataAdapter daOrderDetails; // setup data adapters, and the SelectCommand and UpdateCommands for the // parent Orders table and the child Order Details table // load the data for parent and child tables into the DataSet DataSet ds = new DataSet(); daOrders.Fill(ds, "Orders"); daOrderDetails.Fill(ds, "Order Details"); // ... code to modify the data in the DataSet // update the modified data in the DataSet back to the data source daOrderDetails.Update(ds.Tables["Order Details"].Select(null, null,     DataViewRowState.Deleted)); daOrders.Update(ds.Tables["Orders"].Select(null, null,     DataViewRowState.Deleted)); daOrders.Update(ds.Tables["Orders"].Select(null, null,     DataViewRowState.ModifiedCurrent)); daOrders.Update(ds.Tables["Orders"].Select(null, null,     DataViewRowState.Added)); daOrderDetails.Update(ds.Tables["Order Details"].Select(null, null,     DataViewRowState.ModifiedCurrent)); daOrderDetails.Update(ds.Tables["Order Details"].Select(null, null,     DataViewRowState.Added));