Database cache dependency with SQL cache dependency (with instance)

Using SQL cache dependencies

The simplest caching strategy is to make the cached data expire after a specified period of time. But this simple approach means that the cached data is not kept in contact with the underlying data source, resulting in long-overdue data saving or the current data expiring soon. A better approach is to use the SqlCacheDependency class so that the data is cached until its underlying data is modified in the SQL database. This tutorial will explain how to use this class.

Part 1

Brief introduction

The caching techniques explored using the ObjectDataSource cache data and caching data in schemas tutorial use time-based validity periods to purge data from the cache after a specified period. This approach is the simplest way to balance cache performance against data obsolescence. Choose an X-second validity period, while the page developer can only enjoy the benefits of the X-second cache, but rest assured that the data will expire no more than x seconds. Of course, for static data, x can be extended to the life cycle of the Web application, as described in the caching data tutorial at application startup.

When caching database data, people often choose time-based expiration because it is easy to use. But often this is not a suitable solution. Ideally, the database data is cached until the underlying data is modified in the database, and the cache is cleared at this time. This approach maximizes the performance benefits of caching, while keeping stale data to a minimum. However, to benefit from these benefits, a system must be established that can perceive that the underlying database data has changed and that the corresponding entries are purged from the cache. Before ASP. NET 2.0, the page developer was responsible for implementing the system.

ASP. NET 2.0 provides the SqlCacheDependency class and the necessary infrastructure that can be used to clear the corresponding cache entry by sensing changes as the database changes. There are two techniques that can be used to perceive changes in the underlying data: notifications and polling. We'll discuss the differences between notifications and polling, and then we'll create the necessary infrastructure to support polling and then explore how to use the SqlCacheDependency class in both declarative and programmatic ways.

Understanding Notifications and polling

There are two techniques that can be used to perceive changes in data in a database: Notifications and polling. When using notifications, for a specific query, the database automatically notifies the ASP. NET runtime if its query results have changed since its last execution. When polling is used, the database server saves information when a particular table has recently changed. The ASP. NET runtime periodically queries the database to see which tables have changed after the cache has been cached. The associated cache entries for those tables whose data has been altered are purged.

When you use notification technology, you need to build less than a poll and have finer granularity because the technology tracks changes at the query level rather than at the table level. Unfortunately, notifications are only available in the full version of Microsoft SQL Server 2005, which is non-Express (non-express). For all versions of Microsoft SQL Server from 7.0 to 2005, you can use polling technology. Since these tutorials are using SQL Server 2005 Express, we will focus on establishing and using polling. For more information about the notification features of SQL Server 2005, refer to the more Reading Materials section at the end of this tutorial.

When polled, you configure the database to include a table named AspNet_SqlCacheTablesForChangeNotification, which has three columns – TableName, notificationcreated and Changeid. The table has a record for those tables that might need data in the SQL cache dependency of the Web application. The TableName column specifies the table name, and notificationcreated indicates the date and time when the record was added to the table. The Changeid column is of type int with an initial value of 0. Each time the corresponding table is modified, its value is incremented.

In addition to the AspNet_SqlCacheTablesForChangeNotification table, the database needs to contain a trigger for each table that may appear in the SQL cache dependency. These triggers are executed whenever a record is inserted, updated, deleted in the AspNet_SqlCacheTablesForChangeNotification table, or when the Changeid value in the table is incremented.

When you use a SqlCacheDependency object to cache data, the ASP. NET runtime tracks the current Changeid of the corresponding table. The system periodically checks the database, and once the Changeid value of a SqlCacheDependency object is found to be different from the corresponding value in the database, the object is purged because the difference in Changeid value means that the corresponding table changes after the data is cached.

References (with examples):

Using tools:

Aspnet_regsql.exe

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

First in the command tool through the command: CMD into the directory where Aspnet_regsql.exe:CD C:\Windows\Microsoft.NET\Framework\v2.0.50727

Remote server: 128.45.4.34

Database name: RMA_DWH

Cache-dependent Two table names: Rma_r_article_local_attr, rma_r_purchace_attr

Aspnet_regsql.exe-s 128.45.4.34-u sa-p 23wsxcde#@-D rma_dwh-ed
Aspnet_regsql.exe-s 128.45.4.34-u sa-p 23wsxcde#@-D rma_dwh-t Rma_r_article_local_attr-et
Aspnet_regsql.exe-s 128.45.4.34-u sa-p 23wsxcde#@-D rma_dwh-t Rma_r_purchace_attr-et

After the above script executes, configure the Web. config file in your code as follows:

1: Connection string for database

<connectionStrings>
<add name= "ConnectionString" connectionstring= "Data source=128.45.4.34;initial catalog=rma_dwh; Persist Security info=true; User Id=sa; password=23wsxcde#@; Max Pool Size = 51200; "providername=" System.Data.SqlClient "/>
</connectionStrings>

2: Cached Configuration items:

<caching>
<sqlcachedependency enabled= "true" Polltime= ">"
<databases>
<add name= "RMA_DWH" connectionstringname= "ConnectionString"/>
</databases>
</sqlCacheDependency>
</caching>

Use of the 3: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" 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 cache {Labstate.text = "Use cache" + TS + "s" + DateTime.Now;            }else {labstate.text = "cache not used" + TS + "s" + DateTime.Now;            } Productsdeclarative.datasource = DT;                    Productsdeclarative.databind (); }

Asp. NET cache dependency mechanism-sql cache dependency
http://blog.itpub.net/12639172/viewspace-618184/
Using SQL 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 23wsxcde#@-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 23wsxcde#@-D rma_dwh-t Rma_r_article_local_attr-et
Aspnet_regsql.exe-s 128.45.4.34-u sa-p 23wsxcde#@-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.
Http://www.veryhuo.com/a/view/9402.html

Using SQL cache dependencies, Database cache dependency (with instance)