Avoiding Referential Integrity

Chia sẻ: Bui Tuan | Ngày: | Loại File: PDF | Số trang:22

Thêm vào BST

Báo xấu

44
lượt xem 5
download

Avoiding Referential Integrity

Mô tả tài liệu

Download Vui lòng tải xuống để xem tài liệu đầy đủ

[ Team LiB ] Recipe 6.6 Avoiding Referential Integrity Poblems 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.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Avoiding Referential Integrity

[ Team LiB ] 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: 1. Deleted grandchild rows 2. Deleted child rows 3. Deleted parent rows 4. Updated parent rows 5. Inserted parent rows 6. Updated child rows

CÓ THỂ BẠN MUỐN DOWNLOAD