Update step when the DataSet contains main/child tables

(1) Prologue

When the DataSet contains both primary/sub tables (primary key tables/foreign key tables), sometimes the relationship constraints are too strict:
For example, relational database integrity rules:
1. Entity integrity. The primary key in the primary key table is not allowed to be empty.
2. Referential integrity. The value of the foreign key in the foreign key table must correspond to the primary key in the primary key table.
Either empty or a primary key value in the primary key table.
3. Customize integrity.
If the multi-table relationship constraints defined in the DataSet table are too strict, use the Update method directly.
When submitting multiple tables in a DataSet together, the integrity rules may not be met and an error may occur.
Reason: For example, set up two tables: CompanyMain (company main table) and CompanySon (company subtable)
CompanyMain (company main table) mainly stores the basic information of a company, CompanySon (company sub-table)
It mainly stores information about some customers of this company. And (ID) in the company's main table and (BelongID) in the sub-table
Establish an association, that is, a primary and foreign key relationship; one company corresponds to multiple customers, that is, an ID:BelongID = 1:n relationship.
The ID of the main table is an automatically generated number.
So:
When submitting the new company interface together, the system will not comply with the "database integrity rules" to update the database.
If it updates the sub-table first and then updates the main table, an error may be reported. The reason is that the company information in the main table has not been inserted yet.
To the table in the database, the company number: ID is not generated, and there will be no corresponding BelongID when the sub-table is updated.
At this time, if the integrity rule "The foreign key in the foreign key table is not null" is set, an exception will be thrown.
This is just a possibility of error, there are more possibilities of error, and such errors are more likely to occur in distributed design.

(2). The solution

generally follows the following rules, which will avoid a large number of error chances.

1 Rule. Before updating the DataSet, perform split submission by <table> and <RowState attribute of the table>

I. Perform split submission by <table> means:

the tables in the DataSet are not submitted together, but submitted one at a time. Table, submit multiple times

II. Split submission according to <RowState attribute of the table> means:

split a single table in the DataSet according to the RowState attribute,

submit the same RowState once, and submit multiple times

2. Rules.

Based on rule 1, first update the status of <new> and <modified>, and then update the <deleted>.

That is: first update the DataRowState value: Added and Modified, and then update: Deleted.

3 .Based

on Rule 1 and Rule 2, if DataRowState is Added and Modified, the main table is updated first, and then the sub-table is updated. Based on Rule

1 and Rule 2, if DataRowState is Deleted, then the sub-table is updated first, and then the sub-table is updated. Update the main table.

(3) Summarize the above three rules as follows:

1. Split the table in the DataSet, group each table record according to RowState and store it in different data sets

// This should be stored in the DataSet, This is because: Update accepts the parameters of DataSet, and WebService only supports

Code example

for serialization operation

Assume that the data set to be updated is: dsCompany (which includes two tables, the main table and the sub table) table, and stores

data, to be updated)

//dtCompanyMain stores company main table information, dtCompanySon stores company sub-table information

DataTable dtCompanyMail = ds.Tables["dtCompanyMain"].Clone(); //Separate the main table data

and store another object

DataTable dtCompanySon = ds.Talbes[ "dtCompanySon"].Clone(); //Separate the sub-table data

and store another object

DataSet dsAdded = new DataSet(); //Storage the newly added data of the main table

DataSet dsMidified = new DataSet(); //Storage the main table Table edited row record

DataSet dsDeleted = new DataSet(); //Storage the main table deleted row record

dsAdded = dtCompanyMain.GetChanges(DataRowState.Added); //Get the new row

record set

dsMidified = dtCompanyMain.GetChanges (DataRowState.Modified); //Get the row record set

edited

the row record set

deleted in the main table

2. Update the DataRowState status of the main table to: Added and Modified records.

SqlDataAdapter.Update(dsAdded,"dtCompanyMain"); //Update the added record set to the database

SqlDataAdapter.Update(dsModified,"dtCompanyMain"); //Update the modified record set to

the database

3. Update sub Table DataRowState status is: Added and Modified records

... //Code omitted, similar to 2 main table update

4. Update sub-table DataRowState status: Deleted records.

... ......... // Code omitted, similar to 5 below

5. Update the main table DataRowState status to: Deleted records

SqlDataAdapter.Update(dsDeleted, "dtCompanyMain"); // Update the modified record set to the database

Thanks for reading!