Recipe 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 a record into the Parent table and return the ParentId identity value for the new record

SP0606_UpdateParent

Used to update all field values for the record in the Parent table specified by the @ParentId input parameter

SP0606_GetChild

Used to retrieve a single record from the Child table if the optional @ChildId parameter is specified or all Child records if it is not

SP0606_DeleteChild

Used to delete the record specified by the @ChildId parameter from the Child table

SP0606_InsertChild

Used to insert a record into the Child table and return the ChildId identity value for the new record

SP0606_UpdateChild

Used to update all field values for the record in the Child table specified by the @ChildId input parameter

SP0606_GetGrandchild

Used to retrieve a single record from the Grandchild table if the optional @GrandchildId parameter is specified or all Grandchild records if it is not

SP0606_DeleteGrandchild

Used to delete the record specified by the @GrandchildId parameter from the Grandchild table

SP0606_InsertGrandchild

Used to insert a record into the Grandchild table and return the GrandchildId identity value for the new record

SP0606_UpdateGrandchild

Used to update all field values for the record in the Grandchild table specified by the @GrandchildId input parameter

The sample code contains four event handlers and two methods:

Form.Load

Sets up the sample by creating a DataSet containing the Parent, Child, and Grandchild DataTable objects. DataRelation objects are created relating the tables. DataAdapter objects are created for each DataTable and the select, delete, insert, and update Command objects are specified for each using the custom logic in the twelve stored procedures used by this solution. The DataAdapter objects are used to fill the tables in the DataSet. Finally, the default view of the Parent table is bound to the data grid on the form.

CreateData( )

This method generates a number of rows in the Parent, Child, and Grandchild DataTable objects as specified by the input parameters of the method.

UpdateData( )

This method uses the DataAdapter for each of the Parent, Child, and Grandchild DataTable objects to update offline changes back to the database.

Create Data Button.Click

Calls the CreateData( ) to create four Parent records, four Child records for each Parent, and four Grandchild records for each Child. Finally, the UpdateData( ) method is called to update the database with the new records.

Modify Data Button.Click

Randomly modifies or deletes records from the Parent, Child, and Grandchild DataTable objects. The CreateData( ) method is called to create two Parent records, two Child records for each Parent, and two Grandchild records for each Child. Finally, the UpdateData( ) method is called to update the database with the offline changes, deletions, and additions.

Delete Data Button.Click

Deletes all records from the Parent DataTable, which then cascades to delete all Child and Grandchild records clearing all data from the DataSet. Finally, the UpdateData( ) method is called to update the database with the offline deletions.

The 12 stored procedures are shown in Example 6-8 through Example 6-19.

Example 6-8. Stored procedure: SP0606_GetParent

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

Example 6-9. Stored procedure: SP0606_DeleteParent

CREATE PROCEDURE SP0606_DeleteParent
    @ParentId int
AS
    SET NOCOUNT ON
    
    delete
    from
        TBL0606Parent
    where
        ParentId=@ParentId
        
    return 0

Example 6-10. Stored procedure: SP0606_InsertParent

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

Example 6-11. Stored procedure: SP0606_UpdateParent

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

Example 6-12. Stored procedure: SP0606_GetChild

CREATE PROCEDURE SP0606_GetChild
    @ChildId int=null
AS
    SET NOCOUNT ON
    
    if @ChildId is not null
    begin
        select
            ChildID,
            ParentId,
            Field3,
            Field4
        from
            TBL0606Child
        where
            ChildId=@ChildId
            
        return 0
    end
    
    select
        ChildId,
        ParentId,
        Field3,
        Field4
    from
        TBL0606Child
        
    return 0

Example 6-13. Stored procedure: SP0606_DeleteChild

CREATE PROCEDURE SP0606_DeleteChild
    @ChildId int
AS
    SET NOCOUNT ON
    
    delete
    from
        TBL0606Child
    where
        ChildId=@ChildId
        
    return 0

Example 6-14. Stored procedure: SP0606_InsertChild

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

Example 6-15. Stored procedure: SP0606_UpdateChild

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

Example 6-16. Stored procedure: SP0606_GetGrandchild

CREATE PROCEDURE SP0606_GetGrandchild
    @GrandchildId int=null
AS
    SET NOCOUNT ON
    
    if @GrandchildId is not null
    begin
        select
            GrandchildID,
            ChildId,
            Field5,
            Field6
        from
            TBL0606Grandchild
        where
            GrandchildId=@GrandchildId
            
        return 0
    end
    
    select
        GrandchildId,
        ChildId,
        Field5,
        Field6
    from
        TBL0606Grandchild
        
    return 0

Example 6-17. Stored procedure: SP0606_DeleteGrandchild

CREATE PROCEDURE SP0606_DeleteGrandchild
    @GrandchildId int
