. NET 2.0 sqldependency Quick Start Guide

This article usesSQL Server 2005The above implementation prevails,SQL Server 2000For different implementation mechanisms, please refer to relevant materials.

 

Step 1: in SQL Server 2005 run on alter database set enable_broker; the statement enables the listener service for the corresponding database to support sqldependency feature.

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

 

Step 2: Call SQL Dependency . start ( string strconnectionstring) method, enable dependency listening on the Program side of the application.

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 isFalse.

IfWebIt is recommended that you put this statement inApplication_startEvent execution.

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 and then create a connection object. Sqlcommand Instance, create Sqlcachedependency Instance. After this step, call the command object to obtain data (this order is important ). Call later Cache Of Insert Statement to create a dataset dependent on a specific query Cache .

SqlconnectionConn =New Sqlconnection(Strconnection );

SqlcommandCommand =New Sqlcommand(Strcommandtext, Conn );

SqlcachedependencyDependency =New Sqlcachedependency(Command );

//Register the method to the delegate, which is

CacheitemremovedcallbackOnremove =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 and then create a connection object. Sqlcommand Instance, and finally create Sqldependency Instance. Definition Sqldependency Delegate Onchange , When the data changes, make corresponding processing (such as clearing Cache ).

SqlconnectionConn =New Sqlconnection(Strconnection );

SqlcommandCommand =New Sqlcommand(Strcommandtext, Conn );

SqlcachedependencyDependency =New Sqlcachedependency(Command );

Dependency. onchange + =New Onchangeeventhandler(Dependency_onchange );

 

 

Note:

I don't know if it still exists bug I encountered some strange things during project development.. For the same Code , changes can be captured when running on some machines, while others are completely unresponsive; sometimes cache the dependency change event occurs repeatedly upon creation. Occasionally cache data changes but no events are triggered.

but from the implementation of the final project, it seems that it is only the uncertainty caused by some machine environments? This cannot be determined. At least whether the database is enabled properly. You can use SQL Server Profiler to view monitoring.