ADO.NET Cookbook

4.10. Updating a DataSet with a Many-to-Many Relationship

Problem

You have a DataSet that contains two tables that have a many-to-many relationship between them using a third junction table. You get referential integrity errors when you try to update changes to the data in this DataSet back to the data source. You need to do this successfully.

Solution

Use the techniques described in the discussion.

The schema of table TBL0410Parent used in this solution is shown in Table 4-5.

Table 4-5. TBL0410Parent schema

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

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

Table 4-6. TBL0410Child schema

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

The schema of table TBL0410ParentChild used in this solution is shown in Table 4-7.

Table 4-7. TBL0410ParentChild schema

Column name	Data type	Length	Allow nulls?
ParentId	int	4	No
ChildId	int	4	No

The solution uses eleven stored procedures described in Table 4-8.

Table 4-8. Stored procedures for solution in Recipe 4.10

Name	Description
SP0410_DeleteChild	Deletes the Child record specified by the ChildId input parameter.
SP0410_DeleteParent	Deletes the Parent record specified by the ParentId input parameter.
SP0410_DeleteParentChild	Deletes the ParentChild record specified by the ParentId and ChildId input parameters.