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

Example 4-17. Stored procedure: SP0410_GetParent

CREATE PROCEDURE SP0410_GetParent
    @ParentId int=null
AS
    SET NOCOUNT ON
    
    if @ParentId is not null
    begin
        select
            ParentId,
            Field1,
            Field2
        from
            TBL0410Parent
        where
            ParentId=@ParentId
            
        return 0
    end
    
    select
        ParentId,
        Field1,
        Field2
    from
        TBL0410Parent
        
    return 0

Example 4-18. Stored procedure: SP0410_GetParentChild

CREATE PROCEDURE SP0410_GetParentChild
    @ParentId int=null
AS
    if @ParentId is not null
    begin
        select
            ParentId,
            ChildID
        from
            TBL0410ParentChild
        where
            ParentId=@ParentId

        return 0
    end
        
    select
        ParentId,
        ChildID
    from
        TBL0410ParentChild

    return 0

Example 4-19. Stored procedure: SP0410_InsertChild

CREATE PROCEDURE SP0410_InsertChild
    @ChildId int output,
    @Field3 nvarchar(50)=null,
    @Field4 nvarchar(50)=null
AS
    SET NOCOUNT ON
    
    insert TBL0410Child(
        Field3,
        Field4)
    values (
        @Field3,
        @Field4)
    
    if @@rowcount=0
        return 1
    
    set @ChildId=Scope_Identity( )
    
    select @ChildId ChildId
    
    return 0

Example 4-20. Stored procedure: SP0410_InsertParent

CREATE PROCEDURE SP0410_InsertParent
    @ParentId int output,
    @Field1 nvarchar(50)=null,
    @Field2 nvarchar(50)=null
AS
    SET NOCOUNT ON
    
    insert TBL0410Parent(
        Field1,
        Field2)
    values (
        @Field1,
        @Field2)
    
    if @@rowcount=0
        return 1
    
    set @ParentId=Scope_Identity( )
    
    select @ParentId ParentId
        
    return 0

Example 4-21. Stored procedure: SP0410_InsertParentChild

CREATE PROCEDURE SP0410_InsertParentChild
    @ParentId int,
    @ChildId int
AS
    SET NOCOUNT ON

    insert TBL0410ParentChild(
        ParentId,
        ChildId)
    values (
        @ParentId,
        @ChildId)
        
    if @@rowcount=0
        return 1
    
    return 0

Example 4-22. Stored procedure: SP0410_UpdateChild

CREATE PROCEDURE SP0410_UpdateChild
    @ChildId int,
    @Field3 nvarchar(50)=null,
    @Field4 nvarchar(50)=null
AS
    SET NOCOUNT ON
    
    update
        TBL0410Child
    set
        Field3=@Field3,
        Field4=@Field4
    where
        ChildId=@ChildId
    
    if @@rowcount=0
        return 1
    
    return 0

Example 4-23. Stored procedure: SP0410_UpdateParent

CREATE PROCEDURE SP0410_UpdateParent
    @ParentId int,
    @Field1 nvarchar(50)=null,
    @Field2 nvarchar(50)=null
AS
    SET NOCOUNT ON
    
    update
        TBL0410Parent
    set
        Field1=@Field1,
        Field2=@Field2
    where
        ParentId=@ParentId
    
    if @@rowcount=0
        return 1
    
    return 0

The C# code for updating a DataSet with a many-to-one relationship is shown in Example 4-24.

Example 4-24. File: UpdateManyToManyRelationshipForm.cs

// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient; 

private DataSet ds;
private SqlDataAdapter daParent, daParentChild, daChild;

private const String PARENTTABLENAME       = "TBL0410Parent";
private const String PARENTCHILDTABLENAME  = "TBL0410ParentChild";
private const String CHILDTABLENAME        = "TBL0410Child";

// Table column name constants for Parent table
private const String PARENTID_FIELD        = "ParentId";
private const String FIELD1_FIELD          = "Field1";
private const String FIELD2_FIELD          = "Field2";

// Table column parameter name constants for Child table
private const String CHILDID_FIELD         = "ChildId";
private const String FIELD3_FIELD          = "Field3";
private const String FIELD4_FIELD          = "Field4";

