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

The schema of table TBL00606Child used in this solution is shown in Table 6-3.

Table 6-3. TBL0606Child schema

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

Table 6-4. TBL0606Grandchild schema

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:

1. Deleted grandchild 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. Updated grandchild rows
9. Inserted grandchild rows

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 tablesparent, child, and grandparentand 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.