Use sqldependency for caching

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.