Channels ▼


SQL Cache Dependencies

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.

database = "Northwind";

table = "Customers";

dep = new SqlCacheDependency(database, table);

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:

   <sqlCacheDependency enabled="true" pollTime="1000" >
       <add name="Northwind" connectionStringName="LocalNWind"/>   

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:

dep = new SqlCacheDependency(cmd);

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.

Related Reading

More Insights

Currently we allow the following HTML tags in comments:

Single tags

These tags can be used alone and don't need an ending tag.

<br> Defines a single line break

<hr> Defines a horizontal line

Matching tags

These require an ending tag - e.g. <i>italic text</i>

<a> Defines an anchor

<b> Defines bold text

<big> Defines big text

<blockquote> Defines a long quotation

<caption> Defines a table caption

<cite> Defines a citation

<code> Defines computer code text

<em> Defines emphasized text

<fieldset> Defines a border around elements in a form

<h1> This is heading 1

<h2> This is heading 2

<h3> This is heading 3

<h4> This is heading 4

<h5> This is heading 5

<h6> This is heading 6

<i> Defines italic text

<p> Defines a paragraph

<pre> Defines preformatted text

<q> Defines a short quotation

<samp> Defines sample computer code text

<small> Defines small text

<span> Defines a section in a document

<s> Defines strikethrough text

<strike> Defines strikethrough text

<strong> Defines strong text

<sub> Defines subscripted text

<sup> Defines superscripted text

<u> Defines underlined text

Dr. Dobb's encourages readers to engage in spirited, healthy debate, including taking us to task. However, Dr. Dobb's moderates all comments posted to our site, and reserves the right to modify or remove any content that it determines to be derogatory, offensive, inflammatory, vulgar, irrelevant/off-topic, racist or obvious marketing or spam. Dr. Dobb's further reserves the right to disable the profile of any commenter participating in said activities.

Disqus Tips To upload an avatar photo, first complete your Disqus profile. | View the list of supported HTML tags you can use to style comments. | Please read our commenting policy.