Many times, the performance bottleneck of our server will be when querying the database, so it is very important to cache the database, then there is no way to implement the SQL Server database cache, when the data table is not updated, read from the cache, when there is an update, only read from the data table, The answer is yes, so we can cache some commonly used basic data tables, such as news category of news system, do not need to read from the database every time, speed up the site's access speed.
So how do I turn on SQL Server database cache dependencies by doing the following:
The first step: Modify the configuration of the <system.web> section of the Web. config, with the following code to enable SqlCacheDependency for the site project. Note the connectionStringName in the following code, which is the name of the database connection string variable in the specified <connectionStrings> section. Name is the name of the SqlCacheDependency, and this name will be used in the third step. The SqlCacheDependency class automatically completes the reading of this configuration section information to establish a connection to the database.
Copy CodeThe code is as follows:
<system.web>
<add verb= "*" Path= "*.aspx"
Type= "Urlrewriter.rewriterfactoryhandler, Urlrewriter"/>
<add verb= "*" Path= "*.shtml"
Type= "Urlrewriter.rewriterfactoryhandler, Urlrewriter"/>
<add verb= "*" Path= "*.bobo"
Type= "Urlrewriter.rewriterfactoryhandler, Urlrewriter"/>
<!--> The following sets the database cache dependency mode-
<caching>
<sqlcachedependency enabled= "true" polltime= "6000" >
<databases>
<add name= "Yd_jwc_jake" connectionstringname= "Cachestr"/>
</databases>
</sqlCacheDependency>
</caching>
<!--
Set compilation debug= "True" to insert debug symbols
The page that was compiled. But as this will
Affect performance, so this value is only set during development
Set to True.
-
<compilation debug= "true" >
<assemblies>
<add assembly= "System.Design, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a"/>
</assemblies>
</compilation>
<!--
The <authentication> section allows you to configure ASP.
Secure authentication Mode,
To identify the incoming user.
-
<authentication mode= "Forms" >
<forms loginurl= "Login.aspx" name= ". Ajsupcxaiuth "></forms>
</authentication>
<authorization>
<allow users= "*"/>
</authorization>
<!--
If an unhandled error occurs during the execution of the request,
The corresponding processing steps can be configured through the <customErrors> section. Specifically
This section allows developers to configure
HTML error page to display
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 Two: Execute the following command in CMD to open the SQL Server database support for SqlCacheDependency, using 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 the database connection string, noting that the letter C is uppercase. Parameter-T followed by the data table where you want to open the database cache, where I turned on cache dependency for the News category table. (If you have more than one table, repeat this command, and be careful to modify your data table name)
Step three: In the business layer code that gets the data, if it is read for the first time, it is read from the database and stored in the cache. When data is obtained later, the database automatically determines whether the table has update data, and if so, the read database updates the cache at the same time, and if there is no update, it is 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 database if not in cache
{
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; Directly read cache in cache, no access to 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");//Get 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 ' >" + CDE SC + "</span></a></li>");
}
else if (t = = 2)
{
Sb. Append ("<li><a href="/file "+ CID +". shtml "href=" File "+ CID +". shtml "><span class= ' FontBold ' >" + cdes C + "</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);
}
The Jake.DataCache.GetCache () method in the above code is a generic method of getting and setting the cache itself, compiled separately into a DLL:
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>
Gets the cache value of the current application specified CacheKey
</summary>
<param name= "CacheKey" ></param>
<returns></returns>
public static Object GetCache (String CacheKey)
{
System.Web.Caching.Cache Objcache = Httpruntime.cache;
return Objcache[cachekey];
}
<summary>
Sets the cache value for the current application to specify CacheKey
</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>
Cache data by setting cache dependencies
</summary>
<param name= "CacheKey" > Key value </param>
<param name= "Objobject" > Cache objects </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,//Disabling adjustable expiration
System.Web.Caching.CacheItemPriority.Default,
Null
);
}
}
}
At this point, the cache dependency on the data table is turned on, which can greatly speed up site access.
Turn on SQL Server database cache dependency optimize site performance