// Stored procedure name constants
private const String DELETEPARENT_SP       = "SP0410_DeleteParent";
private const String GETPARENT_SP          = "SP0410_GetParent";
private const String INSERTPARENT_SP       = "SP0410_InsertParent";
private const String UPDATEPARENT_SP       = "SP0410_UpdateParent";
private const String DELETEPARENTCHILD_SP  =
    "SP0410_DeleteParentChild";
private const String GETPARENTCHILD_SP     = "SP0410_GetParentChild";
private const String INSERTPARENTCHILD_SP  =
    "SP0410_InsertParentChild";
private const String DELETECHILD_SP        = "SP0410_DeleteChild";
private const String GETCHILD_SP           = "SP0410_GetChild";
private const String INSERTCHILD_SP        = "SP0410_InsertChild";
private const String UPDATECHILD_SP        = "SP0410_UpdateChild";

// stored procedure parameter name constants for Parent table
private const String PARENTID_PARM         = "@ParentId";
private const String FIELD1_PARM           = "@Field1";
private const String FIELD2_PARM           = "@Field2";

// Stored procedure parameter name constants for Child table
private const String CHILDID_PARM          = "@ChildId";
private const String FIELD3_PARM           = "@Field3";
private const String FIELD4_PARM           = "@Field4"; 

//  . . . 

