SQL data cache

SQL Server Service Broker provides SQL Server database engine support for message and queue applications. This allows developers to easily create complex applications that use database engine components to communicate with different databases. Developers can use service brokers to easily generate reliable distributed applications.

Application developers using service brokers can distribute data workloads across multiple databases without having to write complex internal communications and messages. Because the service broker processes the communication path in the session context, this reduces the development and testing work. It also improves the performance. For example, frontend databases that support websites can record information and send process-intensive tasks to backend databases for queuing. Service Broker ensures that all tasks are managed in the transaction context to ensure reliability and technical consistency. (Official Website: http://technet.microsoft.com/zh-cn/library/bb522893 (V = SQL .105). aspx)

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. 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 ('campr', 'isbrokerenabled') -- camnpr indicates the database name.

-- 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> set enable_broker;

Sometimes the startup fails:

 

Solution: Disable the Agent Proxy function of sqlserver. Or exit SQL server2005/2008, and then run the SQL command.

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 global. sqldependency in the application_start method of asax. stop (connectionstring); // used to close, can be added to global. in the application_end method of asax

 

Step 4: cache implementation

Code for caching using sqldependency:

public class CacheHelper {

Static cache webcache = httpcontext. Current. cache;

Static string defaultconn = configurationmanager. connectionstrings ["camnpr"]. 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, sqlnotifeventargs e ){

Webcache. Remove ("systemparam ");

}

}

Note:

When using sqldependency to subscribe to query notifications, you must provide SQL Server service broker with the query statement for rule-making. Generally, it must be a simple SQL query statement (not available *, 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.