Getting started with. NET 2.0 SqlDependency

This article takes the implementation of SQL Server 2005 as the standard. For details about the implementation mechanism of SQL Server 2000, see related documents.

Step 1: Execute the alter database <DatabaseName> SET ENABLE_BROKER; Statement on SQL Server 2005 to enable the listener service for the corresponding DATABASE to support the SqlDependency feature.

This statement is best executed when the database does not execute any transactions.

Step 2: Call SqlDependency. Start (String strConnectionString) to enable the dependency listener on the application side.

The parameter of this method is a connection string of a database. The database must have performed step 1.

If this statement has been executed for the same connection string, no exception will occur when it is executed again, but the returned value is False.

If it is used in a Web program, we recommend that you run this statement in the Application_Start event.

The listener is based on databases, and the dependency can be based on tables or queries.

Step 3: This step has two different practices. Pay attention to the steps in this phase.

Method A: create A connection object, create A SqlCommand instance, and create A SqlCacheDependency instance. After this step, call the Command object to obtain data (this order is important ). Then, call the Insert Statement of Cache to create a Cache item dependent on a specific query dataset.

SqlConnection conn = new SqlConnection (strConnection );

SqlCommand command = new SqlCommand (strCommandText, conn );

SqlCacheDependency dependency = new SqlCacheDependency (command );

// Register the method to the delegate, which is

CacheItemRemovedCallback onRemove = new CacheItemRemovedCallback (RemovedCallback );

// Add or modify a cache record

Cache. Insert (strCacheKey, objAppCache, dependency, absoluteExpiration, slidingExpiration, CacheItemPriority. Default, onRemove );

Method B: create a connection object, create a SqlCommand instance, and create a SqlDependency instance. Define the SqlDependency delegate OnChange. When the data changes, perform corresponding processing (such as clearing the Cache ).

SqlConnection conn = new SqlConnection (strConnection );

SqlCommand command = new SqlCommand (strCommandText, conn );

SqlCacheDependency dependency = new SqlCacheDependency (command );

Dependency. OnChange + = new OnChangeEventHandler (Dependency_OnChange );

Note:

The SQL statement associated with the strCommandText parameter must include the following:

• A fully qualified table name, including the name of the table owner.

  For example, to reference the name owned by the database owner"customers" The SQL statement must be referenced."dbo.customers".

• Explicitly specify the column name in the Select statement. You cannot use the asterisk (*) wildcard to select all columns in the table.

  For example"select * from customers"And must be used"select name, address, city, state from dbo.customers".

I don't know if there are still bugs. I encountered some strange phenomena in project development. In the same Code, changes can be captured when running on some machines, and some do not respond at all. Sometimes, the dependency changes occur repeatedly when the Cache is just created. Occasionally, the Cache changes data but does not trigger events.

But from the perspective of the implementation of the final project, it seems that all of them are just the uncertainties caused by some machine environments? This cannot be determined. However, you can check whether the database is properly enabled by using SQL Server Profiler.