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:
Deleted grandchild rows
Deleted child rows
Deleted parent rows
Updated parent rows
Inserted parent rows
Updated child rows
Inserted child rows
Updated grandchild rows
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 the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.