ADO.NET 3.5 Cookbook, 2nd Edition

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

Solution

Use the techniques described in the discussion.

The solution uses three tables: ParentUpdateManyMany, JunctionUpdateManyMany, and ChildUpdateManyMany shown in Figures 5-23, 5-24, and 5-25.

Figure 5-23. Schema for table ParentUpdateManyMany

Figure 5-24. Schema for table JunctionUpdateManyMany

Figure 5-25. Schema for table ChildUpdateManyMany

The T-SQL to create the three tables is shown in Example 5-25.

Example 5-25. Create tables

USE AdoDotNet35Cookbook GO CREATE TABLE ParentUpdateManyMany( ParentId int IDENTITY(1,1) NOT NULL PRIMARY KEY, a nvarchar(50), b nvarchar(50) ) GO CREATE TABLE JunctionUpdateManyMany( ParentId int NOT NULL, ChildId int NOT NULL, CONSTRAINT PK_JunctionUpdateManyMany PRIMARY KEY (ParentId, ChildId) ) GO CREATE TABLE ChildUpdateManyMany( ChildId int IDENTITY(1,1) NOT NULL PRIMARY KEY, c nvarchar(50), d nvarchar(50) ) GO ALTER TABLE JunctionUpdateManyMany WITH ...