AS
    SET NOCOUNT ON
    
    delete
    from
        TBL0606Grandchild
    where
        GrandchildId=@GrandchildId
        
    return 0

Example 6-18. Stored procedure: SP0606_InsertGrandchild

CREATE PROCEDURE SP0606_InsertGrandchild
    @GrandchildId int output,
    @ChildId int,
    @Field5 nvarchar(50)=null,
    @Field6 nvarchar(50)=null
AS
    SET NOCOUNT ON
    
    insert TBL0606Grandchild(
        ChildId,
        Field5,
        Field6)
    values (
        @ChildId,
        @Field5,
        @Field6)
    
    if @@rowcount=0
        return 1
    
    set @GrandchildId=Scope_Identity( )
    
    select @GrandchildId GrandchildId
    
    return 0

Example 6-19. Stored procedure: SP0606_UpdateGrandchild

CREATE PROCEDURE SP0606_UpdateGrandchild
    @GrandchildId int,
    @ChildId int,
    @Field5 nvarchar(50)=null,
    @Field6 nvarchar(50)=null
AS
    SET NOCOUNT ON
    
    update
        TBL0606Grandchild
    set
        ChildId=@ChildId,
        Field5=@Field5,
        Field6=@Field6
    where
        GrandchildId=@GrandchildId
    
    if @@rowcount=0
        return 1
    
    return 0

The C# code is shown in Example 6-20.

Example 6-20. File: ReferentialIntegrityUpdateLogicForm.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, daChild, daGrandchild;

private const String PARENTTABLENAME     = "TBL00606Parent";
private const String CHILDTABLENAME      = "TBL00606Child";
private const String GRANDCHILDTABLENAME = "TBL00606Grandchild";

// 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";

// Table column parameter name constants for Grandchild table
private const String GRANDCHILDID_FIELD  = "GrandchildId";
private const String FIELD5_FIELD        = "Field5";
private const String FIELD6_FIELD        = "Field6";

// Stored procedure name constants
private const String DELETEPARENT_SP     = "SP0606_DeleteParent";
private const String GETPARENT_SP        = "SP0606_GetParent";
private const String INSERTPARENT_SP     = "SP0606_InsertParent";
private const String UPDATEPARENT_SP     = "SP0606_UpdateParent";
private const String DELETECHILD_SP      = "SP0606_DeleteChild";
private const String GETCHILD_SP         = "SP0606_GetChild";
private const String INSERTCHILD_SP      = "SP0606_InsertChild";
private const String UPDATECHILD_SP      = "SP0606_UpdateChild";
private const String DELETEGRANDCHILD_SP = "SP0606_DeleteGrandchild";
private const String GETGRANDCHILD_SP    = "SP0606_GetGrandchild";
private const String INSERTGRANDCHILD_SP = "SP0606_InsertGrandchild";
private const String UPDATEGRANDCHILD_SP = "SP0606_UpdateGrandchild";

// 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";

// Stored procedure parameter name constants for Child table
private const String GRANDCHILDID_PARM   = "@GrandchildId";
private const String FIELD5_PARM         = "@Field5";
private const String FIELD6_PARM         = "@Field6";

//  . . . 

