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

Get ADO.NET Cookbook 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.