[ Team LiB ]
Recipe 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.
SP0410_GetChild
Gets the Child record corresponding to the ChildId
specified or returns all Child records if no ChildId is
specified.
SP0410_GetParent
Gets the Parent record corresponding to the ParentId
specified or returns all Parent records if no ParentId is
specified.
SP0410_GetParentChild
Gets the ParentChild records corresponding to the ParentId
specified or returns all ParentChild records if no ParentId
is specified.
SP0410_InsertChild
Adds a new Child record. The stored procedure returns the
ChildId value generated by the data source as both an
output parameter and in the first returned record.
SP0410_InsertParent
Adds a new Parent record. The stored procedure returns the
ParentId value generated by the data source as both an
output parameter and in the first returned record.
SP0410_InsertParentChild Adds a new ParentChild record.
SP0410_UpdateChild Updates the Child record matching the specified ChildId.
SP0410_UpdateParent Updates the Parent record matching the specified ParentId.
The sample code contains five event handlers and four methods:
Form.Load
This event handler sets up the sample by creating a DataSet containing a parent,
child, and many-to-many junction table, as well as the DataRelation objects
between them. A DataAdapter object is created for each table and the stored
procedures to select, delete, insert, and update records in the data source are
specified for each. The LoadData( ) method is called to retrieve data for each table
in the DataSet. Finally, the default view for the parent and the child tables are
bound to data grids on the form.
LoadData( )
This method calls the Fill( ) method of the DataAdapter for each of the parent,
child, and junction tables.
CreateData( )
This method creates random data in both the parent and child tables and randomly
creates relationships between them by adding records to the junction table.
UpdateData( )
This method updates all changes in the DataSet back to the data source by calling
in the correct order the Update( ) method of the DataAdapter object for subsets of
the data in each of the parent, child, and junction tables.
Create Button.Click
This event handler calls the CreateData( ) method to add random data to the
DataSet.
Modify Button.Click
This event handler makes random changes to the data in the DataSet:
• Rows from the parent and child table are deleted or have values in their
fields modified.
• Parent/child relationships are eliminated by deleting records from the
junction table.
• The CreateData( ) method is called to create new data.
• The UpdateData( ) method is called to update all of the changes made to the
DataSet with the data source.
Delete Button.Click
This event handler deletes all data from the parent, child, and junction table. The
UpdateData( ) method is called to update the changes made to the DataSet with the
data source.
Refresh Button.Click
This event handler clears all data from the DataSet. LoadData( ) is then called to
load all data from the data source into the parent, child, and junction tables in the
DataSet.
The 11 stored procedures used in this example are shown in Example 4-13 through
Example 4-23.
Example 4-13. Stored procedure: SP0410_DeleteChild
CREATE PROCEDURE SP0410_DeleteChild
@ChildId int
AS
SET NOCOUNT ON
delete
from
TBL0410Child
where
ChildId=@ChildId
return 0
Example 4-14. Stored procedure: SP0410_DeleteParent
CREATE PROCEDURE SP0410_DeleteParent
@ParentId int
AS
SET NOCOUNT ON
delete
from
TBL0410Parent
where
ParentId=@ParentId
return 0
Example 4-15. Stored procedure: SP0410_DeleteParentChild
CREATE PROCEDURE SP0410_DeleteParentChild
@ParentId int,
@ChildId int
AS
SET NOCOUNT ON
delete
from
TBL0410ParentChild
where
ParentId=@ParentId and
ChildId=@ChildId
return 0
Example 4-16. Stored procedure: SP0410_GetChild
CREATE PROCEDURE SP0410_GetChild
@ChildId int=null
AS
SET NOCOUNT ON
if @ChildId is not null
begin
select
ChildID,
Field3,
Field4
from
TBL0410Child
where
ChildId=@ChildId
return 0
end
select
ChildId,
Field3,
Field4
from
TBL0410Child