private void UpdateManyToManyRelationshipForm_Load(object sender,
    System.EventArgs e)
{
    DataColumnCollection cols;
    DataColumn col;

    // Build the parent table.
    DataTable parentTable = new DataTable(PARENTTABLENAME);
    cols = parentTable.Columns;
    col = cols.Add(PARENTID_FIELD, typeof(Int32));
    col.AutoIncrement = true;
    col.AutoIncrementSeed = -1;
    col.AutoIncrementStep = -1;
    cols.Add(FIELD1_FIELD, typeof(String)).MaxLength = 50;
    cols.Add(FIELD2_FIELD, typeof(String)).MaxLength = 50;

    // Build the ParentChild table.
    DataTable parentChildTable = new DataTable(PARENTCHILDTABLENAME);
    cols = parentChildTable.Columns;
    cols.Add(PARENTID_FIELD, typeof(Int32)).AllowDBNull = false;
    cols.Add(CHILDID_FIELD, typeof(Int32)).AllowDBNull = false;
    parentChildTable.PrimaryKey = new DataColumn[]
        {cols[PARENTID_FIELD], cols[CHILDID_FIELD]};

    // Build the child table.
    DataTable childTable = new DataTable(CHILDTABLENAME);
    cols = childTable.Columns;
    col = cols.Add(CHILDID_FIELD, typeof(Int32));
    col.AutoIncrement = true;
    col.AutoIncrementSeed = -1;
    col.AutoIncrementStep = -1;
    cols.Add(FIELD3_FIELD, typeof(String)).MaxLength = 50;
    cols.Add(FIELD4_FIELD, typeof(String)).MaxLength = 50;

    // Add the tables to the DataSet and create the relationship.
    ds = new DataSet( );
    ds.Tables.Add(parentTable);
    ds.Tables.Add(parentChildTable);
    ds.Tables.Add(childTable);
    ds.Relations.Add(new DataRelation("Parent_ParentChild",
        parentTable.Columns[PARENTID_FIELD],
        parentChildTable.Columns[PARENTID_FIELD], true));
    ds.Relations.Add(new DataRelation("Child_ParentChild",
        childTable.Columns[CHILDID_FIELD],
        parentChildTable.Columns[CHILDID_FIELD], true));


    // Create the Parent DataAdapter.
    daParent = new SqlDataAdapter(GETPARENT_SP,
        ConfigurationSettings.AppSettings["Sql_ConnectString"]);
    daParent.SelectCommand.CommandType = CommandType.StoredProcedure;

    // Build the parent delete command.
    SqlCommand deleteCommand = new SqlCommand(DELETEPARENT_SP,
        daParent.SelectCommand.Connection);
    deleteCommand.CommandType = CommandType.StoredProcedure;
    deleteCommand.Parameters.Add(PARENTID_PARM, SqlDbType.Int, 0,
        PARENTID_FIELD);
    daParent.DeleteCommand = deleteCommand;

    // Build the parent insert command.
    SqlCommand insertCommand = new SqlCommand(INSERTPARENT_SP,
        daParent.SelectCommand.Connection);
    insertCommand.CommandType = CommandType.StoredProcedure;
    insertCommand.Parameters.Add(PARENTID_PARM, SqlDbType.Int, 0,
        PARENTID_FIELD);
    insertCommand.Parameters.Add(FIELD1_PARM, SqlDbType.NVarChar, 50,
        FIELD1_FIELD);
    insertCommand.Parameters.Add(FIELD2_PARM, SqlDbType.NVarChar, 50,
        FIELD2_FIELD);
    daParent.InsertCommand = insertCommand;

    // Build the parent update command.
    SqlCommand updateCommand = new SqlCommand(UPDATEPARENT_SP,
        daParent.SelectCommand.Connection);
    updateCommand.CommandType = CommandType.StoredProcedure;
    updateCommand.Parameters.Add(PARENTID_PARM, SqlDbType.Int, 0,
        PARENTID_FIELD);
    updateCommand.Parameters.Add(FIELD1_PARM, SqlDbType.NVarChar, 50,
        FIELD1_FIELD);
    updateCommand.Parameters.Add(FIELD2_PARM, SqlDbType.NVarChar, 50,
        FIELD2_FIELD);
    daParent.UpdateCommand = updateCommand;


    // Create the Child DataAdapter.
    daChild = new SqlDataAdapter(GETCHILD_SP,
        ConfigurationSettings.AppSettings["Sql_ConnectString"]);
    daChild.SelectCommand.CommandType = CommandType.StoredProcedure;

    // Build the child delete command.
    deleteCommand = new SqlCommand(DELETECHILD_SP,
        daChild.SelectCommand.Connection);
    deleteCommand.CommandType = CommandType.StoredProcedure;
    deleteCommand.Parameters.Add(CHILDID_PARM, SqlDbType.Int, 0,
        CHILDID_FIELD);
    daChild.DeleteCommand = deleteCommand;

    // Build the child insert command.
    insertCommand = new SqlCommand(INSERTCHILD_SP,
        daChild.SelectCommand.Connection);
    insertCommand.CommandType = CommandType.StoredProcedure;
    insertCommand.Parameters.Add(CHILDID_PARM, SqlDbType.Int, 0,
        CHILDID_FIELD);
    insertCommand.Parameters.Add(FIELD3_PARM, SqlDbType.NVarChar, 50,
        FIELD3_FIELD);
    insertCommand.Parameters.Add(FIELD4_PARM, SqlDbType.NVarChar, 50,
        FIELD4_FIELD);
    daChild.InsertCommand = insertCommand;

    // Build the child update command.
    updateCommand = new SqlCommand(UPDATECHILD_SP,
        daChild.SelectCommand.Connection);
    updateCommand.CommandType = CommandType.StoredProcedure;
    updateCommand.Parameters.Add(CHILDID_PARM, SqlDbType.Int, 0,
        CHILDID_FIELD);
    updateCommand.Parameters.Add(FIELD3_PARM, SqlDbType.NVarChar, 50,
        FIELD3_FIELD);
    updateCommand.Parameters.Add(FIELD4_PARM, SqlDbType.NVarChar, 50,
        FIELD4_FIELD);
    daChild.UpdateCommand = updateCommand;

    
    // Create the ParentChild DataAdapter.
    daParentChild = new SqlDataAdapter(GETPARENTCHILD_SP,
        ConfigurationSettings.AppSettings["Sql_ConnectString"]);
    daParentChild.SelectCommand.CommandType = CommandType.StoredProcedure;

    // Build the ParentChild delete command.
    deleteCommand = new SqlCommand(DELETEPARENTCHILD_SP,
        daParentChild.SelectCommand.Connection);
    deleteCommand.CommandType = CommandType.StoredProcedure;
    deleteCommand.Parameters.Add(PARENTID_PARM, SqlDbType.Int, 0,
        PARENTID_FIELD);
    deleteCommand.Parameters.Add(CHILDID_PARM, SqlDbType.Int, 0,
        CHILDID_FIELD);
    daParentChild.DeleteCommand = deleteCommand;

    // Build the ParentChild insert command.
    insertCommand = new SqlCommand(INSERTPARENTCHILD_SP,
        daParentChild.SelectCommand.Connection);
    insertCommand.CommandType = CommandType.StoredProcedure;
    insertCommand.Parameters.Add(PARENTID_PARM, SqlDbType.Int, 0,
        PARENTID_FIELD);
    insertCommand.Parameters.Add(CHILDID_PARM, SqlDbType.Int, 0,
        CHILDID_FIELD);
    daParentChild.InsertCommand = insertCommand;

    LoadData( );

    dataGridParent.DataSource = parentTable.DefaultView;
    dataGridChild.DataSource = childTable.DefaultView;
}

