Cache application (SQL dependent cache)

I. Introduction

ASP. NET 2.0 has added many new functions and controls. The data caching function has greatly changed. As we all know, the data cache function is very important. We can put some data that remains unchanged for a period of time in the cache, so that we do not need to read the database every time, when you need the data again next time, it can be obtained directly from the cache, greatly enhancing the efficiency.
In earlier versions of Asp.net (1.1), the data caching function has been well implemented, but there is a small problem: if the data in the database changes, the cache cannot be updated within the specified time, but must wait until the cache expires. For example, if you use the page cache for the product details, such as the product category, on a webpage, If you modify the information in the background, the user will not immediately see this information, but will not be able to see it after a certain period of time (for example, the page cache expires. In some application scenarios, if you want to update the database, the changes in the cache can take effect immediately. In Asp.net 1.1, it is relatively difficult to implement, in Asp.net 2.0, this function can be conveniently implemented.

2. Preparations before coding

For sqlserver2005(Built-in support for SQL data cache dependencies, built-in notification delivery service, can provide smaller granularity of data change monitoring, easy to use and configure .).

1. Check whether service broker is enabled

Execute select databasepropertyex ('db name', 'isbrokerenabled'). Result: '1': broker is enabled, '0': broker is not enabled.

2. enable service broker (enable the listener service for the corresponding database to support the sqldependency feature. If the SQL statement result in step 1 is 1, skip step 2)

Alter database databasename set enable_broker

Note: Sometimes this SQL statement takes a long time to run. Disconnect the database (or simply close Microsoft SQL Server Management and re-open it) and restart the connection, it takes only a few seconds to execute this statement.

CausePossibleYes: This command may need to generate an exclusive lock. However, when the database is in the connection status, it cannot generate an exclusive lock, so it waits until it is finally suspended.

3. WriteCode

(1) When implementing service-based SQL data cache dependency, You need to explicitly call sqldependency. Start to start the listener that receives the dependency change notification, and call sqldependency. Stop to disable it.

In the global. asax File

 
Void application_start (Object sender, eventargs e) {//ProgramCode string connstr = balloonshopconfiguration. dbconnectionstring; system. data. sqlclient. sqldependency. start (connstr);} void application_end (Object sender, eventargs e) {// code string connstr = balloonshopconfiguration when the application is closed. dbconnectionstring; system. data. sqlclient. sqldependency. stop (connstr );}
 

 (2) write the following code in the application data cache: 

   
Public class catalogaccessusesqlcache {public static datatable getments ments () {string connstr = balloonshopconfiguration. dbconnectionstring; sqlconnection conn = NULL; try {conn = new sqlconnection (connstr); sqlcommand comm = new sqlcommand (); Comm. connection = conn; sqlcachedependency Dep = new sqlcachedependency (Comm); Comm. commandtype = commandtype. text; Comm. commandtext = "select distinct mentid, name, description from DBO. department "; sqldataadapter adapter = new sqldataadapter (Comm); Conn. open (); datatable dt = new datatable (); adapter. fill (DT); system. web. httpcontext. current. cache. add ("getdepartments", DT, DEP, cache. noabsoluteexpiration, cache. noslidingexpiration, cacheitempriority. default, null); Return DT;} catch (exception ex) {utilities. logerror (Ex); throw ex;} finally {Conn. close ();}}}
Private void bindgrid () {If (httpcontext. current. cache ["getments ments"] = NULL) {// get a datatable object containing the catalog ments // grid. datasource = catalogaccess. getdocumments (); list. datasource = catalogaccessusesqlcache. getdocumments ();} else {list. datasource = (system. data. datatable) httpcontext. current. cache ["getments ments"];} // bind the data bound controls to the data source list. databind ();}
 
 
(3) The SQL statement associated with the sqlcommand parameter must include the following content:
 
A. A data table with a fully qualified name must be set. That is, the table name must be preceded by the owner, such as DBO. Department.
 
 
B. You must specify the names of the columns in the accessed database. wildcards ("*") are not allowed.
 
 
For example"Select * from department"And must be used"Initialize mentid, name, description from DBO. Department".
 
C. Make sure it is not an aggregate function. Such as Count and Max
 
 
After the above steps, the code of SQL dependent cache is basically completed.
 
 
 
 
4. Introduction of related knowledge points (the content that I often talk about is easy to forget)
 
 
(1)Sqlcachedependency Initialization
 
 
Constructor: Public sqlcachedependency (sqlcommand sqlcmd ).
 
(2) differences between system. Web. caching. cache insert and add
 
 
For details about the ADD and insert methods, see msdn:
 
 
Http://msdn.microsoft.com/zh-cn/library/system.web.caching.cache.add (V = vs.80). aspx
 
Http://msdn.microsoft.com/zh-cn/library/system.web.caching.cache.insert (V = vs.80). aspx
 
 
The differences between the two are as follows:
 
 
A. the insert method supports five types of overloading and is flexible to use. The add method must provide seven parameters;
 
 
B. The add method returns the Data Object of the cache item, and the insert method returns the void;
 
C. When duplicate cache is added, insert will replace this option, and the add method will report an error.
 
 
V. Summary
 
 
This article describes some basic knowledge about ASP. NET data caching. More knowledge points and notes need to be noted during actual use.