Usage of SqlDependency and SqlCacheDependency caches

SqlDependency with Servicebroker for caching

Step One:

The SQL database must have the Servicebroker service turned on, first detecting if Servicebroker is enabled, and the detection method:

Select databasepropertyex (' dbname ', ' isbrokerenabled ')

--1 indicates that 0 is enabled and is not enabled

Step Two:

If Servicebroker is not enabled, use the following statement to enable:

ALTER Database < databases name > SET enable_broker;

Step Three:

In implementing a service-based SQL data cache dependency process, you need to explicitly call Sqldependency.start to start a listener that accepts dependency change notifications.

Sqldependency.start (connectionString);//recommend adding this code to the Application_Start method of Global.asax
Sqldependency.stop (connectionString);//For closing, can be added in Global.asax Application_End method

Step Four: Cache implementation

To implement cached code 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");
}
}

Usage of SqlDependency and SqlCacheDependency caches