. NET 2.0 SqlDependency Quick Start Guide

This article is based on SQL Server 2005 implementation, SQL Server 2000 because the implementation mechanism is different, please refer to the relevant information.

Step one: Execute alter DATABASE <DatabaseName> SET enable_broker on SQL Server 2005 to have the appropriate database enable the listening service to support the SqlDependency feature.

This statement is best performed without any transactions being performed by the database.

Step Two: Call the Sqldependency.start (String strConnectionString) method to enable the dependent listener on the application side.

The parameter of the method is a connection string for a database, which must have performed a step-one operation.

For the same connection string, if the statement has already been executed, no exception will occur again, but the return value will be false.

If used in a Web program, it is recommended that the statement be executed in the Application_Start event.

Listening is based on the database, and dependencies can be based on tables or queries.

Step three: There are two different approaches to this step. Steps must be taken in this phase.

Method A: Establish a Connection object, create a SqlCommand instance, create an SqlCacheDependency instance, and then invoke the command object to fetch the data (this order is important). Then invoke the cache INSERT statement to establish a cache entry that relies on a specific query dataset.

SqlConnection conn = new SqlConnection (strconnection);
SqlCommand command = new SqlCommand (STRCOMMANDTEXT, conn);
SqlCacheDependency dependency = new SqlCacheDependency (command);

Registers the method to the delegate, which is the
CacheItemRemovedCallback onremove = new CacheItemRemovedCallback (removedcallback);

Add or modify a cache record
Cache.Insert (Strcachekey, Objappcache, dependency, absoluteexpiration, slidingexpiration, Cacheitempriority.default , onremove);

Method B: Establish a Connection object, create a SqlCommand instance, and finally create a SqlDependency instance. Define the SqlDependency delegate onchange, and make the appropriate processing (such as clearing cache) when the data changes.

SqlConnection conn = new SqlConnection (strconnection);
SqlCommand command = new SqlCommand (STRCOMMANDTEXT, conn);
SqlCacheDependency dependency = new SqlCacheDependency (command);
Dependency. OnChange + = new Onchangeeventhandler (dependency_onchange);

Precautions:

I do not know whether there are bugs, I encountered some strange phenomenon in the development of the project. The same code, running on some machines can capture the change, some are completely unresponsive, and sometimes the cache has just been established to repeatedly rely on changes in the event. Occasionally the cache also changes data but does not raise events.

But from the end of the project, it seems that the uncertainty is caused by some machine environment. This is not certain. However, at least the database side is turned on properly, and you can view monitoring through SQL Server Profiler.