ASP.NET “Whidbey” Database Cache Invalidation

Here’s a common scenario:

You’ve built a dynamic web site with ASP.NET; for example: the Commerce Stater Kit. You have a products page that displays detail (from the database) about a requested product specified by a query string parameter, e.g. /products/default.aspx?ProdID=5. A page such as this is usually a great candidate for the ASP.NET Output Cache using the declarative OutputCache directive:

<%@ outputcache duration=”3600″ varybyparam=”ProdID” %>

If you’ve used ASP.NET, you’re likely very familar with the output cache and the concept of dependencies. In the code sample above, a time based dependency is used to control how long a particular page can remain in the cache before being removed from the cache. Specifically, the above directive states: cache the output generated by the page in memory for 1 hour varying the entries by the ProdID query string or post-parameter.

There is a common problem here … what if a manager wanted to change the price of a product from $10 to $8. In the current model you would either have to manually evict the page from cache using the Response.RemoveOutputCacheItem or wait until the hour expired before the update would occur automatically.

To address this problem we’ve added support for database cache dependencies in ASP.NET “Whidbey”, i.e. when the data changes in the database remove the item from the ASP.NET cache. We support this for SQL Server 7, 2000, and “Yukon”. The support for SQL 7 and 2000 is a table level change notification, i.e. anything in the table changes and we notify you. The SQL “Yukon” model is granular enough to alert us when the results from a stored procedure change.

For SQL 7 and 2000 it’s as simple as:

<%@ outputcache duration=”3600″ varybyparam=”none” sqldependency=”MyDatabase:Authors” %>

This new sqldependency allows you to name a database entry from web.config (that’s the ‘MyDatabase’ part) as well as the table to watch for changes: Authors. For SQL Server “Yukon“ it’s even easier — in fact support is built directly into the SqlCommand class.

Below is a sample using the OutputCache API with SQL Server “Yukon“:

Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)

Response.Write(“Page generated: ” + DateTime.Now.ToString(“r”))
Dim connection As New SqlConnection(ConfigurationSettings.ConnectionStrings(“MyConnectionString”))
Dim command As New SqlCommand(“SELECT * FROM Products”, connection)
Dim sqlDependency As New SqlCacheDependency(command)

connection.Open()

GridView1.DataSource = command.ExecuteReader()
GridView1.DataBind()

connection.Close()

Response.AddCacheDependency(sqlDependency)
Response.Cache.SetValidUntilExpires(True)
Response.Cache.SetExpires(DateTime.Now.AddMinutes(60))
Response.Cache.SetCacheability(HttpCacheability.Public)

End Sub

In the above code snip you can see the SqlCommand object being passed to the SqlCacheDependency constructor.

The other really cool part is that we’ve also added the ability for you to write your own ‘custom’ cache dependencies. So if you want, for example, to support row level change notification for SQL 7 or 2000 — you can now do it by deriving from CacheDependency. For example:

public class StockPriceCacheDependency: CacheDependency { … }

… and then in your page code:

Dim dep As New StockPriceCacheDependency(“MSFT“, 5)

Cache.Insert(“MSFT“, dep.StockPrice, dep)< /P>

In the above code, whenever the stock price of Microsoft — or other stock — changes the cache dependency would force the evicition of the data from the application using the custom ‘StockPriceCacheDependency’ (I’ll do another blog entry about this in more detail).

So armed with the ability to create custom cache dependencies you could built depdencies for the AS/400 server, an Oracle database or any other type of dependency you can think of.

If you would like to learn more, come to the PDC WS300 “Advanced Caching Techniques” on either Tuesday or Wednesday. You can also download the slides and demos from www.asp.net/whidbey/.  I’ll also blog about this as well as other new ASP.NET “Whidbey” features more over the coming weeks.