. NET 2.0 SqlDependency Quick Start Guide

Source: Internet
Author: User

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.