Sunday, November 02, 2003 10:54 PM bart

Database cache invalidation

I've been playing around with this exciting new feature of ASP.NET v2.0. It's really cool to see it working on my system :-). Let me give a short overview of how it works:

  • Almost everyone who has been working with ASP.NET v1.x in some professional way knows of the directive <%@ OutputCache ... %>. This is the way to activate caching in ASP.NET v1.x. What it does it very simple: it holds the (response HTML) result of an .aspx or .ascx for a specified time and in that timeframe every request made to the page or control is responded with this result. However, the problem is that the cached date can be invalid (for example, a price could be updated in the database)
  • In ASP.NET v2.0 there's a class called CacheDependency which can be used to create a dependency for a cache (today you have file-based invalidation support in ASP.NET v1.x)
  • On of those dependencies which are ready-to-run is the SqlCacheDependency which works with SQL Server 7, 2000 and Yukon:
    • Specify the cache dependency in the output cache directive, e.g. sqldependency="Database:Table"
    • In the web.config add a section <sqlCacheDependency> in <system.web><cache>:

      <system.web>
         <cache>
            <sqlCacheDependency enabled="true" pollTime="10000">
               <databases>
                  <add name="Database" connectionStringName="Northwind" />
               </databases>
            </sqlCacheDependency>
         </cache>
      </system.web>
    • Furthermore there is a new section in web.config called <connectionString> in which you add the DSN to the database. The connectionStringName attribute for the cache dependency refers to such a DSN.
  • You can use the SqlCacheDependency class directly from within your code as well, which is not difficult at all.

SQL Server Yukon edition has a feature which interacts with http.sys of IIS 6.0 and higher to notify the webserver of updates (using a so-called Notification Delivery Service over port 80). However, in SQL Server 7 and 2000 you must extend your database using one of the tools in the new .NET Framework "Whidbey", called aspnet_regsqlcache.exe:

  • What is does is creating some extra tables (ChangeNotification table) and a SqlCacheNotification trigger on the tables of the database.
  • There are several ways to use this exe, for example:
       aspnet_regsqlcache.exe -S jefken -E -d Northwind -ed
    which will enable the database Northwind on my server (called Jefken) for SQL Cache Dependencies.

This really rocks! Check out more of this great stuff on the ppts of the PDC on www.asp.net/whidbey/pdc.aspx. Check out the great hands-on labs as well!

Del.icio.us | Digg It | Technorati | Blinklist | Furl | reddit | DotNetKicks

Filed under:

Comments

# re: Database cache invalidation

Tuesday, April 27, 2004 5:51 PM by bart

I get an error that the <cache> section is an unrecognized configuration section. Any ideas?

# re: Database cache invalidation

Tuesday, April 27, 2004 11:47 PM by bart

It should work if you're using the PDC build of ASP.NET version 2.0 (a.k.a. "Whidbey").

# re: Database cache invalidation

Tuesday, May 04, 2004 11:23 AM by bart

i also get

"Unrecognized configuration section cache"

when ASP.NET tries to parse web.config.

we've got the Community Tech Preview installed here. Tried it on 2 machines, same error. Interestingly no error with the <connectionStrings/> node with i believe is a ASP.NET 2.0 feature.

# re: Database cache invalidation

Wednesday, May 12, 2004 11:23 PM by bart

Try using 'caching' instead of cache.

# re: Database cache invalidation

Wednesday, September 15, 2004 12:21 PM by bart

According to the 2.0 guided tour, you need not do the web.config work for Yukon:

http://beta.asp.net/GuidedTour/s24.aspx

Yukon doesn't do table-level locking, it detects changes at the row-level.

(However, I still can't get it to work!)

Cheers,

Dave