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.

Connecting to Data

Retrieving and Managing Data

Searching and Analyzing Data

Adding and Modifying Data

Copying and Transferring Data

Maintaining Database Integrity

Binding Data to .NET User Interfaces

Working with XML

Optimizing .NET Data Access

Enumerating and Maintaining Database Objects

Appendix A. Converting from C# to VB Syntax



ADO. NET Cookbook
ADO.NET 3.5 Cookbook (Cookbooks (OReilly))
ISBN: 0596101406
EAN: 2147483647
Year: 2002
Pages: 222
Authors: Bill Hamilton

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net