Published: 04 Jun 2007
By: Luke Stratman

Luke Stratman shows how and why to use the SqlCacheDependency class.

It's hard for me to pin down a favorite feature in the 2.0 version of the .NET framework. Is it generics? Or maybe built-in configuration file encryption? How about the updated configuration classes? It's a tough choice. But there's no doubt about my favorite example of what makes .NET 2.0 such a compelling development platform: the SqlCacheDependency class. It does a brilliant job of illustrating just how well the various Microsoft products work together to produce some extremely powerful applications.


Caching is an important part of any enterprise-level web application. It allows you to avoid unnecessary trips to your backing store, which can be a directory on the filesystem or a database, in order to retrieve relatively static information. So, if your application finds that it doesn't have a piece of data that it needs, it makes a call to this backing store and retrieves it. After doing so, however, it stores this data in memory on the web server so that the next time that someone wants to view that data, the application doesn't have to bother the backing store. All of this is accomplished through the use of the System.Web.Caching.Cache class: every running web application has its own instance of this class that's accessible by referencing HttpContext.Current.Cache.

It's a fairly simple class with some powerful features if you choose to dig down into the guts of it: at its core, however, are the standard Get(), Add(), Insert(), and indexer methods that allow you to get or set object instances in the cache based on a string key. Items in the cache are, obviously, available for any page request made to the application and will remain in the cache until they are explicitly removed or until ASP.NET needs to free up space by scavenging cache items. You can hook in to various events to control which item is chosen when scavenging occurs, but that's beyond the scope of this article. Check out the MSDN documentation for more details.

A key feature relevant to our discussion here is the ability to add dependencies to cache entries: that is, to add a condition that, when changed, invalidates the cache entry. These dependencies can take two forms in the 2.0 version of the framework: watching a file or collection of files for changes or watching for changes to a result set from a database. The former is accomplished by using the System.Web.Caching.CacheDependency class and the latter, the focus of this article, is accomplished by using the System.Web.Caching.SqlCacheDependency class.

But why are these dependencies so important? Well, obviously, you don't want to serve stale data from your cache. In a single web server environment, this is a fairly easy hurdle to overcome: whenever your web application receives a request from the user to update an object, you can save the updated version of the object to the cache and overwrite any data that would now be stale. However, when you move into a clustered web server setup, you really need these cache dependency classes. Say that you have two web servers: one gets a view request for the object, so it calls the database, instantiates the object, and stores it in the cache. However, when the user decides to update this object, their request could get routed to the second web server and when it updates the database with the object's new information, the cached copy on the first server would be out of date but it would have no way of knowing that. Thus was born the need for a centralized notification methodology for database changes, which took the form of the SqlCacheDependency class.

Using SqlCacheDependency

The ability to watch for changes in a database and invalidate cache entries is available for .NET 2.0 and either Microsoft SQL Server 7.0, 2000, or 2005. While the dependency functionality is technically there for 7.0 and 2000, it's an extreme kludge to say the least. It involves installing a set of triggers on tables that you wish to watch for changes and then having ASP.NET launch a thread that periodically polls a predetermined table to check to see if any of the watched tables have changed. So, you're locked into watching for changes at a table, rather than a row, level. As you will see in a moment, the implementation of this functionality is much more elegant when using SQL Server 2005, so I urge you to upgrade to the latest and greatest if you plan on making use of the SqlCacheDependency class.

