ADO - ADO.NET DataSet Merge Method and Conflict Resolution

The Merge() method in ADO.NET is used to combine the contents of one DataSet, DataTable, or collection of data rows with another DataSet or DataTable. It is especially useful when an application receives updated or additional data from different sources and needs to incorporate that data into an existing in-memory dataset. Instead of manually copying every row and column, the Merge() method can synchronize the contents of two data structures while preserving relationships, schemas, and row states where applicable.

Purpose of the Merge Method

A DataSet can contain multiple DataTable objects, relationships between tables, and information about the current and original values of rows. When another dataset contains new or modified information, Merge() allows the application to combine the two datasets.

For example, suppose an application has an existing DataSet containing customer information:

CustomerID    Name       City
101           Ravi       Mysore
102           Anitha     Bengaluru

A second DataSet received from the database may contain:

CustomerID    Name       City
102           Anitha     Chennai
103           Kiran      Madikeri

After merging, the resulting dataset can contain the new customer and the updated information:

CustomerID    Name       City
101           Ravi       Mysore
102           Anitha     Chennai
103           Kiran      Madikeri

The exact result depends on the schema, primary keys, row states, and options used during the merge.

Basic Syntax

The commonly used syntax is:

DataSet destination = new DataSet();
DataSet source = new DataSet();

destination.Merge(source);

Here, destination is the dataset into which the information is merged, while source contains the data being incorporated.

A DataTable can also be merged:

DataTable table1 = new DataTable();
DataTable table2 = new DataTable();

table1.Merge(table2);

The method has additional overloads that provide control over how the merge should be performed.

Using the PreserveChanges Option

One important feature of Merge() is the preserveChanges parameter. It determines whether changes already made in the destination dataset should be preserved.

For example:

destination.Merge(source, true);

When true is specified, changes that have already been made to the destination are preserved as much as possible during the merge.

When false is specified:

destination.Merge(source, false);

the incoming source data can replace corresponding values in the destination.

This distinction becomes important when an application allows users to edit data locally before receiving updated information from a database.

Example of Preserving Local Changes

Consider a customer record:

CustomerID: 101
Name: Ravi
City: Bengaluru

The user changes the city locally:

CustomerID: 101
Name: Ravi
City: Mysuru

Meanwhile, updated information arrives from the database:

CustomerID: 101
Name: Ravi
City: Bengaluru

If the application performs:

destination.Merge(source, true);

the local modification can be preserved rather than immediately being replaced by the incoming value.

This is useful in disconnected applications where users may modify data while they are not directly connected to the database.

Understanding Conflict Resolution

A merge conflict occurs when the destination dataset and source dataset contain different versions of the same record. This usually happens when the same row has been changed in two different places.

For example, the original database value might be:

CustomerID: 101
City: Bengaluru

The local application changes it to:

CustomerID: 101
City: Mysuru

Another user changes the same record in the database to:

CustomerID: 101
City: Chennai

When the updated database data is merged into the local dataset, there are now two different versions of the same record.

ADO.NET uses row states and original/current values to help manage such situations. Developers need to decide which version should ultimately be accepted before sending changes back to the database.

Primary Keys and Record Matching

Primary keys play an important role in the merge operation. ADO.NET uses the table schema, particularly primary-key information, to determine which records correspond to one another.

Suppose the destination contains:

ID    Name
1     Ravi
2     Anitha

and the source contains:

ID    Name
2     Anitha Sharma
3     Kiran

If ID is defined as the primary key, ADO.NET can recognize that record 2 already exists and that record 3 is new.

The resulting data can therefore contain the updated record and the newly added record.

For this reason, correctly defining primary keys is important when using Merge() for synchronization.

Schema Merging

Merge() can also deal with differences between the schemas of the source and destination datasets.

For example, the destination table might contain:

ID
Name

while the source table contains:

ID
Name
Email

During the merge, the additional Email column can be incorporated into the destination table, depending on the merge configuration and schema.

Developers should nevertheless design schemas carefully because automatically incorporating schema differences may not always produce the structure expected by the application.

MissingSchemaAction

The MissingSchemaAction setting can be used to control what happens when the source contains schema elements that are missing from the destination.

For example:

destination.Merge(
    source,
    true,
    MissingSchemaAction.Add
);

MissingSchemaAction.Add allows missing schema elements to be added.

Other options can be used depending on the desired behavior, including:

Add
AddWithKey
Ignore
Error

AddWithKey is particularly useful when primary-key information needs to be included as part of the schema.

Merging Specific DataTables

It is not always necessary to merge an entire DataSet. A particular DataTable can be merged with another table.

For example:

DataTable customerTable = new DataTable();
DataTable updatedCustomerTable = new DataTable();

customerTable.Merge(updatedCustomerTable);

This approach is useful when an application is working with a specific collection of records rather than an entire database structure.

Merge and Disconnected Applications

The Merge() method is particularly useful in disconnected data access scenarios. A client application can retrieve data, work with it locally, disconnect from the database, and later receive updated information.

A typical workflow could be:

Database
   |
   v
DataSet
   |
   v
Local modifications
   |
   v
Updated DataSet received
   |
   v
Merge()
   |
   v
Resolve conflicts
   |
   v
Send accepted changes back

This approach is useful because the application does not need to maintain a continuous database connection while users are working with the data.

Difference Between Merge and Copy

Merge() should not be confused with methods such as Copy() or Clone().

Copy() creates a separate copy of a DataSet, including its data and schema.

Clone() creates a new dataset containing the schema but not the data.

Merge() is different because its purpose is to combine data from one dataset or table with another existing dataset or table.

In simple terms:

Clone  → Schema only
Copy   → Schema + Data
Merge  → Combine incoming data with existing data

Practical Example

Consider an application that downloads customer updates periodically:

DataSet localData = GetLocalCustomerData();
DataSet updatedData = GetUpdatedCustomerData();

localData.Merge(
    updatedData,
    true,
    MissingSchemaAction.AddWithKey
);

In this example, the updated customer information is merged into the local dataset. The true value indicates that existing local changes should be preserved, while AddWithKey allows missing schema information, including key information, to be incorporated.

After the merge, the application can examine the resulting rows and determine whether any records require conflict resolution before updating the database.

Advantages of DataSet Merge

The Merge() method provides several benefits:

  1. It simplifies the process of combining datasets.

  2. It can identify matching records using primary-key information.

  3. It supports disconnected data applications.

  4. It can incorporate new records and updated records.

  5. It provides options for preserving local changes.

  6. It can handle differences between source and destination schemas.

  7. It reduces the need for manually copying rows and columns.

  8. It is useful when synchronizing data received at different times.

Limitations and Considerations

Although Merge() is powerful, developers should use it carefully. A merge does not automatically mean that every data conflict has been logically resolved according to the application's business rules. When two versions of the same record have different modifications, the application may need additional logic to determine which change should be accepted.

Primary keys and schema definitions should also be properly configured. Incorrect key definitions can cause records to be treated as new records rather than updates to existing records.

Conclusion

The ADO.NET DataSet.Merge() method provides a convenient mechanism for combining data from separate datasets or tables. Its importance is particularly evident in disconnected applications, where local data can be modified independently and later synchronized with incoming data. Features such as preserveChanges, primary-key matching, schema merging, and MissingSchemaAction provide developers with control over how the merge takes place. Understanding these features helps developers build applications that can efficiently handle updated, newly added, and potentially conflicting data.