Asp.net| Cache | data | Database asp.net database cache dependencies
by Peter A. Bromberg, Ph.D.
In ASP.net, the coolest feature of the cache class is its ability to control its behavior according to various dependencies. file-based dependencies are most useful, and file dependencies are added by CacheDependency objects that use Cache.Insert and provide reference files.
Cache.Insert ("MyData", Source, New CacheDependency (Server.MapPath ("Authors.xml"));
But what to do when we want the cache to fail and rebuild the cache based on the changes in the tables in the database--a scenario that exists in many applications. ASP.net does not provide intrinsic direct caching support for monitoring database table changes. This is done by using the infrequently used system stored procedures of SQL Server, a stored procedure used to generate Web pages from a query, but we just have to make a little change-using it in triggers, and we can get a reasonable and effective way to sp_makewebtask. Modifying a particular file when a record of a table in a database is updated, deleted, or modified will cause the file monitoring process in the CacheDependency instance to detect changes in the file, thereby invalidating the cache. In fact, because the CacheDependency class works on UNC file protocols, we can deploy this solution across the Web farm, Web The copy of the application on each machine on the farm will monitor the same file on a single machine in webfarm via a UNC file path
With less nonsense, let's create a simple Web application to demonstrate if it works. First, we'll use the Northwind sample database that we all trust in SQL Server. Create a simple DataGrid to display the records in the Employees table. The first thing we need to do is create triggers.
CREATE TRIGGER writecachedepfile on [dbo]. [Employees]
For INSERT, UPDATE, DELETE
As
EXEC sp_makewebtask ' \\peter\C$\Cache\mycache.txt ', ' SELECT top 1 FirstName from Employees '
The above stored procedure is simply to tell SQL Server, if any changes in the employee table, based on 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 The server will be happy to update the file.
Next, we need to create a directory and set it to be shared. You may want to update the file's access rights so that it can be written, notice that I am using the admin share "C $" here. In addition, you need to create an empty text file, "Mycache.txt".
OK, now it's time to create our application. First, enter the dependent file name in the Web.config file so that we do not need to redeploy the application when we modify the dependent file.
At the root of the Web.config file, add the appsettings configuration section:
</system.web>
<appSettings>
<!-Cache-dependent file path-->
<add key= "Dependencyfile" value= "\\peter\Cache\mycache.txt"/>
</appSettings>
</configuration>
Now let's create a caching 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);
String depfile= configurationsettings.appsettings["Dependencyfile"]. ToString ();
HttpContext.Current.Cache.Insert ("Employees", Ds,new cachedependency (Depfile),
Cache.noabsoluteexpiration,cache.noslidingexpiration,
Cacheitempriority.high,onremove);
Blnreflash = true;
}
}
As seen above, we define a cache-type _cache object, in the Application_Start method, we assign the current cache instance to it, and then invoke the RefreshCache method to populate the object. RefreshCache is actually a static delegate callback method that simply obtains a dataset from the Empoyees table and then creates a CacheItemRemovedCallback type of delegate onremove, Make it point to the RefreshCache method so that when the monitored file changes, that is, the cache fails, the delegate is invoked and the data in the cache is refreshed.
Finally, we insert the dataset together with the OnRemove delegate into the cache, and in Session_Start, in order to "insure," I added another judgment to invoke the RefreshCache method to populate the cache.
Here, our application is created so that a cached dataset can be accessed on any page. In webform1aspx, I demonstrated how to use it.
[C #]
private void Page_Load (object sender, System.EventArgs e)
{
Guarantee that the cache is not empty, and if it is empty, populate it
if (cache["Employees"] = = null)
Global.refreshcache (null,null, 0);
Cachestatus.text = "Cache refreshed at" + DateTime.Now.ToLongTimeString ();
Else
Cachestatus.text = "DataSet from Cache";
DataSet ds = (DataSet) cache["Employees"];
datagrid1.datasource= ds. Tables[0];
Datagrid1.databind ();
}
Now, if you request this page, it will successfully display the dataset from cache each time, if you keep the browser open, open SQL Server Query Analyzer, select Northwind database, execute SQL statement ' Update Employees Set Lastname = ' Davovlieu ' where EmployeeID = 1 ', update the records in the table, and then request the page again, you will see that the cache has expired and refreshed.
Translator: About database based cache dependencies, GotDotNet also has a dataset implementation (Rob Howard implementation) ASP.net cache invalidation on db change,
At present there is no very natural solution in the asp.net1.1 version, and it is gratifying to note that the asp.net2.0, as well as Yuku, released with Whidbey, provide a good implementation from the data layer. Let's wait and see!
