ASP. NET Database Cache dependency

ASP. NET Database Cache dependency

More often, our server performance loss is still in the query database, so the database cache is particularly important, the above several ways can be implemented in some data caching functions. But the problem is that our data is sometimes changing so that the data that the user might query during the cache is old data, which results in inconsistent data. That there is no way to do, if the data does not change, the user has been fetching data from the cache, once the data changes, the system can automatically update the data in the cache, so that users get a better user experience.  

The answer is YES! . NET has provided us with a very good workaround:sqlcachedependency Database Cache dependency.  

Implementation steps:

Let's look at how to implement the Database cache dependency feature:

First step: Modify the Web. configto enable the project to SqlCacheDependency .

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

<?XML version= "1.0"?><!--For more information about how to configure an ASP. NET application, go to http://go.microsoft.com/fwlink/?LinkId=169433 -<Configuration>    <system.web>        <Caching>            <SqlCacheDependencyenabled= "true"Polltime= "6000">                <databases>                    <Addname= "Codematic"connectionStringName= "Strcodematic" />                </databases>            </SqlCacheDependency>        </Caching>    </system.web>    <connectionStrings>        <Addname= "Strcodematic"connectionString= "server=."; Database=xlinshawydbase;uid=sa;pwd=123 "/>    </connectionStrings></Configuration>

The connectionstringname here Specifies a connection string that is added in <connectionStrings> . name is for the SqlCacheDependency , and this name will be used in step 3 .
The SqlCacheDependency class automatically completes the reading of this configuration section information to establish a connection to the database.

　　Note: in the <databases> section of <add name= "codematic" connectionstringname= "strcodematic"/> The value in the Name property must be System.Web.Caching.SqlCacheDependency ("codematic", "dbo") in the Page_Load code of the third step. Contact "); The first parameter in the (database name) is consistent.
Step two: Enable cache dependency for the database by executing the following command.

If you want to configure SqlCacheDependency, you need to execute it as a command line.

The aspnet_regsql.exe tool is located in the windows\\microsoft.net\\framework\\[ version ] folder.

such as:(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 after the parameter- C is the connection string (replace it with the value you want).

The string following the parameter- T is the name of the data table.

After the successful production, in the corresponding database, there will be one more table dbo. AspNet_SqlCacheTablesForChangeNotification

SELECT * FROM dbo. AspNet_SqlCacheTablesForChangeNotification

　Attention:

To enable SQL Server 7.0 or above to support the SqlCacheDependency feature, the database server needs to be configured to perform the relevant configuration.

There are two ways of configuring SQL Server:

One using the aspnet_regsql command-line tool,

Two uses the SqlCacheDependencyAdmin class.

For example:

aspnet_regsql-s "Server"-e-d "database" –ed or

Aspnet_regsql-s "Server"-e-d "database"-et-t "table"
If it is Sql authentication, you need to replace- e with,-u ( user name ),-p ( password )

The following is a description of the command parameters for the tool:

-? Display the help function of the tool;

- s followed by the parameter is the name of the database server or IP address;

- u after the parameter is the login user name of the database;

- p after the parameter is the database login password;

- e authenticates with Windows integrated authentication for the currently logged-on user.

- D followed by parameters for which database to use the sqlcachedependency function;

- c connection string to connect to the database. If you specify server (-s) and logon (-u and- p, or- e) information, this option is not required because the connection string already contains this information.

- T followed by parameters for which table to use the sqlcachedependency function;

-ed allows the use of SqlCacheDependency functions on the database;

-DD prohibit the use of sqlcachedependency function in database;

-et allows the use of SqlCacheDependency functions for data tables;

-DT prohibit the use of SqlCacheDependency function for data sheets;

-lt lists which tables in the current database are already using the sqlcachedependency feature.

Step three: Use the cache in your code and set SqlCacheDependency dependencies for it:

usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingsystem.web;usingSystem.Web.UI;usingSystem.Web.UI.WebControls;usingSystem.Data.SqlClient;usingSystem.Data; Public Partial classdefault4:system.web.ui.page{/// <summary>    ///Gets the cache object value for the current application specified CacheKey/// </summary>    /// <param name= "CacheKey" >index key value</param>    /// <returns>Returning cached Objects</returns>     Public Static ObjectGetCache (stringCacheKey) {System.Web.Caching.Cache Objcache=Httpruntime.cache; returnObjcache[cachekey]; }    /// <summary>    ///set caching data in a cache-dependent way/// </summary>    /// <param name= "CacheKey" >index key value</param>    /// <param name= "Objobject" >Cache Objects</param>    /// <param name= "Cachedepen" >Dependent Objects</param>     Public Static voidSetcache (stringCacheKey,ObjectObjobject, System.Web.Caching.CacheDependency DEP) {System.Web.Caching.Cache Objcache=Httpruntime.cache; Objcache.insert (CacheKey, Objobject, DEP, Sys Tem. Web.Caching.Cache.NoAbsoluteExpiration,//never ExpiresSystem.Web.Caching.Cache.NoSlidingExpiration,//Disable Adjustable expirationSystem.Web.Caching.CacheItemPriority.Default,NULL); }    protected voidPage_Load (Objectsender, EventArgs e) {        stringCacheKey ="cachetest"; ObjectObjmodel = GetCache (CacheKey);//getting from the cache        if(Objmodel = =NULL)//not in the cache.{Objmodel= GetData ();//cache the current time            if(Objmodel! =NULL)                    {                            //to update the cache by relying on p_product table changes in database CodematicSystem.Web.Caching.SqlCacheDependency dep =NewSystem.Web.Caching.SqlCacheDependency ("codematic","dbo. Contact"); Setcache (CacheKey, Objmodel, DEP);//Write Cache}} Gridview1.datasource=(DataSet) Objmodel;    Gridview1.databind (); }    PrivateDataSet GetData () {stringConstring ="data source=.; Initial Catalog=xlinshawydbase;user id=sa;password=123";stringstrSQL ="SELECT * FROM dbo. Contact"; SqlConnection myconnection=NewSqlConnection (constring); DataSet DS=NewDataSet (); Myconnection.open (); SqlDataAdapter Adapter=NewSqlDataAdapter (strSQL, MyConnection); Adapter. Fill (DS,"dbo. Contact");         Myconnection.close (); returnds; }}

As can be seen from the above code, and file dependencies are basically the same, only when the cache setcache stored in the different dependent objects. SqlCacheDependencyis used here.

Where to create the SqlCacheDependency method of construction:

Public SqlCacheDependency (String databaseentryname,string tableName)

databaseentryname : Is the sqlcachedependency databases in the caching section of the Web. config file the name of the database defined in the element.

tableName : The name of the database table associated with the SqlCacheDependency .

This way, only if dbo. When the contents of the Contact table change, the query operation will re-query the contents of the data Update cache, which can greatly reduce the duplicate query of the database and improve the performance and operational efficiency of the system.

ASP. NET Database Cache dependency