Research on the Merge method of DataSet

In Ado.net, when we merge two identical or similar dataset objects, we usually use the dataset's Merge method, which has multiple overloaded versions, and we review the merge method before introducing it, and here's how to use the Merge method in MSDN:



The merge method is used to consolidate two DataSet objects that are roughly similar to the schema. Merging on a client application is typically used to merge the most recent changes in the data source into an existing dataset. This enables the client application to have a dataset refreshed with the latest data from the data source. The Merge method is typically called at the end of a series of procedures involving validating changes, eliminating errors, updating the data source with changes, and finally refreshing the existing dataset.

In a client application, there is usually a button that the user can click to collect and validate the changed data, and then send it back to the middle tier component. In this case, the GetChanges method is invoked first. This method returns another DataSet that is optimized for validation and merging. The second DataSet object contains only the changed DataTable and DataRow objects, resulting in a subset of the initial dataset. This subset is usually small, so it can be passed back to the middle tier component more efficiently. The middle-tier component then uses the changes to update the initial data source through the stored procedure. The middle tier can then send back a new dataset that contains the initial data and the latest data from the data source (by running the initial query again), or it can send back a subset of all changes made to it from the data source. (for example, if your data source automatically creates unique primary key values, you can propagate these values back to the client application.) In either case, you can use the Merge method to merge the returned dataset back into the client application's initial dataset.

When a new source DataSet is merged into the target, any source rows with the DataRowState value of unchanged, Modified, or Deleted will match the target row with the same primary key value. The source row with the DataRowState value of Added matches the new target row with the same primary key value as the Xinyuan row.


Based on the instructions above, we know that the merge method combines the primary key value of the row with the main contrast reference when merging two datasets. This does not have any problems adding new rows to the dataset, but it doesn't matter if you modify the row without modifying the primary key value, but if you modify the value of the primary key when you change the line, the problem comes ... Here's an example:

A Products table structure under SQL Server is as follows:

Column Name
Data type
Description

Code
NChar
Product Code (primary key column)

Name
nvarchar
Name of production name

UnitPrice
Numeric
Product price


Using XSD in. NET to generate a corresponding productsdata.xsd structure is as follows:

Column Name
Data type
Description

Code
String
Product Code (primary key column)

Name
String
Name of production name

UnitPrice
Decimal
Product price


As explained by MSDN, when we submit background updates after the foreground is added or modified via productsdata, it is usually done as follows:

Create a new dataset to save changes to the primary dataset

Productsdata datasetchanges;

Datasetchanges = (productsdata) (Productsdata.getchanges ());

Check to see if any changes have been made

if (datasetchanges!= null) {

try {

You need to make some changes, so try calling the Update method

and pass data sets and any parameters to update the data source

Updatedatasource (datasetchanges);

Productsdata.merge (datasetchanges);

Productsdata.acceptchanges ();

}

catch (System.Exception eupdate) {

Throw eupdate;

}

}

The above code is based on the Vs.net data form Generation Wizard, based on the above code we simulate adding a row of data to a dataset and updating it:

Code
Name
UnitPrice

1001
Golden Sand Chocolate
120.00


No problem, the following we modify this line of data to update, here we change the code to 1002, after the update result data did not as we expected the original line of the code column of the value 1001 to 1002, but added a row:

Code
Name
UnitPrice

1001
Golden Sand Chocolate
120.00

1002
Golden Sand Chocolate
130.00


Note: Normally we rarely change the primary key value, but in the case where the code is not used, it is generally allowed to change code, especially at the stage of system implementation.

The cause of the above problem is not surprising, according to the principle of the merge method, this is true, but this is what we do not want, how to solve it, do not allow the user to change the primary key, but it seems to be not realistic. How to solve it?

Since to use the merge method, that only according to merge the data principle to do, not to change the primary key we do not change the primary key, we can give the foreground of the Productsdata DataSet add an additional primary key ID column, and its AutoIncrement set to True, Change the original primary key column code to the key column, as the source table in the background SQL Server does not make any modifications, as follows:

Column Name
Data type
Description

Id
Int
Auto Grow column (primary key)

Code
String
Product code (key key)

Name
String
Name of production name

UnitPrice
Decimal
Product price


After this change, because the ID is automatically an incremental column, we do not modify its value, so that we can arbitrarily change the value of the Code column, the merge method in the merging of data is based on the ID example to compare, so it will not appear in front of the Cadogan line problem.

PostScript: This is my blog to write a good post about the first article on technology, since the feeling of writing a bit wordy, in fact, write so long useful only the last few lines, which may be for those. NET masters are just tricks, so let the experts laughed at.