Use sqldependency for caching

Source: Internet
Author: User

Caching is a function that must be involved in every system. Generally, caching has a problem-when to clear it? For example, you can set an expiration time for the cache in ASP. NET, but how long is it appropriate? If it is too long, the meaning of the cache will be lost. This problem can be solved by using sqldependency for caching.

Sqldependency is a type encapsulated by. net2.0. It must be used with sql2005 or later.

The sqldependency class must be supported by the database's servicebroker. The application is notified when data in the database changes.ProgramUpdate cache. This is the most effective cache method.

 

Step 1:

The servicebroker service must be enabled for SQL database. First, check whether servicebroker is enabled. The check method is as follows:

 

  SelectDatabasepropertyex ('Database Name','Isbrokerenabled')

 

 

-- 1 indicates that 0 is enabled, indicating that 0 is not enabled.

 

Step 2:

If servicebroker is not enabled, use the following statement:

  AlterDatabase<Database Name>SetEnable_broker;

 

Step 3:

In the process of implementing service-based SQL data cache dependencies, You need to explicitly call sqldependency. Start to start the listener that receives the notification of dependency changes.

 

  Sqldependency. Start (connectionstring );//We recommend that you use this sectionCodeAdded to the application_start method of Global. asax.
Sqldependency. Stop (connectionstring );//Used to disable it. It can be added to the application_end method of Global. asax.

 

Step 4: cache implementation

Code for caching using sqldependency:

 

    Public     Class  Cachehelper
{
Static Cache webcache = Httpcontext. Current. cache;
Static String Defaultconn = Configurationmanager. connectionstrings [ " Default " ]. Connectionstring;

Public Static Datatable getsystemparams ()
{
If (Webcache [ " Systemparam " ] = Null )
{
String Strsql = " Select usystemparamid, paramname, paramvalue, description from DBO. dts_systemparam " ;
Sqldataadapter da = New Sqldataadapter (strsql, defaultconn );
Sqldependency Dep = New Sqldependency (DA. selectcommand );
Dep. onchange + = New Onchangeeventhandler (dep_onchange );
Datatable TBL = New Datatable ();
Da. Fill (TBL );
Webcache [ " Systemparam " ] = TBL;
Return TBL;
}
Else
{
Return (Datatable) webcache [ " Systemparam " ];
}
}

Private Static Void Dep_onchange ( Object Sender, sqlnotificationeventargs E)
{
Webcache. Remove ( " Systemparam " );
}
}

 

Note:

to subscribe to query notifications using sqldependency, you must provide SQL Server service broker with query statements for rule-making. Generally, it must be a simple SQL query statement ( cannot use *, cannot use top, cannot use functions, including Aggregate functions, cannot use subqueries, including subqueries after where, cannot use external connections, self-join: temporary tables cannot be used, variables cannot be used, views cannot be used, and databases cannot be broken down, before a table name, you must add a prefix like the DBO database owner ) for example, select * From Table1, select column1 from Table1, select count (*) from Table1 are all incorrect SQL query statements, select column1 from DBO. table1 is the correct statement.

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.