Sunday, November 8, 2009

Simple way to invalidate SqlDataSource cache programmatically

EnableCaching and CacheDuration are the normal cache-related properties of the data source controls, but they don't give you the convenience to invalidate the cache on-demand. Some people use the SqlCacheDependency, where ASP.NET worker process will poll for changes in the SqlCacheTablesForChangeNotification when a change in monitored table triggers the "notification". This measure is somewhat clumsy in that:
  1. You have to use aspnet_regsql command line to enable notifications for the database;
  2. ASP.NET uses a polling mechanism, and you have to both write code and alter your web.config
There is a simpler way though: use CacheKeyDependency, where you make the data source cache dependent on another item in the data cache (CacheKeyDependency). Details follow:
  • Add CacheKeyDependency in aspx:
<asp:SqlDataSource ID="x" EnableCaching="True" CacheKeyDependency="MyCacheDependency" />
  • Add some code in aspx.cs:
protected void Page_Load(object sender, EventArgs e)
{ // Or somewhere else before the DataBind() takes place
  if (!IsPostBack)
      if (Cache["MyCacheDependency"] == null)
        Cache["MyCacheDependency"] = DateTime.Now;
  • Where you make changes to the database table and want to invalidate cache so that next time a data binding will see the changes:
// Evict cache items with an update in dependent cache:
Cache["MyCacheDependency"] = DateTime.Now;
You can use any value instead of DateTime.Now as long as they are different to trigger the refresh.
Ref: 1. ASP.NET Caching: SQL Cache Dependency With SQL Server 2000
2. Accessing and Updating Data in ASP.NET 2.0: Declaratively Caching Data


dotnetchris said...

What awesome timing for this blog post, I was just doing some research on the sql datasource and was looking at how to invalidate the cache today and found your post.

Great work.

Anonymous said...

Works perfect! Thanks for the post.

Mark K. said...

Great Post - Thanks a bunch!