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 ...

Get ADO.NET in a Nutshell now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.