Use SQL cache dependencies, database cache dependencies (with instances), and SQL instances

Use SQL cache dependencies, database cache dependencies (with instances), and SQL instances

Use SQL to cache Dependencies

The simplest cache policy is to make the cache data expire after a specified time period. However, this simple method means that the cached data does not keep in touch with the underlying data source, resulting in long-time storage of expired data or quick expiration of the current data. A better way is to use the SqlCacheDependency class, so that data is cached until its underlying data is modified in the SQL database. This tutorial explains how to use this class.

Part 1

Introduction

Use ObjectDataSource to cache data and cache data discussed in the schema tutorial using a time-based validity period to clear data from the cache after a specified period. This method is the easiest way to balance cache performance and data expiration. With the x-second validity period selected, page developers can only enjoy the benefits of x-second caching, but can rest assured that the data expiration time cannot exceed x seconds at most. Of course, for static data, x can be extended to the lifecycle of a web application, as described in the cache data tutorial at application startup.

When caching database data, people often choose a time-based validity period because it is easy to use. But this is often not an appropriate solution. Ideally, the database data is cached until the underlying data is modified in the database. This method can maximize the performance benefits of caching, while minimizing the time required to maintain outdated data. However, to enjoy these benefits, you must establish a system that can detect changes in the underlying database data and clear corresponding entries from the cache. Before ASP. NET 2.0, page developers were responsible for implementing the system.

ASP. NET 2.0 provides the SqlCacheDependency class and necessary infrastructure, which can be used to detect changes when the database changes, thus clearing the corresponding cache entries. There are two technologies that can be used to perceive changes in the underlying data: Notification and polling. Next we will discuss the differences between notifications and polling. Afterwards, we will create the necessary infrastructure to support round-robin, and then explore how to use the SqlCacheDependency class through declaration and programming.

Learn about notifications and polling

There are two technologies that can be used to perceive changes in data in the database: Notification and polling. When using notifications, if the query result of a specific query has changed since its last execution, the database will automatically notify ASP. NET runtime. When poll is used, the database server saves the information about the most recent changes to a specific table. ASP. NET periodically queries databases during runtime to see which tables have been modified after being cached. The cache entries of the tables whose data has been modified will be cleared.

When using the notification technology, it requires less setup work than polling and has a finer granularity, because the technology tracks changes at the query level rather than at the table level. Unfortunately, notifications can only be used in the full version of Microsoft SQL Server 2005, that is, non-Express. All versions of Microsoft SQL Server from 7.0 to 2005 can adopt the round robin technology. Since these tutorials use SQL Server 2005 Express, we will focus on establishing and using polling. For more information about the notification function of SQL Server 2005, see the more reading material section at the end of this tutorial.

When using round robin, You need to configure a database to include a table named AspNet_SqlCacheTablesForChangeNotification. The table has three columns: tableName, icationicationcreated, and changeId. For tables that may need to use their data in the SQL cache dependencies of web applications, this table has a record corresponding to it. The tableName column specifies the table name, while icationicationcreated indicates the date and time when the record is added to the table. The changeId column is of the int type and its initial value is 0. The value of a table increases progressively every time it is modified.

In addition to the AspNet_SqlCacheTablesForChangeNotification table, the database also needs to include a trigger for each table that may appear in the SQL cache dependency. Each time you insert, update, or delete a record in the AspNet_SqlCacheTablesForChangeNotification table, or the changeId value in the table increments, these triggers are executed.

When using a SqlCacheDependency object to cache data, ASP. NET will track the current changeId of the corresponding table during runtime. The system periodically checks the database. Once the changeId value of a SqlCacheDependency object is found to be different from the corresponding value in the database, the object is cleared, because the changeId value is different, the corresponding table changes after the data is cached.

References (with examples ):

Tools used:

Aspnet_regsql.exe

Tool address: C: \ Windows \ Microsoft. NET \ Framework \ v2.0.50727

First, run the command: Dig to the directory where aspnet_regsql.exe is located: cd C: \ Windows \ Microsoft. NET \ Framework \ v2.0.50727.

Remote Server: 128.45.4.34

Database Name: RMA_DWH

Two cached tables: RMA_R_ARTICLE_LOCAL_ATTR and RMA_R_PURCHACE_ATTR

Aspnet_regsql.exe-S 128.45.4.34-U sa-P 23 WSXCDE # @-d RMA_DWH-ed
Aspnet_regsql.exe-S 128.45.4.34-U sa-P 23 WSXCDE # @-d RMA_DWH-t RMA_R_ARTICLE_LOCAL_ATTR-et
Aspnet_regsql.exe-S 128.45.4.34-U sa-P 23 WSXCDE # @-d RMA_DWH-t RMA_R_PURCHACE_ATTR-et

After the preceding script is executed, configure the web. config file in the Code as follows:

1: database connection string

