ADO.NET 3.5 Cookbook, 2nd Edition

10.20. Automatically Refreshing a DataTable When Underlying Data Changes

Problem

You need to refresh a DataTable when the data in the underlying data source changes.

Solution

Use the SqlDependency class to create a query notification.

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

	USE AdoDotNet35Cookbook
	GO
	SET ANSI_NULLS ON
	GO
	SET QUOTED_IDENTIFIER ON
	GO
	CREATE TABLE AutoRefreshChange(
	    Id int NOT NULL PRIMARY KEY,
	    Field1 nvarchar(50) NULL,
	    Field2 nvarchar(50) NULL )

Add three records to the AutoRefreshChange table by executing the following T-SQL batch:

	USE AdoDotNet35Cookbook
	GO

	INSERT INTO AutoRefreshChange VALUES (1, 'Field1.1', 'Field2.1');
	INSERT INTO AutoRefreshChange VALUES (2, 'Field1.2', 'Field2.2');
	INSERT INTO AutoRefreshChange VALUES (3, 'Field1.3', 'Field2.3');

SQL Server databases do not have Service Broker enabled by default, for security reasons. Enable Service Broker for the AdoDotNet35Cookbook database by executing the following T-SQL statement:

	USE AdoDotNet35Cookbook
	GO

	ALTER DATABASE AdoDotNet35Cookbook SET ENABLE_BROKER

You can confirm that Service Broker is now enabled for the database by using the DATABASEPROPERTYEX function, as shown in the following T-SQL statement:

	SELECT DATABASEPROPERTYEX('AdoDotNet35Cookbook', 'IsBrokerEnabled')

The function returns 0 for false and 1 for true.

The solution creates a Connection to the AdoDotNet35Cookbook database and a DataAdapter ...