Enable database cache dependency to optimize website performance

In many cases, the performance bottleneck of our SERVER is when querying the database, so it is very important for the database cache. Is there a way to implement the SQL SERVER database cache, when the data table is not updated, it is read from the cache. When there is an update, it is read from the data table. The answer is yes, in this way, we can cache some common basic data tables, such as the news category of the news system. Each time we do not need to read data from the database, we can speed up Website access.
To enable the SQL Server database cache dependency, use the following method:
Step 1: modify the configuration in the <system. Web> section of web. Config. The Code is as follows to enable SqlCacheDependency for the website project. Note that the connectionStringName in the following code is the name of the database connection string variable in the specified <connectionStrings> section. Name indicates the name of the SqlCacheDependency, which will be used in step 3. The SqlCacheDependency class automatically reads the configuration section information to establish a connection with the database.
Copy codeThe Code is as follows:
<System. web>
<HttpHandlers>
<Add verb = "*" path = "*. aspx"
Type = "URLRewriter. RewriterFactoryHandler, URLRewriter"/>
<Add verb = "*" path = "*. shtml"
Type = "URLRewriter. RewriterFactoryHandler, URLRewriter"/>
<Add verb = "*" path = "*. bobo"
Type = "URLRewriter. RewriterFactoryHandler, URLRewriter"/>
</HttpHandlers>
<! --> Set the database cache dependency method as follows -->
<Caching>
<SqlCacheDependency enabled = "true" pollTime = "6000">
<Databases>
<Add name = "YD_JWC_JAKE" connectionStringName = "cachestr"/>
</Databases>
</SqlCacheDependency>
</Caching>
<! --
Set compilation debug = "true" to insert the debugging symbol
Compiled pages. However, this
Performance is affected, so this value is only available during development.
Set to true.
-->
<Compilation debug = "true">
<Assemblies>
<Add assembly = "System. Design, Version = 2.0.0.0, Culture = neutral, PublicKeyToken = B03F5F7F11D50A3A"/>
</Assemblies>
</Compilation>
<! --
In the <authentication> section, you can configure
Security Authentication mode,
To identify the user.
-->
<Authentication mode = "Forms">
<Forms loginUrl = "login. aspx" name = ". AJSUPCXAIUTH"> </forms>
</Authentication>
<Authorization>
<Allow users = "*"/>
</Authorization>
<! --
If an unprocessed error occurs during request execution,
You can configure the corresponding processing steps in the <mermerrors> section. Specifically,
This section allows developers to configure
Html error page to be displayed
To replace the error stack trace. -->
<CustomErrors mode = "RemoteOnly" defaultRedirect = "/ER3.shtml">
<Error statusCode = "403" redirect = "/ER1.shtml"/>
<Error statusCode = "404" redirect = "/ER404.shtml"/>
</CustomErrors>
</System. web>

Step 2: run the following command in CMD to enable support for sqlcachedependencyin in the SQL Server database. Use the aspnet_regsql.exe tool, which is located in the windows \ microsoft.net \ framework \ [version] folder.
The Code is as follows:
Copy codeThe Code is as follows:
Aspnet_regsql-C "data source = 127.0.0.1; initial catalog = YD_JWC_JAKE; user id = sa; password ="-ed-et-t "T_NewsClass"

The parameter-C is followed by a database connection string. Note that the letter C is in uppercase. The parameter-t is followed by the data table that you want to enable the database cache. Here, I have enabled the cache dependency for news‑type tables. (If there are multiple tables, execute this command again. Be sure to modify your data table name)
Step 3: In the business-Layer Code that obtains data, if it is the first time to read data, it will be read from the database and saved to the cache. When you obtain data in the future, the database automatically determines whether the table has updated data. If yes, the database will be read to update the cache at the same time. If no update is made, the database will be read from the database. The Code is as follows:

