ADO.NET 3.5 Cookbook, 2nd Edition

5.11. 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 solution uses a table named UpdateDifferentDataSource in each database—SQL Server source database and SQL Server Express destination database—with the schema shown in Figure 5-18.

Figure 5-18. Schema for table UpdateDifferentDataSource

The T-SQL DDL to create the table in each data source is shown in Example 5-18.

Example 5-18. Create table UpdateDifferentDataSource

USE AdoDotNet35Cookbook
GO
CREATE TABLE UpdateDifferentDataSource (
    Id int NOT NULL,
    Field1 nvarchar(50) NULL,
    Field2 nvarchar(50) NULL,
  CONSTRAINT PK_UpdateDifferentDataSource PRIMARY KEY CLUSTERED
    ( Id ASC )
)

The solution also requires some data in the table UpdateDifferentDataSource in both the SQL Server and SQL Server Express databases. Initially these tables will both contain identical data. The T-SQL to add three initial records to the table UpdateDifferentDataSource is shown in Example 5-19.

Example 5-19. Insert initial records into table UpdateDifferentDataSource

USE AdoDotNet35Cookbook GO DELETE FROM UpdateDifferentDataSource; INSERT INTO UpdateDifferentDataSource VALUES (1, 'field ...