ADO.NET 3.5 Cookbook, 2nd Edition

5.18. Capturing Changes to Data in a SQL Server Database

Problem

You need to automatically capture changes made to data in a table in a SQL Server 2008 database.

Solution

Use change data capture as shown in this solution.

The solution uses a table named CaptureDataChanges in the AdoDotNet35Cookbook database. Execute the following T-SQL statement to create the table:

	USE AdoDotNet35Cookbook
	GO
	CREATE TABLE CaptureDataChanges (
	    Id int NOT NULL PRIMARY KEY,
	    Field1 nvarchar(50) NULL,
	    Field2 nvarchar(50) NULL )

The solution needs some sample data in the table CaptureDataChanges. Create the data by executing the following T-SQL batch:

	USE AdoDotNet35Cookbook
	GO
	INSERT INTO CaptureDataChanges VALUES (1, 'Field1.1', 'Field2.1')
	INSERT INTO CaptureDataChanges VALUES (2, 'Field1.2', 'Field2.2')
	INSERT INTO CaptureDataChanges VALUES (3, 'Field1.3', 'Field2.3')

You need to enable change data capture on the database that you want to monitor. Do this by executing the stored procedure sys.sp_cdc_enable_db_change_data_capture in the database. For the AdoDotNet35Cookbook database used in this solution, execute the following command:

	USE AdoDotNet35Cookbook
	GO
	sys.sp_cdc_enable_db_change_data_capture

Once CDC is enabled on the database, a new user cdc is created, a new user role cdc_admin is created, and five CDC tables are created in the database:

• cdc.captured_columns

• cdc.change_tables

• cdc.ddl_history

• cdc.index_columns

• cdc.lsn_time_mapping

Verify that data capture is enabled on the database by querying the ...