ASP. NET database cache dependency, asp.net database cache

ASP. NET database cache dependency, asp.net database cache

By Peter A. Bromberg, Ph. D.

In ASP. NET, the coolest feature of the Cache class is that it can control its own behavior based on various dependencies. File-based dependency is the most useful. File dependencies are added by Using Cache. Insert and providing the CacheDependency object of the referenced file.

 

Cache. Insert ("MyData", Source, new CacheDependency (Server. MapPath ("authors. xml ")));

 

But what should we do when we want the cache to expire and recreate the cache based on the changes in the database table? This situation exists in many applications. Asp.net does not provide internal direct cache support for monitoring database table changes. This can be achieved by using sp_makewebtask, an uncommon system stored procedure of SQL Server. This stored procedure was originally used to generate Web pages from queries, but we only need to make a slight modification-use it in the trigger, we can get a reasonable and effective way, when the record of a table in the database is updated, modify a specific file when you delete or modify it. This will cause the file monitoring process in the CacheDependency instance to detect file changes and invalidate the cache. In fact, because the CacheDependency class works on the UNC file protocol, we can deploy this solution on the entire Web Farm, copies of applications on each machine on the Web Farm use the UNC file path to monitor the same file on a single machine in the WebFarm.

To put it bluntly, let's create a simple web application to demonstrate how it works. First, we will use the Northwind sample database that we trust in SQL Server. Create a simple DataGrid to display records in the Employees table. The first thing we need to do is create a trigger.

Create trigger WriteCacheDepFile ON [dbo]. [Employees]

For insert, UPDATE, DELETE

AS

EXEC sp_makewebtask '\ peter \ C $ \ Cache \ mycache.txt', 'select top 1 FirstName FROM ployees'

 

The above stored procedure is simple to tell SQL server, if the employee table changes, then a simple query to update the mycache.txt file, there is a simple query statement is actually enough, as long as it is a valid T-SQL statement, SQL Server will be happy to update that file.

Next, we need to create a directory and set it to share. You may want to update the access permission of the file so that it can be written. Note that the Administrator Shares "C $" here ". in addition, you need to create an empty text file named "mycache.txt ".

Okay. Now we can create our application. First, enter the name of the dependency file in the web. config file, so that you do not need to redeploy the application when modifying the dependency file.

Add the appSettings configuration section to the root of the web. config file:

</System. web>

<Deleetask>

<! -Cache dependent file path -->

<Add key = "dependencyFile" value = "\ peter \ Cache \ mycache.txt"/>

</AppSettings>

</Configuration>

 

Now, let's create a cache mechanism in the Global class so that we don't need to write specific code on any page.

[C #]

Public class Global: System. Web. HttpApplication

{

Cache _ cache = null;

Public static bool blnReflash = false;

Public const string ConnStr = "server = localhost; database = Northwind; uid = sa; pwd = ";

Public const string strSQL = "SELECT EmployeeID, lastname, firstname FROM Employees ";

 

Protected void Application_Start (Object sender, EventArgs e)

{

_ Cache = Context. Cache;

RefreshCahe (null, null, 0 );

}

 

Protected void Session_Start (Object sender, EventArgs e)

{

If (HttpContext. Current. Cache ["Employees"] = null)

RefreshCache (null, null, 0 );

}

 

Static void RefreshCache (string key, object item, CacheItemRemoveReason reason)

{

SqlDataAdapter adapter = new SqlDataAdapter (strSQL, ConnStr );

DataSet ds = new DataSet ();

Adapter. Fill (ds, "Employees ");

CacheItemRemovedCallback onRemove = new CacheItemRemovedCallback (RefreshCache );

}

In ASPNET, why cannot I use database cache dependencies after using Linq?

Linq to SQL does not seem to support cache dependencies. Use Traditional ADO to implement SqlCacheDependency.
 
. Net data cache dependency and File Cache dependency

Virtual Machine ...... Give up.
After self-implementation, the data to be cached is lost to the memory. Then write a class to control the reading and writing of this piece of data. Read data directly from the memory and write data to the database. If the write is successful, modify the memory data. Pay attention to locking data operations.