ADO.NET Cookbook

4.7. Updating a Data Source with Data from a Different Data Source

Problem

You want to update a data source using changes made to another data source for data replication or auditing purposes.

Solution

Use the GetChanges( ) method of the DataSet to identify changes made to a DataSet and replicate these changes into a different data source.

The sample code contains two event handlers:

Form.Load

Sets up the example by setting up two DataSet objects each containing a single table. The first table is filled with the Customers table from Northwind stored in SQL Server; the second is filled with the Customers table from Northwind stored in a MSDE instance. The default view of each table is bound to a data grid on the form.

Update Destination Button.Click

Creates a new DataSet containing only the records that have changed in the original data source. This DataSet is then used to apply the changes to a second data source using its DataAdapter; the DataSet for the second data source is reloaded. Finally, the first data source is updated with the changes.

The C# code is shown in Example 4-10.

Example 4-10. File: UpdateDataFromDifferentDataSourceForm.cs

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

private DataSet dsSource, dsDest;
private SqlDataAdapter daSource, daDest;

//  . . . 

private void UpdateDataFromDifferentDataSourceForm_Load(object sender,
    System.EventArgs e)
{
    // Create the DataAdapter for the source records.
    daSource = new SqlDataAdapter("SELECT * FROM Customers",
        ConfigurationSettings.AppSettings["Sql_ConnectString"]);
    SqlCommandBuilder cbSource = new SqlCommandBuilder(daSource);
    dsSource = new DataSet( );
    // Get the schema and data for the source.
    daSource.FillSchema(dsSource, SchemaType.Source, "Customers");
    daSource.Fill(dsSource, "Customers");
    // Bind the default view of the customers table to the grid.
    dataGridSource.DataSource = dsSource.Tables["Customers"].DefaultView;

    // Create the DataAdapter for the destination records.
    daDest = new SqlDataAdapter("SELECT * FROM Customers",
        ConfigurationSettings.AppSettings["Sql_Msde_ConnectString"]);
    SqlCommandBuilder cbDest = new SqlCommandBuilder(daDest);
    dsDest = new DataSet( );
    // Get the schema and data for the destination.
    daDest.FillSchema(dsDest, SchemaType.Source, "Customers");
    daDest.Fill(dsDest, "Customers");
    // Bind the default view of the customers table to the grid.
    dataGridDest.DataSource = dsDest.Tables["Customers"].DefaultView;
}

private void updateDestButton_Click(object sender, System.EventArgs e)
{
    try
    {
        // Create a DataSet of the added, modified, and deleted records.
        DataSet dsDelta = dsSource.GetChanges(DataRowState.Added |
            DataRowState.Modified | DataRowState.Deleted);
        if (dsDelta != null)
            // Update the destination with the delta DataSet.
            daDest.Update(dsDelta, "Customers");

        // Reload the destination DataSet.
        dsDest.Clear( );
        daDest.Fill(dsDest, "Customers");

        // Update the source.
        daSource.Update(dsSource, "Customers");
    }
    catch(Exception ex)
    {
        MessageBox.Show("ERROR: " + ex.Message, "Fill Destination",
            MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

Discussion

The ADO.NET DataSet contains data and schema information within its contained objects, but not information about the provider that was used to retrieve the data or the original source of the data. The DataSet tracks changes made to data by maintaining multiple versions of each row allowing the data to be reconciled later to a data source using a DataAdapter. The data source to which the DataSet is reconciled is usually, but does not have to be, the original data source.

The GetChanges( ) method of the DataSet creates a copy of the DataSet containing all changes that have been made to it since it was last retrieved from the data source or since AcceptChanges( ) was last called.

To replicate the changes made to the first data source into the second data source, the GetChanges( ) method of the first DataSet is called to retrieve a subset of rows that have been added, modified, or deleted. This is the subset returned if the DataRowState filter argument is not specified. Next, the Update( ) method of the destination DataAdapter is called using the DataSet containing the changes as the data object argument; this applies the changes to the destination data source. The destination DataSet is then cleared and reloaded to reflect the applied changes. Finally, the changes are applied to the first data source.

The technique demonstrated in this example relies on the changes made to a DataSet and can therefore be used only to keep a second data source synchronized to a data source that is being modified. It is called one-way replication. The destination data source server does not have to be the same as the source database server, so an Oracle table could be synchronized to reflect all changes made to a SQL Server table. In fact, the data sources do not even have to be databases. If the destination data is not identical to the source data or if the destination is updated outside of this synchronizing application, primary key violations will occur if records with the same primary key as the source are inserted into the destination. Concurrency errors will result if records are modified within or deleted from the destination source. You could use application-specific logic to handle the DataAdapter.RowUpdating to resolve these concurrency errors. For more information about the RowUpdating event, see the Discussion section in Recipe 6.7.

The technique demonstrated in this example requires only slight modification to create an audit trail of changes made to a DataSet. Instead of using the update logic generated by the CommandBuilder for the destination DataAdapter, create custom update logic to write the changes made to the source data, along with any other required audit information such as a user ID or the date and time of the change, to the data destination. One or more values from the DataRowState enumeration can be used to filter the changes returned by the GetChanges( ) method to further control the logging.