Asp.net database cache dependency

In more cases, the performance loss of our servers is still the query of the database, so the database cache is particularly important. Some data cache functions can be implemented in the above methods. However, the problem is that our data is changing sometimes, so that the data that users may query during the cache is old data, resulting in data inconsistency. There is no way to achieve this. If the data does not change, the user will always retrieve the data from the cache. Once the data changes, the system will automatically update the data in the cache so that the user can have a better user experience.

The answer is yes !. NET has provided us with such a good solution: SqlCacheDependency database cache dependency.

Steps:

The following describes how to implement the database cache dependency function:

Step 1: modify web. config to enable SqlCacheDependency for the project.

Add the following code to the <system. web> section of web. config:

<? Xml version = "1.0"?> <! -- For more information about how to configure ASP. NET applications, visit the http://go.microsoft.com/fwlink? LinkId = 169433 --> <configuration> <system. web> <caching> <sqlCacheDependency enabled = "true" pollTime = "6000"> <databases> <add name = "codematic" connectionStringName = "strcodematic"/> </databases> </sqlCacheDependency> </caching> </system. web> <connectionStrings> <add name = "strcodematic" connectionString = "server = .; database = XLinShaWyDBase, uid = sa, pwd = 123 "/> </connectionStrings> </configuration>

ConnectionStringName specifies a connection string added in <connectionStrings>. Name indicates the name of the SqlCacheDependency, which will be used in step 1.
The SqlCacheDependency class automatically reads the configuration section information to establish a connection with the database.

　　Note:In the <databases> section, the name attribute value in <add name = "codematic" connectionStringName = "strcodematic"/> must be in the System. web. caching. sqlCacheDependency ("codematic", "dbo. the first parameter (Database Name) in Contact "); is consistent.
Step 2: execute the following command to enable cache dependency for the database.

To configure SqlCacheDependency, run the command line.

The aspnet_regsql.exe tool is located in the Windows \ Microsoft. NET \ Framework \ [version] folder.

For example: (C: \ WINDOWS \ Microsoft. NET \ Framework \ v4.0.30319)

Aspnet_regsql-C "data source =.; initial catalog = XLinShaWyDBase; user id = sa; password = 123"-ed-et-t "dbo. Contact"

The string following parameter-C is the connection string (replace it with the value you need ),

The string after parameter-t is the name of the data table.

After the production is successful, an additional table dbo. AspNet_SqlCacheTablesForChangeNotification will be added to the corresponding database.

Select * from dbo. AspNet_SqlCacheTablesForChangeNotification

　Note:

To enable SQL Server 7.0 or later to support the SqlCacheDependency feature, you must configure the database server.

There are two ways to configure SQLServer:

1. Use the aspnet_regsql command line tool,

Second, use the SqlCacheDependencyAdmin class.

For example:

Aspnet_regsql-S "server"-E-d "database"-ed or

Aspnet_regsql-S "server"-E-d "database"-et-t "table"
For SQL verification, replace-E with-U (User Name) and-P (password)

The command parameters of the tool are described as follows:

-? Displays the help function of the tool;

-S is followed by the database server name or IP address;

-U is followed by the login username of the database;

-P is followed by the database login password;

-E use the Windows integrated authentication of the current login user for authentication.

-D the following parameter indicates which database adopts the SqlCacheDependency function;

-C connection string to connect to the database. If you specify the server (-S) and login (-U and-P, or-E) information, this option is not required because the connection string already contains this information.

-T followed by the SqlCacheDependency function for which table the parameter is used;

-Ed allows SqlCacheDependency for databases;

-Dd prohibits the use of SqlCacheDependency for databases;

-Et allows SqlCacheDependency for data tables;

-Dt prohibits the use of SqlCacheDependency for data tables;

-Lt lists the tables in the current database that have used the sqlcachedependency function.

Step 3: Use the cache in the Code and set the SqlCacheDependency dependency for it:

Using System; using System. collections. generic; using System. linq; using System. web; using System. web. UI; using System. web. UI. webControls; using System. data. sqlClient; using System. data; public partial class Default4: System. web. UI. page {// <summary> /// obtain the Cache object Value of the specified CacheKey in the current application. /// </summary> /// <param name = "CacheKey"> Index key value </param> /// <returns> returns the cache object </returns> public static object GetCache (string Ca CheKey) {System. web. caching. cache objCache = HttpRuntime. cache; return objCache [CacheKey];} /// <summary> /// set the index key value to cache dependent data /// </summary> /// <param name = "CacheKey">/ param> /// <param name = "objObject"> cache object </param> /// <param name = "cacheDepen"> dependency object </param> public static void SetCache (string CacheKey, object objObject, System. web. caching. cacheDependency dep) {System. web. caching. cache objCa Che = HttpRuntime. cache; objCache. insert (CacheKey, objObject, dep, System. web. caching. cache. noAbsoluteExpiration, // never expires System. web. caching. cache. noSlidingExpiration, // disable the adjustable expiration System. web. caching. cacheItemPriority. default, null);} protected void Page_Load (object sender, EventArgs e) {string CacheKey = "cachetest"; object objModel = GetCache (CacheKey ); // obtain if (objModel = null) from the cache // No {objMo in the cache Del = GetData (); // cache the current time if (objModel! = Null) {// update the cache System based on the P_Product table changes in the database codematic. web. caching. sqlCacheDependency dep = new System. web. caching. sqlCacheDependency ("codematic", "dbo. contact "); SetCache (CacheKey, objModel, dep); // write cache} GridView1.DataSource = (DataSet) objModel; GridView1.DataBind ();} private DataSet GetData () {string conString = "data source = .; initial catalog = XLinShaWyDBase; user id = sa; password = 123 "; string strSQL =" SELECT * FROM dbo. contact "; SqlConnection myConnection = new SqlConnection (conString); DataSet ds = new DataSet (); myConnection. open (); SqlDataAdapter adapter = new SqlDataAdapter (strSQL, myConnection); adapter. fill (ds, "dbo. contact "); myConnection. close (); return ds ;}}

From the code above, we can see that the dependency object is basically the same as that of the file, except that the dependent object stored when the cache is stored in SetCache is different. SqlCacheDependency is used here.

Here, the SqlCacheDependency constructor is created:

Public SqlCacheDependency (string databaseEntryName, string tableName)

DatabaseEntryName: name of the database defined in the databases element of sqlCacheDependency In the caching section of the Web. config file.

TableName: name of the database table associated with SqlCacheDependency.

In this way, only when dbo. when the content of the Contact table changes, the query operation will re-query the data to update the cache content, which can greatly reduce repeated queries in the database and improve system performance and operational efficiency.