Recipe14.10.Tying Database Tables to the Cache

Recipe 14.10. Tying Database Tables to the Cache

Problem

You want to cache datasets you create from a database to help the performance of your ASP.NET application, but you want changes to the data in the database to be reflected in your pages.

Solution

Use the SqlCacheDependency class to expire data in the cache when the underlying database data changes. A SqlCacheDependency sets up a relationship with the database so that, if the data changes, the item in the cache that has this dependency is released from the cache, and the code that established the item can fetch the values from the database again.

To demonstrate this, a SqlCacheDependency object is created for the Authors table in the pubs database in Microsoft SQL Server using the CreateSqlCacheDependency method. The pubs database is a sample database that ships with SQL Server 2000.

 public SqlCacheDependency CreateSqlCacheDependency(string connStr) {     // Make a dependency on the authors database table so that     // if it changes, the cached data will also be disposed of.     // Make sure we are enabled for notifications for the db.     // Note that the parameter has to be the actual connection     // string NOT the connection string NAME from web.config.     SqlCacheDependencyAdmin.EnableNotifications(connStr);     // Make sure we are enabled for notifications for the table.     SqlCacheDependencyAdmin.EnableTableForNotifications(connStr, "Authors");     // This is case-sensitive so make sure the first entry     // matches the entry in the web.config file exactly.     // The first parameter here must be the connection string     // NAME not the connection string itself…     return new SqlCacheDependency("pubs", "Authors"); }

The SqlCacheDependencyAdmin class is responsible for talking to SQL Server to set up the necessary infrastructure (triggers and the like for SQL Server 2000, Cache Sync for SQL Server 2005) for the SqlCacheDependency to fire correctly. The SqlCacheDependency has a section in the application's web.config file under configuration/system.web/caching defining the parameters that the dependency operates under. There are timeout settings for the polling time (for SQL Server 2000 as SQL Server 2005 doesn't poll) and the connection time, as well as a link to the connection string to use via its name. This connection string can be found in the web.config file in the configuration/connectionStrings section. The two entries are shown here:

     <caching>      <sqlCacheDependency enabled="True" pollTime="60000">         <databases>           <add name="pubs" connectionStringName="LocalPubs" pollTime="9000000" />         </databases>      </sqlCacheDependency>     </caching>   <connectionStrings>     <add name="LocalPubs" connectionString="Server=(local);Integrated Security=True;Database=pubs;Persist Security Info=True" providerName="System.Data.SqlClient"/>   </connectionStrings>

Discussion

The main scenario for using SqlDependencyCache is for data that is read frequently but changes very infrequently. The data should be reasonably static as there is overhead associated with keeping the cache in sync with the database table. While the SqlDependencyCache is for use with Microsoft SQL Server, it is just a derived implementation of a CacheDependency class. CacheDependency-based classes could be written for any other database provider, but surprisingly (or perhaps not so) Microsoft SQL Server is the only database with one provided.

When using the SqlCacheDependency class, the first thing to do is insure that Notifications have been enabled for both the database and the table being monitored for changes. If either of these notifications is not enabled for the database and/or table, a DatabaseNotEnabledForNotificationException will be thrown when constructing the SqlCacheDependency. A SqlCacheDependency can also be created directly from a SqlCommand object.