ASP.NET 2.0 Cookbook, 2nd Edition

16.5. Caching Pages Based on Database Dependencies

Problem

You have pages in your application you want to cache but the data on the pages is retrieved from a database.

Solution

Configure SQL Server to support notifications when data changes (not required for SQL Server 2005 and SQL Server Express Edition), configure your application to use SQL dependencies by adding the <sqlCacheDependency> element to web.config, and add the @ OutputCache directive at the top of the .aspx file of each page you want to cache.

Discussion

ASP.NET 1.x provided many ways to cache data with dependencies, but caching data with database dependencies was not one of them. Fortunately, ASP.NET 2.0 has added the ability to cache pages with dependencies on data in a SQL Server database. SQL Server 7.0, 2000, 2005, MSDE, and SQL Server Express Edition are all supported, in one form or another.

The key to determining when cache content should be made to expire is knowing when the data in the database changes, which differs depending on the vintage of the SQL Server product your application uses.

SQL Server 2005 and SQL Server Express Edition provide notification events that can be used to notify an application the data has changed. This approach is referred to as notification-based invalidation. What’s more, neither one requires any configuration changes to support this capability, so you can skip to the portion of the discussion where we describe adding the <sqlCacheDependency> element to web.config.

SQL Server 7, ...