private void ReferentialIntegrityUpdateLogicForm_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 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(PARENTID_FIELD, typeof(Int32)).AllowDBNull = false;
    cols.Add(FIELD3_FIELD, typeof(String)).MaxLength = 50;
    cols.Add(FIELD4_FIELD, typeof(String)).MaxLength = 50;

    // Build the grandchild table.
    DataTable grandchildTable = new DataTable(GRANDCHILDTABLENAME);
    cols = grandchildTable.Columns;
    col = cols.Add(GRANDCHILDID_FIELD, typeof(Int32));
    col.AutoIncrement = true;
    col.AutoIncrementSeed = -1;
    col.AutoIncrementStep = -1;
    cols.Add(CHILDID_FIELD, typeof(Int32)).AllowDBNull = false;
    cols.Add(FIELD5_FIELD, typeof(String)).MaxLength = 50;
    cols.Add(FIELD6_FIELD, typeof(String)).MaxLength = 50;

    // Add the tables to the DataSet and create the relation between them.
    ds = new DataSet( );
    ds.Tables.Add(parentTable);
    ds.Tables.Add(childTable);
    ds.Tables.Add(grandchildTable);
    ds.Relations.Add(new DataRelation("Parent_Child_Relation",
        parentTable.Columns[PARENTID_FIELD],
        childTable.Columns[PARENTID_FIELD], true));
    ds.Relations.Add(new DataRelation("Child_Grandchild_Relation",
        childTable.Columns[CHILDID_FIELD],
        grandchildTable.Columns[CHILDID_FIELD], true));

    // Create the DataAdapter objects for the tables.
    daParent = new SqlDataAdapter( );
    daChild = new SqlDataAdapter( );
    daGrandchild = new SqlDataAdapter( );

    // Build the parent select command.
    SqlCommand selectCommand = new SqlCommand(GETPARENT_SP,
        new SqlConnection(
        ConfigurationSettings.AppSettings["Sql_ConnectString"]));
    selectCommand.CommandType = CommandType.StoredProcedure;
    daParent.SelectCommand = selectCommand;

    // 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;

    // Build the child select command.
    selectCommand = new SqlCommand(GETCHILD_SP,
        new SqlConnection(
        ConfigurationSettings.AppSettings["Sql_ConnectString"]));
    selectCommand.CommandType = CommandType.StoredProcedure;
    daChild.SelectCommand = selectCommand;

    // 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(PARENTID_PARM, SqlDbType.Int, 0,
        PARENTID_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(PARENTID_PARM, SqlDbType.Int, 0,
        PARENTID_FIELD);
    updateCommand.Parameters.Add(FIELD3_PARM, SqlDbType.NVarChar, 50,
        FIELD3_FIELD);
    updateCommand.Parameters.Add(FIELD4_PARM, SqlDbType.NVarChar, 50,
        FIELD4_FIELD);
    daChild.UpdateCommand = updateCommand;

    // Build the grandchild select command.
    selectCommand = new SqlCommand(GETGRANDCHILD_SP,
        new SqlConnection(
        ConfigurationSettings.AppSettings["Sql_ConnectString"]));
    selectCommand.CommandType = CommandType.StoredProcedure;
    daGrandchild.SelectCommand = selectCommand;

    // Build the grandchild delete command.
    deleteCommand = new SqlCommand(DELETEGRANDCHILD_SP,
        daGrandchild.SelectCommand.Connection);
    deleteCommand.CommandType = CommandType.StoredProcedure;
    deleteCommand.Parameters.Add(GRANDCHILDID_PARM, SqlDbType.Int, 0,
        GRANDCHILDID_FIELD);
    daGrandchild.DeleteCommand = deleteCommand;

    // Build the grandchild insert command.
    insertCommand = new SqlCommand(INSERTGRANDCHILD_SP,
        daGrandchild.SelectCommand.Connection);
    insertCommand.CommandType = CommandType.StoredProcedure;
    insertCommand.Parameters.Add(GRANDCHILDID_PARM, SqlDbType.Int, 0,
        GRANDCHILDID_FIELD);
    insertCommand.Parameters.Add(CHILDID_PARM, SqlDbType.Int, 0,
        CHILDID_FIELD);
    insertCommand.Parameters.Add(FIELD5_PARM, SqlDbType.NVarChar, 50,
        FIELD5_FIELD);
    insertCommand.Parameters.Add(FIELD6_PARM, SqlDbType.NVarChar, 50,
        FIELD6_FIELD);
    daGrandchild.InsertCommand = insertCommand;

    // Build the grandchild update command.
    updateCommand = new SqlCommand(UPDATEGRANDCHILD_SP,
        daGrandchild.SelectCommand.Connection);
    updateCommand.CommandType = CommandType.StoredProcedure;
    updateCommand.Parameters.Add(GRANDCHILDID_PARM, SqlDbType.Int, 0,
        GRANDCHILDID_FIELD);
    updateCommand.Parameters.Add(CHILDID_PARM, SqlDbType.Int, 0,
        CHILDID_FIELD);
    updateCommand.Parameters.Add(FIELD5_PARM, SqlDbType.NVarChar, 50,
        FIELD5_FIELD);
    updateCommand.Parameters.Add(FIELD6_PARM, SqlDbType.NVarChar, 50,
        FIELD6_FIELD);
    daGrandchild.UpdateCommand = updateCommand;

    // Fill the parent and child table.
    daParent.Fill(parentTable);
    daChild.Fill(childTable);
    daGrandchild.Fill(grandchildTable);

    // Bind the default view of the data source to the grid.
    dataGrid.DataSource = parentTable.DefaultView;

}

