SqlCacheDependency with ASP.Net

ASP.Net websites drawing their data and content from databases tend to be relatively inefficient in terms of their data access strategy – regularly fetching and re-fetching data on each page that hasn’t change between requests. Often this isn’t a problem, but as traffic scales it becomes more and more important to consider an effective caching strategy.

The converse issue of course is returning data that is out of date –time based caches work well, except for the poor person updating the content of the site who is wondering why his or her changes can’t be viewed.

ASP.Net provides a useful technique where the expiry of the cache can be tied to a table (or tables) in the database – meaning that the cache is only flushed when the data changes (or the application is restarted of course).

This post is intended to detail the steps required to set up and use this on SQL 2005 (the method has changed from previous versions of SQL server where a poll based mechanism was used).

Configure Database

Run the following statement on your database:

ALTER DATABASE mydb SET ENABLE_BROKER

Because this requires an exclusive lock on the database, you may find it takes a very long time. To close existing sessions and run at once, use this:

ALTER DATABASE mydb SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE

You can check the result with this statement - should return 1 if all is well.

SELECT is_broker_enabled FROM sys.databases WHERE name = 'mydb'

Setting up Notifications

Before any SQL queries are made, the notification listener needs to be started. This creates and keeps a connection to the database, through which notification updates are received. The best place for this is in Application_OnStart in the global.ascx file:

void Application_Start(object sender, EventArgs e)
{
System.Data.SqlClient.SqlDependency.Start(ConfigurationManager.ConnectionStrings["DSN"].ConnectionString);
}

This process requires a number of permissions to view system tables and set up procedures, therefore it needs to be run with appropriate database permissions to do this. Normally you would want to configure your website to run using a database login with minimal permissions (e.g. just SELECTS and EXECUTES), so this may need to be changed to use a different user account for this one process.

In web.config, you need to add a section like the following within <system.web>:

<caching>
<sqlcachedependency enabled="true">
 <databases>
   <add name="mydb"
     connectionStringName="DSN"
     />
 </databases>
</sqlcachedependency>
</caching>

Using the Cache

The way I tend to use the cache is in my business logic layer (BLL – a layer in between the presentation layer and the data access layer (DAL) where the database specific code is held).

By adding an extra parameter to my factory methods (methods that instantiate typed objects or collections) I can select whether to use caching or not via a configuration file. If that is set, a cache key is defined – basically a string made up of other parameters that would change the generated SQL or stored procedure call and hence the output.

I look up this cache key, if there’s something found, it’s returned. If not, I go to the database to retrieve the data for populating the object or collection, store the result in the cache and return it.

e.g.

In BLL

public static Language GetLanguage(int intID, bool blnUseCaching)
{
Language objLanguage;
string strCacheKey = String.Format("Language_{0}", intID);
if (blnUseCaching && System.Web.HttpContext.Current != null && System.Web.HttpContext.Current.Cache[strCacheKey] != null)
 objLanguage = (Language)System.Web.HttpContext.Current.Cache[strCacheKey];
else
 objLanguage = DataAccess.LanguageMethods.GetLanguage(intID, "");
if (blnUseCaching)
 System.Web.HttpContext.Current.Cache.Insert(strCacheKey, objLanguage, DataAccess.LanguageMethods.GetCacheDependency());
return objLanguage;
}

In DAL (DataAccess.LanguageMethods)

public static SqlCacheDependency GetCacheDependency()
{
SqlCacheDependency dep = null;

try
{
 dep = new SqlCacheDependency("app", "dbo.tblLanguages");
}
catch (TableNotEnabledForNotificationException)
{
 SqlCacheDependencyAdmin.EnableTableForNotifications(DataAccessUtilities.ConnectionString(), "dbo.tblLanguages");
 dep = new SqlCacheDependency("app", "dbo.tblLanguages");
}

return dep;
}

public static Language GetLanguage(int intID, string strURLFolder)
{
IDataReader dr = null;
Language objLanguage = null;

Object[] arrParamValues = new Object[1];
arrParamValues[0] = intID;
dr = DataAccessUtilities.GetDataReaderFromProcedure(DataAccessUtilities.ConnectionString(), "spGetLanguage", arrParamValues);
try
{
 if (dr.Read())
 {
     objLanguage = new Language(dr.GetByte(0), dr.GetString(1), dr.GetString(2), dr.GetString(3), dr.GetString(4));
 }
}
catch (Exception ex)
{
 throw (ex);
}
finally
{
 dr.Close();
}     
return objLanguage;
}

Creating a Dependency on More Than One Table

In many, perhaps most, situations database queries involve more than one table – and hence it wouldn’t be necessary to tie the cache to each. SQLCacheDependencies can only be associated with a single table however.

The key here is to make use of the AggregateCacheDependency – a mechanism for combining several other types of dependency to a single one, such that a change in any of the dependent tables or files will cause the cache to be flushed.

SqlCacheDependency
dep1 = new SqlCacheDependency ("app", "tblUsers"),
dep2 = new SqlCacheDependency ("app", "tblRoles"),

AggregateCacheDependency depAgg = new AggregateCacheDependency ();
depAgg.Add (dep1, dep2);

Cache.Insert ("Users", data, depAgg);

For more details head to MSDN

For details on how to use this technique with older versions of SQL server again there is information at MSDN and ASP.Net Resources