Copy codeThe Code is as follows:
Private void getInfoClass (int t)
{
String CacheKey = "cacheclass" + t. ToString ();
Object objModle = Jake. DataCache. GetCache (CacheKey); // get from Cache
DataTable dt = null;
If (objModle = null) // read the database if the cache does not exist
{
Jake. BLL. NewsManage. NewsClass nc = new Jake. BLL. NewsManage. NewsClass ();
Dt = nc. GetList (""). Tables [0];
ObjModle = dt;
If (objModle! = Null)
{
System. Web. Caching. SqlCacheDependency dep = new System. Web. Caching. SqlCacheDependency ("YD_JWC_JAKE", "T_NewsClass ");
Jake. DataCache. SetCache (CacheKey, objModle, dep );
}
}
Else
{
Dt = (DataTable) objModle; // you can directly read the cache if it is in the cache. You do not need to access the database.
}
DataRow [] drs = dt. Select ("", "classid ");
StringBuilder sb = new StringBuilder ();
Sb. Append ("<ul> ");
Foreach (DataRow r in drs)
{
String cid = r ["ClassId"]. ToString ();
Security js = new Security ();
String decrystr = Jake. Common. ConfigHelper. GetConfigString ("DecryStr"); // obtain the encryption key
Cid = js. EncryptQueryString (cid, decrystr );
String cdesc = r ["ClassDesc"]. ToString ();
If (t = 1)
{
Sb. append ("<li> <a href ="/Info "+ cid + ". shtml "href =" Info "+ cid + ". shtml "> <span class = 'fontbold '>" + cdesc + "</span> </a> </li> ");
}
Else if (t = 2)
{
Sb. append ("<li> <a href ="/File "+ cid + ". shtml "href =" File "+ cid + ". shtml "> <span class = 'fontbold '>" + cdesc + "</span> </a> </li> ");
}
Else
Sb. append ("<li> <a href ="/FAQ "+ cid + ". shtml "href =" FAQ "+ cid + ". shtml "> <span class = 'fontbold '>" + cdesc + "</span> </a> </li> ");
}
Sb. Append ("</ul> ");
Response. Write (sb );
}

In the above Code, the method for obtaining and setting the cache is a self-defined general method, which is compiled into a DLL separately:
The Code is as follows:
Copy codeThe Code is as follows:
Using System;
Using System. Collections. Generic;
Using System. Web;
Using System. Text;
Namespace Jake
{
Public class DataCache
{
/// <Summary>
/// Obtain the Cache value of the specified CacheKey of the current application
/// </Summary>
/// <Param name = "CacheKey"> </param>
/// <Returns> </returns>
Public static object GetCache (string CacheKey)
{
System. Web. Caching. Cache objCache = HttpRuntime. Cache;
Return objCache [CacheKey];
}
/// <Summary>
/// Set the Cache value of the specified CacheKey for the current application
/// </Summary>
/// <Param name = "CacheKey"> </param>
/// <Param name = "objObject"> </param>
Public static void SetCache (string CacheKey, object objObject)
{
System. Web. Caching. Cache objCache = HttpRuntime. Cache;
ObjCache. Insert (CacheKey, objObject );
}
/// <Summary>
/// Set the cached dependency to cache data
/// </Summary>
/// <Param name = "CacheKey"> key value </param>
/// <Param name = "objObject"> cache object </param>
/// <Param name = "dep"> cache dependencies </param>
Public static void SetCache (string CacheKey, object objObject, System. Web. Caching. CacheDependency dep)
{
System. Web. Caching. Cache objCache = HttpRuntime. Cache;
ObjCache. Insert (
CacheKey,
ObjObject,
Dep,
System. Web. Caching. Cache. NoAbsoluteExpiration, // never expires
System. Web. Caching. Cache. NoSlidingExpiration, // disable adjustable expiration
System. Web. Caching. CacheItemPriority. Default,
Null
);
}
}
}

So far, the cache dependency on the data table has been enabled, which can greatly speed up Website access.
(Reprinted please indicate the source of this article: http://blog.csdn.net/j_jake)