Dr. Dobb's | SQL Cache Dependencies

SQL Cache Dependencies

Since the advent of the ASP.NET Cache object and cache dependencies, developers have dreamt of an easy way to refresh stored result sets based on database changes. In ASP.NET 2.0, the dream came true.

July 26, 2006
URL:http://www.drdobbs.com/windows/sql-cache-dependencies/191200217

Most ASP.NET applications store some data to the cache in order to generate pages more quickly and effectively. The problem with cached data is that users may get reports and pages filled with out-of-date information. There might be situations in which users are not looking for fresh and late-breaking records—for example, users getting daily or weekly sales reports. At the same time, you might find useful to cache the list of customers and employees for all pages working with that information. What if, at some point, the customer record is updated? If updates occur at a known or predictable rate, you can set an appropriate duration for the cached data so that pages get automatically refreshed. But if updates occur occasionally?

Since the advent of the ASP.NET Cache object and cache dependencies, developers dreamt of an easy way to refresh stored result sets based on database changes. In ASP.NET 2.0, the dream came true, and now database dependencies are at last a reality.

A database dependency is a special case of a custom dependency that consists of the automatic invalidation of cached data when the contents of a given database table changes. It is essential to note that the cached data doesn't necessarily have to be a result set. Any cache item—regardless of its contents—can be made dependent on changes to a database table. As soon as the table undergoes some changes, the cached item is emptied. In most cases, though, you just place in the cache item the results of a query run on that table.

ASP.NET 2.0 supplies an ad hoc class—SqlCacheDependency—that inherits CacheDependency and supports dependencies on SQL Server tables. More precisely, the class is compatible with MSDE, SQL Server 7.0, and subsequent SQL Server versions including SQL Server 2005. The following code shows how to add data to the cache bound to a SQL Server 7.0 or SQL Server 2000 database table.


string
database = "Northwind";

string
table = "Customers";

SqlCacheDependency
dep = new SqlCacheDependency(database, table);

Cache.Insert("MyData",
data, dep);

Note that while Customers indicates a real table name in the database, Northwind is not meant to be the name of a physical database. How could it be without connection information? More precisely, the string Northwind in the example indicates the name of an entry in the web.config file that points to a physical database and related connection string. When the database is SQL Server 7.0 or 2000, the "database" parameter points to an entry under the <databases> section in the web.config:


<system.web>
  <caching>
   <sqlCacheDependency enabled="true" pollTime="1000" >
     <databases>
       <add name="Northwind" connectionStringName="LocalNWind"/>   
     </databases>
   </sqlCacheDependency>
 </caching>
</system.web>

The connectionStringName attribute, in turn, points to an entry under the <connectionStrings> section where predefined connection strings are listed. In addition to these application settings, the physical database must be extended with additional capabilities. In particular, it needs to have a few triggers and helper tables added. You run the following command to enable notifications on the Northwind database for the local installation of SQL Server:


aspnet_regsql.exe -S (local) -U YourUserName -P YourPassword 
                  -d Northwind -ed

Furthermore, you run the following command to enable notification on the Customers table:


aspnet_regsql.exe -S (local) -U YourUserName -P YourPassword 
                  -d Northwind "et "t Customers

To enable SQL cache dependencies on SQL Server 2005, you need minimal changes at the application level and, nicely enough, zero work on the administration side. There's no need to write anything in the web.config file nor to create triggers and helper tables on the target database. All that you have to do is using a different constructor for the SqlCacheDependency class, as below:


SqlCacheDependency
dep = new SqlCacheDependency(cmd);

Cache.Insert("MyData",
data, dep);

You pass the constructor a reference to the command object whose result set is used to detect changes. When the database changes so that the results of the specified command are modified, the data stored in the cache is removed.

In SQL Server 2005, you need no extra configuration because the database engine supports change notifications natively.