Recipe 5.6 Merging Data

Problem

You have two DataSet objects with the same schema, but containing different data. You need to combine data from these two DataSet objects without creating duplicate rows.

Solution

Use the DataSet.Merge( ) method with the appropriate MissingSchemaAction enumeration values.

The sample code contains four event handlers and a single method:

Form.Load

Sets up the sample by creating two DataSet objects each with a single DataTable containing different subset of data from the Employees table in Northwind. The default view for each table is bound to a data grid on the form.

MergeA Button.Click

The first Button.Click calls the Merge( ) method to merge the first DataSet into the second.

MergeB Button.Click

A second Button.Click calls the Merge( ) method to merge the second DataSet into the first.

Clear Button.Click

A third Button.Click clears the data grid displaying the results of either merge.

Merge( )

This method takes two DataTable arguments. The first DataTable is copied to a new DataTable and the second DataTable is merged into it with the specified MissingSchemaAction. The default view of the result DataTable is bound to a data grid on the form.

The C# code is shown in Example 5-6.

Example 5-6. File: MergingDataForm.cs

// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;

private SqlDataAdapter daA, daB;
private DataSet dsA, dsB;

//  . . . 

private void MergingDataForm_Load(object sender, System.EventArgs e)
{
    // Fill the schema and data for table A.
    String sqlText = "SELECT EmployeeID, LastName, FirstName, Title " +
        "FROM Employees WHERE EmployeeID BETWEEN 1 AND 5";
    // Set up the DataAdapter and CommandBuilder for table A.
    SqlCommandBuilder cbA = new SqlCommandBuilder(daA);
    daA = new SqlDataAdapter(sqlText,
        ConfigurationSettings.AppSettings["Sql_SqlAuth_ConnectString"]);
    // Define DataSet A and fill its table A with schema and data.
    dsA = new DataSet("A");
    daA.FillSchema(dsA, SchemaType.Source, "Employees");
    daA.Fill(dsA, "Employees");
    // Bind the default view for table A to the grid.
    dataGridA.DataSource = dsA.Tables["Employees"].DefaultView;

    // Fill the schema and data for table B.
    sqlText = "SELECT EmployeeID, LastName, FirstName, " +
        "BirthDate, HireDate " +
        "FROM Employees WHERE EmployeeID BETWEEN 4 AND 8";
    // Set up the DataAdapter and CommandBuilder for table B.
    daB = new SqlDataAdapter(sqlText,
        ConfigurationSettings.AppSettings["Sql_SqlAuth_ConnectString"]);
    SqlCommandBuilder cbB = new SqlCommandBuilder(daB);
    // Define DataSet B and fill its table B with schema and data.
    dsB = new DataSet("B");
    daB.FillSchema(dsB, SchemaType.Source, "Employees");
    daB.Fill(dsB, "Employees");
    // Bind the default view for table B to the grid.
    dataGridB.DataSource = dsB.Tables["Employees"].DefaultView;
}

private void Merge(DataTable dtSource, DataTable dtDest)
{
    // Set the missing schema value to the default and read
    // actual value, if otherwise, from the radio buttons.
    MissingSchemaAction msa = MissingSchemaAction.Add;

    if(addWithKeyRadioButton.Checked)
        msa = MissingSchemaAction.AddWithKey;
    else if(errorRadioButton.Checked)
        msa = MissingSchemaAction.Error;
    else if(ignoreRadioButton.Checked)
        msa = MissingSchemaAction.Ignore;

    // Create the merge DataSet and copy table B into it.
    DataSet ds = new DataSet("Merge");
    ds.Tables.Add(dtDest.Copy( ));

    try
    {
        // Merge table A into the DataSet.
        ds.Merge(dtSource, false, msa);
    }
    catch (Exception ex)
    { 
        MessageBox.Show(ex.Message);
    }

    // Bind the merge result table default view to the grid.
    dataGridMerge.DataSource = ds.Tables[0].DefaultView;
    dataGridMerge.CaptionText = "Merge Results: " +
        dtSource.DataSet.DataSetName + " into " +
        dtDest.DataSet.DataSetName;
}

private void mergeAIntoBButton_Click(object sender, System.EventArgs e)
{
    Merge(dsA.Tables["Employees"], dsB.Tables["Employees"]);
}

private void mergeBIntoAButton_Click(object sender, System.EventArgs e)
{
    Merge(dsB.Tables["Employees"], dsA.Tables["Employees"]);
}

private void clearResultsButton_Click(object sender, System.EventArgs e)
{
    dataGridMerge.DataSource = null;
    dataGridMerge.CaptionText = "";
}

Discussion

The Merge( ) method of the DataSet can merge a DataRow array, a DataTable, or a DataSet into an existing DataSet. If the existing DataSet has a primary key defined, the incoming data is matched to rows having the same primary key values. Where matches are found, the existing row is updated with the new values. Otherwise, rows are appended to the existing table.

There are two arguments that can be optionally specified in the overloaded Merge( ) methods.

The first, preserveChanges, is a Boolean value that indicates whether incoming values will overwrite changes made to the existing DataSet. If preserveChanges is false, the default, both the Current and Original row are overwritten with incoming values and the RowState of the row is set to the RowState of the incoming row. Exceptions are shown in Table 5-2.

Table 5-2. Exceptions to PreserveChanges argument when PreserveChanges = false.

Incoming RowState	Existing RowState	New RowState
Unchanged	Modified, Deleted, or Added	Modified
Added	Unchanged, Modified, or Deleted	Modified. Also, data in the Original version of the existing row is not overwritten because the Original version of the incoming row does not exist.

If preserveChanges is specified as true, the values in the Current version of the existing row are maintained while values in the Original version of the existing row are overwritten with the Original values for the incoming row. The RowState of the existing row is set to Modified. Exceptions are shown in Table 5-3.

Table 5-3. Exceptions to PreserveChanges argument when PreserveChanges = true

Incoming RowState	Existing RowState	New RowState
Any	Deleted	Deleted
Added	Any	Modified. Data in the Original version of the row is not overwritten because the Original version of the incoming row does not exist.

The second argument is the missingSchemaAction argument, which accepts a value from the MissingSchemaAction enumeration that specifies how the Merge( ) method will handle schema elements in the incoming data that are not part of the existing DataSet. Table 5-4 describes the values in the MissingSchemaAction enumeration.

Table 5-4. MissingSchemaAction enumeration

Value	Decription
Add	Add the new schema information and populate the new schema with incoming values. This is the default value.
AddWithKey	Add the new schema and primary key information and populate the new schema with incoming values.
Error	Throw an exception if the incoming schema does not match the schema of the existing DataSet.
Ignore	Ignore new schema information.