To drive dependency notifications, SQL Server 2005 leverages two new features: indexed views and notification services. Indexed views allow the DBMS to pre-compute the results of a query defining a view and also to watch the underlying database for operations that would change the contents of the view. Because the underlying tables don't need to be used for costly joins or calculations nearly as often, dramatic performance increases are possible when indexed views are present for often-referenced combinations of tables. This is very similar to what happens with cache dependencies: the database gets a query and a request to watch the results of that query to see if it changes. As such, the same restrictions that apply to defining queries for indexed views also apply to defining queries for dependency notifications. These restrictions can be found here: the syntax is a little verbose (references to tables and functions must be fully qualified with the schema name, i.e. schemaname.tablename) and some of the more complex query operators are forbidden, but you should be able to get by fine with these requirements. In general, if you find yourself wanting to specify a complex query for dependency notifications, then you're on the wrong track. I'll go over a few best practices later in this article, but a general rule of thumb is to keep things as simple as possible to reduce the strain on the database server. The second new feature of SQL Server 2005 that we'll look at is notification services. It does exactly what it sounds like: provides a way for the database to send notifications to interested applications. In the case of dependency notifications, when a SqlCacheDependency object is created, ASP.NET will spin off another thread (if it doesn't exist already) that will be responsible for listening for messages from the database and invalidating the correct cache entry when it receives them. You can learn more about notification services here.

Some example code

As is often the case with well-implemented portions of the .NET 2.0 framework, the SqlCacheDependency class contains a ton of power but, in practice, is almost absurdly simple to use. Let's go through the sample code below:

The first thing that we do is check the cache to see if we already have the first name for this particular user ID. If so, we just return it and continue on our way. Otherwise, we get the first name from the database and store it in the cache along with a dependency. To do this, we get the connection string for our database from the config file and then start dependency watching for the connection. Prior to creating a SqlCacheDependency object for a connection, you first have to call SqlDependency.Start() and pass it the connection string: this function is responsible for setting up a notification services queue on the database server and spinning off the thread that listens for messages from that queue and invalidates the appropriate cache entries. This only needs to be done once per database, so you can do this in the Application_Start event handler in your application's global.asax file or just do it in the function itself like I have here. Next, we open the connection and define the SqlCommand object that we're going to use to retrieve the data and drive the dependency. Notice that I had to qualify the table reference with the name of the schema to which it belongs, or it would not have worked. While we're on the topic of valid queries, do careful testing of all queries you using for cache dependency notifications. If you specify one that does not meet the requirements, you won't get an exception, but mostly your cache entry will be invalidated as soon as its added. However, sometimes I've seen a cache entry never be invalidated so, again, do thorough testing of any new dependency queries. Moving on, once the SqlCommand object has been created, we construct a new SqlCacheDependency instance with it, call ExecuteScalar() to retrieve the name from the database, and then add it to the cache using the user ID as the key and specifying the dependency object that we created. Now, whenever the user's first name is updated in the database, the cache entry will be invalidated and, the next time that the function is called, we will have to go through the whole lookup/cache process again.

Words of warning

So all of this caching and use of dependencies sounds great, right? Well, it is, provided that it's used properly. Like indexed views, cache dependencies introduce load onto the database server so they are not appropriate when the underlying data is complex (i.e. involving a lot of joins) to access or changes often relative to how often it's accessed. The dependency isn't going to serve much of a purpose if the entry gets invalidated after two or three cache hits: the performance savings gained by those few cache hits isn't worth the cost associated with setting up the dependency. However, for relatively static data that might only change once a week or once a month, SqlCacheDependency objects are very useful. To further reduce load on the database server, try to reduce the surface area that the dependency watches as much as possible. For instance, if you have an updated date column in a Users table that your data access layer updates every time it changes a User object, just watch that instead of watching all of the columns in the table. It won't help in the case where somebody manually updates the data via Query Analyzer, but it can help to minimize the impact that the cache dependencies have on the database server and, hey, people probably shouldn't be cowboying your live data anyway :). So, in conclusion, the SqlCacheDependency object is very powerful and can create enormous performance gains in an application, provided that you understand how to use it properly.

<<  Previous Article Continue reading and see our next or previous articles Next Article >>

About Luke Stratman

Sorry, no bio is available

This author has published 2 articles on DotNetSlackers. View other articles or the complete profile here.

Other articles in this category

Lucene.NET vs SQL Server Full-text – Generating a million records and a full-text index
In this article we will take a look at how SQL Server performs with one million records in a table. ...
Identifying currently running SQL queries
This article is taken from the book SQL Server DMVs in Action. The author discusses a routine that i...
Easy Insert, Update and Retrieve Values for Microsoft SQL Database with C#, Visual Studio
This article will provide you with the small amount of code required to insert, update and retrieve ...
What's blocking my running SQL?
This article is taken from the book SQL Server DMVs in Action. The author discusses a routine that i...
SQL Azure to Developers: Part 1
In this part we will focus on overview of SQL Azure along with a first look on SQL Azure Management ...

You might also be interested in the following related blog posts

Quick & Dirty Solution to Employing .NET's ".input-validation-error" Class w/ Bootstrap's ".has-error" & ".has-success" Classes read more
A Simplified Parameterized Query Class in Classic ASP read more
New Entity Framework Feature CTP for VS2010 Beta 2 read more
Using T4MVC strongly-typed helpers with Telerik Extensions for ASP.NET MVC read more
Introducing Versatile DataSources read more
Using T4 Templates for Simple DTOs read more
Mapping references and collections in Telerik OpenAccess ORM (Part 1) read more
Naming Anonymous Types with Generate from Usage read more
VS 2010 Code Intellisense Improvements (VS 2010 and .NET 4.0 Series) read more
Rich Tooltips With jQuery read more

Please login to rate or to leave a comment.