Sqldependency cache processing in Asp.net

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. When data in the database changes, the application is notified to update the 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:

 

Select databasepropertyex ('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:

Alter database <Database Name> set enable_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 add this code to the application_start method of Global. asax.
Sqldependency. Stop (connectionstring); // used to disable the function. 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, sqlnotifeventeventargs E)
{
Webcache. Remove ("systemparam ");
}
}


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