private void LoadData( )
{
    // Fill the dataset.
    daParent.Fill(ds, PARENTTABLENAME);
    daChild.Fill(ds, CHILDTABLENAME);
    daParentChild.Fill(ds, PARENTCHILDTABLENAME);
}

private void CreateData(int parentRows, int childRows)
{
    // Create some data update the data source with it.
    for(int iParent = 0; iParent < parentRows; iParent++)
    {
        DataRow parentRow = ds.Tables[PARENTTABLENAME].NewRow( );
        parentRow[FIELD1_FIELD] = Guid.NewGuid().ToString( );
        parentRow[FIELD2_FIELD] = Guid.NewGuid().ToString( );
        ds.Tables[PARENTTABLENAME].Rows.Add(parentRow);
    }

    for(int iChild = 0; iChild < childRows; iChild++)
    {
        DataRow childRow = ds.Tables[CHILDTABLENAME].NewRow( );
        childRow[FIELD3_FIELD] = Guid.NewGuid().ToString( );
        childRow[FIELD4_FIELD] = Guid.NewGuid().ToString( );
        ds.Tables[CHILDTABLENAME].Rows.Add(childRow);
    }

    // Randomly create the parent-child relationships.
    Random r = new Random((int)DateTime.Now.Ticks);
    foreach(DataRow rowParent in ds.Tables[PARENTTABLENAME].Rows)
    {
        if(rowParent.RowState != DataRowState.Deleted)
        {
            foreach(DataRow rowChild in
                ds.Tables[CHILDTABLENAME].Rows)
            {
                if(rowChild.RowState != DataRowState.Deleted &&
                    r.Next(2) == 1)
                {
                    // Check to see that row doesn't exist
                    // before adding.
                    if(ds.Tables[PARENTCHILDTABLENAME].
                        Rows.Find(new object[]
                        {rowParent[PARENTID_FIELD],
                        rowChild[CHILDID_FIELD]}) == null)
                    {
                        ds.Tables[PARENTCHILDTABLENAME].
                            Rows.Add(new object[]
                            {rowParent[PARENTID_FIELD],
                            rowChild[CHILDID_FIELD]});
                    }
                }
            }
        }
    }
}

private void UpdateData( )
{
    try
    {
        daParentChild.Update(ds.Tables[PARENTCHILDTABLENAME].Select(
            null, null, DataViewRowState.Deleted));
        daChild.Update(ds.Tables[CHILDTABLENAME].Select(
            null, null, DataViewRowState.Deleted));
        daParent.Update(ds.Tables[PARENTTABLENAME].Select(
            null, null, DataViewRowState.Deleted));
        daParent.Update(ds.Tables[PARENTTABLENAME].Select(
            null, null, DataViewRowState.ModifiedCurrent));
        daParent.Update(ds.Tables[PARENTTABLENAME].Select(
            null, null, DataViewRowState.Added));
        daChild.Update(ds.Tables[CHILDTABLENAME].Select(
            null, null, DataViewRowState.ModifiedCurrent));
        daChild.Update(ds.Tables[CHILDTABLENAME].Select(
            null, null, DataViewRowState.Added));
        daParentChild.Update(ds.Tables[PARENTCHILDTABLENAME].Select(
            null, null, DataViewRowState.Added));
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

private void createDataButton_Click(object sender, System.EventArgs e)
{
    // Create parent and child records.
    CreateData(4,4);

    UpdateData( );
    MessageBox.Show("Data created.","Many-to-Many Relationships",
        MessageBoxButtons.OK, MessageBoxIcon.Information);
}

private void modifyButton_Click(object sender, System.EventArgs e)
{
    Random r = new Random((int)DateTime.Now.Ticks);

    // Randomly delete or modify rows from the child and parent rows.
    for(int i = ds.Tables[CHILDTABLENAME].Rows.Count; i > 0; i--)
    {
        DataRow childRow = ds.Tables[CHILDTABLENAME].Rows[i - 1];

        if(r.Next(2) == 0)
        {
            childRow[FIELD3_FIELD] = Guid.NewGuid().ToString( );
            childRow[FIELD4_FIELD] = Guid.NewGuid().ToString( );
        }
        else
            childRow.Delete( );
    }

    for(int i = ds.Tables[PARENTTABLENAME].Rows.Count; i > 0; i--)
    {
        DataRow parentRow = ds.Tables[PARENTTABLENAME].Rows[i - 1];

        if(r.Next(2) == 0)
        {
            parentRow[FIELD1_FIELD] = Guid.NewGuid().ToString( );
            parentRow[FIELD2_FIELD] = Guid.NewGuid().ToString( );
        }
        else
            parentRow.Delete( );
            
    }

    // Randomly delete m-n parent/child relationships.
    for(int i = ds.Tables[PARENTCHILDTABLENAME].Rows.Count; i > 0; i--)
    {
        DataRow parentChildRow =
            ds.Tables[PARENTCHILDTABLENAME].Rows[i - 1];

        if(r.Next(2) == 0)
            parentChildRow.Delete( );                    
    }

    // Insert two rows into Parent, Child, and random ParentChild.
    CreateData(2,2);

    UpdateData( );

    MessageBox.Show("Data randomly modified.",
        "Many-to-Many Relationships", MessageBoxButtons.OK,
        MessageBoxIcon.Information);        
}

private void deleteButton_Click(object sender, System.EventArgs e)
{
    // Delete the Parent records.
    for(int i = ds.Tables[PARENTTABLENAME].Rows.Count; i > 0 ;i--)
        ds.Tables[PARENTTABLENAME].Rows[i - 1].Delete( );

    // Delete the Child records.
    for(int i = ds.Tables[CHILDTABLENAME].Rows.Count; i > 0; i--)
        ds.Tables[CHILDTABLENAME].Rows[i - 1].Delete( );

    // Delete the ParentChild records.
    for(int i = ds.Tables[PARENTCHILDTABLENAME].Rows.Count; i > 0 ;i--)
        ds.Tables[PARENTCHILDTABLENAME].Rows[i - 1].Delete( );

    UpdateData( );

    MessageBox.Show("Data deleted.", "Many-to-Many Relationships",
        MessageBoxButtons.OK, MessageBoxIcon.Information);
}

private void refreshButton_Click(object sender, System.EventArgs e)
{
    ds.Clear( );

    LoadData( );
}

Discussion

To avoid referential integrity problems when updating a data source with changes in a DataSet having tables related with a many-to-many relationship, update the rows in the following order:

1. Deleted junction rows

2. Deleted child rows

3. Deleted parent rows

4. Updated parent rows

5. Inserted parent rows

6. Updated child rows

7. Inserted child rows

8. Inserted junction rows

Pass DataViewRowState.Deleted into the Select( ) method of the DataTable object to get the subset of deleted rows from a table. Similarly, pass DataViewRowState.Added to obtain inserted rows and DataViewRowState.ModifiedCurrent to obtain modified rows.

A few more considerations involving the primary key:

• If the primary key cannot be modified once added, the updated and inserted rows can be processed in the same statement. Pass a bitwise combination into the select method as shown here:

  daParent.Update(ds.Tables[PARENTTABLENAME].Select(null, null,
      DataViewRowState.Added | DataViewRowState.ModifiedCurrent));

• If the primary key can be modified, the database must cascade the updated primary key values to the child records; otherwise, a referential integrity violation will occur. If the foreign key is used as part of the concurrency handling process, the UpdateCommand property of child tables must accept either its Original or the Current value.

• If the primary key is an AutoIncrement value and its value is generated by the database, the InsertCommand must return the primary key value from the data source and use it to update the value in the DataSet. The DataSet will then automatically cascade this new value to the child records.