O'Reilly logo

ADO.NET Cookbook by Bill Hamilton

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required