SQL: Understanding Cache Dependencies in ASP.NET

SQL: Understanding Cache Dependencies in ASP.NET

I’ve had a number of clients over the years who were wondering how the SQL dependencies in ASP.NET evolved, and what they were used for. I’ve updated a previous blog post about this below.

Traditional ASP Applications

In traditional ASP applications, every time the user would request a page, the application would retrieve the data from the database to render the page. Clearly this doesn’t scale very well. It works for a small number of users but not a large number. Often though, this data access can be completely avoided for certain types of data.

Consider the Warehouse.StockGroups table in the WideWorldImporters database. Realistically, how often would it change? If we are building an application that displays the list of stock groups, it doesn’t make sense to retrieve it from the database on every request. We could improve on this situation if the same user was retrieving this list regularly by holding it in the user’s Session state but that also wasn’t that helpful if there were a large number of users as many copies of the same data would be held.

ASP.NETCache Object

A key advance in ASP.NET was the introduction of the Cache object. It provided a server-wide shared cache that could be used to hold relatively static data. We could read the list of stock groups into a dataset and hold it in the Cache.

The issue with any cached data though is when do I need to refresh the cache? If we choose to refresh it once per day, we then could be showing an incorrect list for up to an entire day. If we base the refresh policy on elapsed time, we have a trade-off. The shorter the refresh time, the fresher the data but the less useful the cache is. The longer the refresh interval, the more efficient the cache becomes but the staler the data potentially is.

The ASP.NET Cache object back in v1 gave us two options for cache invalidation. One was time based, and the other was based on a file dependency. This was originally designed to allow for situations where the data was coming from an XML file stored in the filesystem. A filesystem watcher was set up to monitor the file. If the file had changed, the Cache would be invalidated and refreshed.

But the invalidation option that we really wanted was when the data changes.

Cache Invalidation Prior To ASP.NET v2

Prior to ASP.NET v2, there were a number of attempts to provide cache invalidation when the data changed. They were messy. The most popular version was:

  • Create an extended stored procedure (now deprecated) in SQL Server that simply touched a nominated file in the filesystem.
  • Add a trigger (for INSERT, UPDATE and DELETE) on the table to be monitored that called fired the trigger.
  • Set the cache to have a dependency on that file.

This option was miles ahead of where we started but it had a few downsides.

First, writing extended stored procedures for SQL Server was not trivial, and is now deprecated. It couldn’t be done in managed code but a C++ template was available to use as a starting point.
Second, the trigger would fire for any changes to the table, even if our query was only for certain rows in the table.
Third, the mechanism was table-based and didn’t deal with queries that involved joins across multiple tables.
Finally, the mechanism was tied to a specific database engine and often to a specific version of a database engine.

ADO.NETv2 SqlDependency and SQL Server 2005

SQL Server 2005 added a feature called Query Notifications. (NOTE: this has nothing to do with Notification Services – even experienced people used to confuse these).

What Query Notifications let you do was to say to SQL Server Here is a query I want you to execute. However, when you finish, I don’t want you to forget the query, I want you to remember it and, if the underlying data that it was based on changes in such a way that if I was to re-execute the query I’d get a different response, I want you to tell me about it.

This is exactly what we needed to support our ideal Cache invalidation scenario. We don’t want to re-execute the query, unless the data has changed.

You might be thinking that sounds like a lot of hard work for the database engine. Well it is a bit of work but it’s in fact utilising the same mechanism that was already in place for detecting data changes that was used for Indexed Views as far back as in SQL Server 2000. What it did mean is that we had some limitations on the types of query that this would work with:

  • Must use two part names for tables (SQL Server needs to know which schema so it knows exactly which table, rather than having to resolve that at query time)
  • Reasonable complexity (not all queries can be implemented)
  • Specific column lists (ie: No SELECT * FROM somewhere)

Returning the data

Normally with SQL Server, we send a query and get a response. We don’t expect unannounced data to arrive sometime later. So the next issue is how can SQL Server send us the notification?

To do this it uses another new feature that was added at the same time, called Service Broker. Service Broker is a transacted queue that lives within the database server. Communication occurs between services attached to endpoints. We set up an endpoint ready to receive the notification.

Note: if you want to learn about Service Broker, we have an online, on-demand course on it here:

SQL Server Service Broker for Developers and DBAs .

ADO.NET team made it easier

The ADO.NET team did a lot of the work for us. They created a new object called the SqlDependency. It is a simple programmable object that wraps most of this functionality for us. Basically, it’s used by doing this:

  • Create a SqlCommand you wish to execute
  • Wrap a new SqlDependency around the command (you pass the command to the constructor of the dependency)
  • An event on the SqlDependency object fires when the data changes.

It’s that easy. The one remaining requirement is to set up the queue to listen for the notifications. You do this by calling the Start() method on the SqlDependency object once before you get going.

Use with applications

This new object could be used in any ADO.NET v2 application, including in WinForms applications. However, its primary use-case was for ASP.NET Cache invalidation. You can use it in code like this:

using (SqlConnection conn = new SqlConnection("some connection string"))
{
      SqlCommand cmd = new
          SqlCommand("SELECT EmployeeID, EmployeeName FROM Employees",conn);
      SqlCacheDependency dependency = new SqlCacheDependency(cmd);
      SqlDataAdapter employeesDA = new SqlDataAdapter(cmd);
      DataSet employessDS = new DataSet("EmployeesDS");
      employeessDA.Fill(employeesDS,"Employees");
      Cache.Insert("emp", employeesDS, dependency);
}

ASP.NET v2 SqlCacheDependency

Even though the ADO.NET v2 team had done most of the work, the ASP.NET team decided to make it even easier to use with the Cache object. In v2, the Cache object could have a SqlCacheDependency in addition to the previous options of file and time dependencies. This can be applied to the Cache object and also to other cached objects like user controls. You can specify a directive like:

<%@ OutputCache Duration="2400" VaryByParam="none"
               SqlDependency="northwind:Employees" %>

You can also directly use the SqlDependencies in code:

Cache.Insert("emp",obj,
        new SqlCacheDependency("northwind","Employees"));

The code shown is a general form of the SqlCacheDependency settings. In ASP.NET v2 and later, when using SQL Server’s special mechanisms, we use the specific string CommandNotification rather than database and table names.

What if I didn’t have SQL Server?

Rather than tying their implementation directly to SQL Server 2005 and later, the ASP.NET team also decided to create a pluggable architecture so other forms of SqlCacheDependency could be provided.

For example, at the time, they have provided an implementation for SQL Server 2000. As SQL Server 2000 doesn’t offer query notifications or service broker, the implementation wasn’t as good as the one for 2005.

It worked like this:

  • A table was created to hold details of change notifications. (For early SQL Server versions like 2000, a command was provided to help with this: aspnet_regsql.exe –d databasename –ed –E)
  • Triggers were set up on the required tables. (Again a command was provided to assist: aspnet_regsql.exe –d databasename –et –t tablename –E)
  • ASP.NET polled the change notification table to look for changes.

Obviously polling is not as efficient as being notified but was still a whole lot more efficient that having the application constantly looking up and/or reloading all the data.

The same logic could also be adapted for use with other database engines.

I hope that explains some of the background for these options that are still in use at many sites today.

2026-03-03