private void CreateData(int parentRows, int childRows, int grandchildRows)
{
    // Generate some data into each of the related tables.
    for(int iParent = 0; iParent < parentRows; iParent++)
    {
        // Generate parentRows of data in the parent table.
        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++)
        {
            // Generate childRows of data in the child table.
            DataRow childRow = ds.Tables[CHILDTABLENAME].NewRow( );
            childRow[PARENTID_FIELD] =
                (int)parentRow[PARENTID_FIELD];
            childRow[FIELD3_FIELD] = Guid.NewGuid().ToString( );
            childRow[FIELD4_FIELD] = Guid.NewGuid().ToString( );
            ds.Tables[CHILDTABLENAME].Rows.Add(childRow);
        
            for(int iGrandchild = 0; iGrandchild < grandchildRows;
                iGrandchild++)
            {
                // Generate grandchildRows of data in the
                // grandchild table.
                DataRow grandchildRow =
                    ds.Tables[GRANDCHILDTABLENAME].NewRow( );
                grandchildRow[CHILDID_FIELD] =
                    (int)childRow[CHILDID_FIELD];
                grandchildRow[FIELD5_FIELD] =
                    Guid.NewGuid().ToString( );
                grandchildRow[FIELD6_FIELD] =
                    Guid.NewGuid().ToString( );
                ds.Tables[GRANDCHILDTABLENAME].Rows.Add(
                    grandchildRow);
            }
        }
    }
}

private void UpdateData( )
{
    // Update the related tables.
    daGrandchild.Update(ds.Tables[GRANDCHILDTABLENAME].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));
    daGrandchild.Update(ds.Tables[GRANDCHILDTABLENAME].Select(null, null,
        DataViewRowState.ModifiedCurrent));
    daGrandchild.Update(ds.Tables[GRANDCHILDTABLENAME].Select(null, null,
        DataViewRowState.Added));
}

private void createDataButton_Click(object sender, System.EventArgs e)
{
    // Create four rows of data in each parent, child, and grandchild.
    CreateData(4, 4, 4);
    // Update the data source with the new data.
    UpdateData( );

    MessageBox.Show("Data created.", "Referential Integrity",
        MessageBoxButtons.OK, MessageBoxIcon.Information);
}

private void modifyButton_Click(object sender, System.EventArgs e)
{
    // Randomly delete or modify rows from the grandchild, child, and
    // parent rows.
    Random r = new Random((int)DateTime.Now.Ticks);

    // Modify grandchild rows.
    for(int i = ds.Tables[GRANDCHILDTABLENAME].Rows.Count; i > 0; i--)
    {
        DataRow grandchildRow =
            ds.Tables[GRANDCHILDTABLENAME].Rows[i - 1];

        if(r.Next(2) == 0)
        {
            grandchildRow[FIELD5_FIELD] = Guid.NewGuid().ToString( );
            grandchildRow[FIELD6_FIELD] = Guid.NewGuid().ToString( );
        }
        else
            grandchildRow.Delete( );
    }

    // Modify or delete child 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( );
    }

    // Modify or delete parent rows.
    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( );
            
    }

    // Insert two rows into parent, child, and grandchild.
    CreateData(2 ,2, 2);

    // Update the data source with the changes.
    UpdateData( );

    MessageBox.Show("Data randomly modified.", "Referential Integrity",
        MessageBoxButtons.OK, MessageBoxIcon.Information);        
}

private void deleteButton_Click(object sender, System.EventArgs e)
{
    // Delete the parent data which cascades by default
    // to child and grandchild records.
    for(int i = ds.Tables[PARENTTABLENAME].Rows.Count; i > 0; i--)
        ds.Tables[PARENTTABLENAME].Rows[i - 1].Delete( );

    // Update the data source with the changes.
    UpdateData( );

    MessageBox.Show("Data deleted.", "Referential Integrity",
        MessageBoxButtons.OK, MessageBoxIcon.Information);
}

Discussion

To avoid referential integrity problems when updating the data source from a DataSet containing related tables, use one DataAdapter for each DataTable to update the deleted, updated, and inserted rows in the following order:

In the solution, this is done using the following code:

daGrandchild.Update(ds.Tables[GRANDCHILDTABLENAME].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));
daGrandchild.Update(ds.Tables[GRANDCHILDTABLENAME].Select(null, null,
    DataViewRowState.ModifiedCurrent));
daGrandchild.Update(ds.Tables[GRANDCHILDTABLENAME].Select(null, null,
    DataViewRowState.Added));

There are three related tables—parent, child, and grandparent—and one DataAdapter for each table. An overload of the Select( ) method of the DataTable is used to retrieve the subset of rows identified by the state argument containing a value from the DataViewRowState enumeration:

• Added to get the subset of inserted rows

• Deleted to get the subset of deleted rows

• ModifiedCurrent to get the subset of modified rows

There are few other considerations involving the primary key:

• If the primary key cannot be modified once added, the updated and inserted rows can be processed together in the same statement.

• If the primary key can be modified after it has been added, the database must cascade the updated primary key values to the child records or else a referential integrity violation will occur. The UpdateCommand property of child tables must accept either the Original or the Current value of the foreign key if it is used in the concurrency check.

• If the primary key for the DataTable is an auto-increment value and the primary key value is generated by the data source, the InsertCommand must return the primary key value from the data source and use it to update the value in the DataSet. The DataSet can automatically cascade this new value to the foreign keys in the related child records.