ASP.NET SqlCacheDependency Redux
By Peter A. Bromberg, Ph.D.

Peter Bromberg

"A man can't just sit around."
-- Lawn Chair Larry from L.A., 1982

Some time ago, I cobbled together an interesting way to create a SQL Dependency Cache invalidation scheme by adding a trigger to the subject table that called the sp_makewebtask stored proc to "touch" a file which was monitored as a file dependency, thereby creating (in a roundabout way) a "poor man's" SqlCacheDependency. You can review this article if you like, here.

At Tech-Ed 2004 in San Diego, Rob Howard presented, among other neat tricks, a detailed view of the new ASP.NET 2.0 SqlCacheDependency object. One of the high points of his "Black Belt" discussion was an example of a custom SqlDependency Cache arrangement for ASP.NET 1.1 that he had put together in almost exactly the way that it works in Visual Studio.NET 2005.

The importance of the judicious use of caching cannot be underestimated. At Tech-Ed, Howard demonstrated conclusively that caching objects for as little as one second could have a profound effect on scalability in terms of maximum requests per second. We are talking about scalability improvements on the order of going from 100 requests per second up to 500 requests per second with caching in place! Howard went on to explain the many tricks that are used for the web site, which runs on only 2 web servers and one database server, with plenty of room to spare. I'd recommend his slide deck, which should be available at the above link, to any .NET developer at any experience level.

Rob's sample solution had a number of things mixed together, but it was missing the code to create the changenotifications table, so what I've done is include that, remove all the non-relevant code, and rework the solution as a "Stand-alone" example of his SqlDependency code alone. I do this because I think it is an important tool, and since Rob's been kind enough to publish the demo slides and code, I thought some developers would like to be able to use this "on its own" or as the basis for additional work.

The neat thing about Rob's code is that it does it's cache - check polling on a background thread (same as ASP.NET 2.0), leaving your application to be totally responsive and do its merry thing. If anything changes in the monitored table, the cache is invalidated. The proof of concept is a simple page with a DataGrid and a label that shows the time that the request was last processed from the database. If you reload the page and nothing in the database has changed, the time stays the same since the DataGrid gets populated out of Cache. The second you change anything in the subject table and reload the page, the time label changes, showing that a new database request was made for fresh data, and of course you now have a new Cache object in effect.

The key to the scheme is the HttpModule:

using System;
using System.Web;
using System.Threading;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace BlackbeltBLL {
    public class BackgroundService : IHttpModule {
        static Timer timer;
        int interval = 5000;
        public String ModuleName { 
            get { return "BackgroundService"; } 

        public void Init(HttpApplication application) { 
            // Wire-up application events
            if (timer == null)
                timer = new Timer(new TimerCallback(ScheduledWorkCallback), 
application.Context, interval, interval); } public void Dispose() { timer = null; } private void ScheduledWorkCallback (object sender) { HttpContext context = (HttpContext) sender; Poll(context); } void DoSomething (HttpContext context) { } #region DB Poll void Poll (HttpContext context) { SqlConnection connection =
new SqlConnection(ConfigurationSettings.AppSettings["Northwind"]); SqlCommand command =
new SqlCommand("SELECT * FROM changenotification", connection); SqlDataReader reader; string key = ConfigurationSettings.AppSettings["SqlDependency"]; connection.Open(); reader = command.ExecuteReader(); while (reader.Read()) { string tableKey = String.Format(key, reader["Table"]); if (context.Cache[tableKey] != null) { int changeKey =
.Parse( context.Cache[ String.Format(key, reader["Table"])].ToString() ); if (changeKey != int.Parse( reader["ChangeID"].ToString() )) context.Cache.Remove(tableKey); } } connection.Close(); } #endregion } }

HttpModule and settings are set up in the web.config as follows:

<add key="Northwind" value="server=(local);database=northwind;uid=sa;pwd=" />
<add key="SqlDependency" value="SqlDatabaseCache-{0}" />

<add name="BackgroundService" type="BlackbeltBLL.BackgroundService, BlackbeltBLL" />

Finally, we have Rob's SqlCacheDependency class:

using System;
using System.Web;
using System.Web.Caching;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace BlackbeltBLL {  
    public class SqlCacheDependency {
        public static string CreateTableDependency (string table) {
            string dbKey = ConfigurationSettings.AppSettings["SqlDependency"];
            // Format our cache key
            dbKey = String.Format(dbKey, table);
            if ( HttpContext.Current.Cache[dbKey] != null)
                return dbKey;
            // Add table entry into the cache
            if ( HttpContext.Current.Cache[dbKey] == null ) {
                HttpContext.Current.Cache.Insert(dbKey, GetTableKey(table));
            return dbKey;

 public static string CreateTableDependency (string table, string key, object value) {
            string dbKey = CreateTableDependency(table);
            // Create key dependency
            string[] s = new string[] {dbKey};
            CacheDependency d = new CacheDependency(null, s);
            // do the actual insert
            HttpContext.Current.Cache.Insert(key, value, d);
            return dbKey;

        public static int GetTableKey (string table) {
            SqlConnection connection = 
new SqlConnection(ConfigurationSettings.AppSettings["Northwind"]); SqlCommand command =
new SqlCommand("SELECT ChangeID FROM ChangeNotification WHERE [Table] = '"
+ table + "'", connection); SqlDataReader reader; int changeID = -1; connection.Open(); reader = command.ExecuteReader(); reader.Read(); changeID = (int) reader["ChangeID"]; connection.Close(); return changeID; } } }

So essentially, when your page is requested and you've made your database request for your data, you add the following code:

// Get Cache Key for db caching

You have added a CacheItemDependency of type SqlCacheDependency, and every 5000 milliseconds, your HttpModule's BackgroundServiceClass will call its Poll method, checking to see if the dependency has been invalidated by checking the changeId from the changenotifications table. If it is, it calls context.Cache.Remove(tableKey); and your Cache dutifully resurrects its contents. Sweet!

Oops, did you notice I forgot something? Nothing gets put in changenotification table if Products changes, does it? So I need a trigger. N.B. Reader Doug Nelson (see post below) suggested something much more effective, and so I've updated this here (and included it in the source code download. Also, changed the polling to be a sproc and employed his delete all the rows idea - since your stuff is in cache anyway)

if not exists( select null from changenotification where [table]= 'products' )
insert changenotification ([table]) values('products')

This is a relatively simple implementation, but it can be used as the basis for a remarkably powerful scalability enhancement in any web site where data can be changed and it is important to be able to show the most up-to-date contents.

Download the Solution that accompanies this article


Peter Bromberg is a C# MVP, MCP, and .NET consultant who has worked in the banking and financial industry for 20 years. He has architected and developed web - based corporate distributed application solutions since 1995, and focuses exclusively on the .NET Platform.
Article Discussion: