ADO.NET Cookbook

6.6. Avoiding Referential Integrity Problems When Updating the Data Source

Problem

You sometimes get referential integrity errors when you update a DataSet that contains related parent, child, and grandchild records back to the underlying data source, but want to perform the update without errors.

Solution

Use one DataAdapter for each DataTable to update the deleted, updated, and inserted rows as shown in the following example.

The schema of table TBL0606Parent used in this solution is shown in Table 6-2.

Table 6-2. TBL0606Parent schema

Column name	Data type	Length	Allow nulls?
ParentId	int	4	No
Field1	nvarchar	50	Yes
Field2	nvarchar	50	Yes

The schema of table TBL00606Child used in this solution is shown in Table 6-3.

Table 6-3. TBL0606Child schema

Column name	Data type	Length	Allow nulls?
ChildId	int	4	No
ParentId	int	4	No
Field3	nvarchar	50	Yes
Field4	nvarchar	50	Yes

The schema of table TBL0606Grandchild used in this solution is shown in Table 6-4.

Table 6-4. TBL0606Grandchild schema

Column name	Data type	Length	Allow nulls?
GrandchildId	int	4	No
ChildId	int	4	No
Field5	nvarchar	50	Yes
Field6	nvarchar	50	Yes

The sample uses 12 stored procedures:

SP0606_GetParent

Used to retrieve a single record from the Parent table if the optional @ParentId parameter is specified or all Parent records if it is not

SP0606_DeleteParent

Used to delete the record specified by the @ParentId parameter from the Parent table

SP0606_InsertParent

Used to insert ...