<ConnectionStrings>
<Add name = "ConnectionString" connectionString = "Data Source = 128.45.4.34; Initial Catalog = RMA_DWH; Persist Security Info = True; User ID = sa; Password = 23 WSXCDE #@; max Pool Size = 51200; "providerName =" System. data. sqlClient "/>
</ConnectionStrings>

2: cache configuration items:

<Caching>
<SqlCacheDependency enabled = "true" pollTime = "1000">
<Databases>
<Add name = "RMA_DWH" connectionStringName = "ConnectionString"/>
</Databases>
</SqlCacheDependency>
</Caching>

3: Use of asp.net code:

Protected void Page_Load (object sender, EventArgs e) {string TS; DateTime time = DateTime. now; bool B = false; DataTable DT = new DataTable (); <span style = "color: # ff0000;"> DataTable DT_Article = (DataTable) Cache ["ARTICLE"]; </span> if (DT_Article! = Null) DT = DT_Article; else {Common. sqlBase SB = new Common. sqlBase (); DT = SB. query_Table ("select top 1000 * FROM dbo. RMA_V_ARTICLE "); <span style =" color: # ff6666; "> SqlCacheDependency productsTableDependency = new SqlCacheDependency (" RMA_DWH "," RMA_R_ARTICLE_LOCAL_ATTR "); Cache. insert ("ARTICLE", DT, productsTableDependency, Cache. noAbsoluteExpiration, Cache. noSlidingExpiration); </span> B = true;} TS = (DateTime. Now-time). TotalSeconds. ToString (); if (! B) // use the cache {labState. text = "use cache" + TS + "s" + DateTime. now;} else {labState. text = "cache not used" + TS + "s" + DateTime. now;} ProductsDeclarative. dataSource = DT; ProductsDeclarative. dataBind ();}

In addition, if you want to use the cache on a non-web layer, you can use HttpRuntime. Cache. Multiple table associations can be used:

AggregateCacheDependency

Some code is as follows:

Model.User userModel = new Com.AdiRMA.Model.User();            string key = string.Format("{0}_{1}", "GetUserByUserID", user_id);            if ((Model.User)<span style="color:#ff0000;">HttpRuntime.Cache</span>[key] != null)            {                userModel = (Model.User)<span style="color:#ff0000;">HttpRuntime.Cache</span>[key];            }            else            {                                <span style="color:#ff0000;">AggregateCacheDependency Dependency = new AggregateCacheDependency();                SqlCacheDependency[] dependencies = {                                                                        new SqlCacheDependency("RMA_DWH", "RMA_S_USER"),                                                                        new SqlCacheDependency("RMA_DWH", "RMA_S_DEPART"),                                                                        new SqlCacheDependency("RMA_DWH", "RMA_S_USER_DEPART")                            };                Dependency.Add(dependencies);                HttpRuntime.Cache.Insert(key, userModel, Dependency, Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration);</span>            }

ASP. NET cache dependency mechanism-SQL cache dependency
Http://blog.itpub.net/12639172/viewspace-618184/
Use SQL to cache Dependencies
Http://msdn.microsoft.com/zh-cn/dd263032
Http://msdn.microsoft.com/zh-cn/dd263037


Aspnet_regsql.exe-S server-U user-P password-d database-ed
Aspnet_regsql.exe-S 128.45.4.34-U sa-P 23 WSXCDE # @-d RMA_DWH-ed




Aspnet_regsql.exe-S <I> server </I>-U <I> user </I>-P <I> password </I>-d <I> database </I >-t <I> tableName </I>-et
Aspnet_regsql.exe-S 128.45.4.34-U sa-P 23 WSXCDE # @-d RMA_DWH-t RMA_R_ARTICLE_LOCAL_ATTR-et
Aspnet_regsql.exe-S 128.45.4.34-U sa-P 23 WSXCDE # @-d RMA_DWH-t RMA_R_PURCHACE_ATTR-et

SqlDependency and SqlCacheDependency Cache Usage and specific steps
Http://www.webkaka.com/tutorial/asp.net/2012/111912/


How to call the Global. asax file in ASP. NET
Http://www.veryhuo.com/a/view/9402.html

How can I implement cache dependency in SQL Server 2005? Who knows how to teach?

[Hosting service] using SQL Server 2005 to implement database cache dependencies and the steps are as follows:
Step 1
Check whether ServiceBroker is enabled. Check Method:
SelectDATABASEpRoPERTYEX ("Database Name", "IsBrokerEnabled") -- 1 indicates that 0 has been enabled and 0 has not been enabled
Step 2
If ServiceBroker is not enabled, use the following statement:

For more information, see my workspace ~ Hope to help you
Reference: hi.baidu.com/...9.html

You cannot connect to the SQL database for registration of cache dependencies. You cannot log on to the database where you log on.

I solve it like this:
Check the database-security-login name to see if there is a "pet" user name. If there is no such user name, create it. The roles are public and sysadmin. Select the table to be processed. Of course, the "mixed mode" still needs to be adjusted. Then try pet to log on to the database. If you can, create "database cache dependencies"