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, 2000, and MSDE do not support notification events. Instead, the ability to determine when data has changed is implemented by ASP.NET polling a table added to your database. This is referred to as polling-based invalidation. The table (AspNet_SqlCacheTablesForChangeNotification) contains a single row per table being monitored. When the data in a monitored table changes, a trigger is fired that changes the value for the changeId column in the row for the monitored table. The change in the changeId value will be what ASP.NET uses to determine if the data for the monitored table has changed. If your application uses one of these earlier SQL Server products, your database will need to be altered to support notification when data changes. Microsoft provides two options for configuring your database to support notifications. The first option is to use the aspnet_regsql command-line tool. You must first configure the database to support notifications and then specify the tables to be monitored. Use these commands to configure your database. Run this command to configure your database for notifications: aspnet_regsql -S [server] -E -d [database] -ed Run this command to configure a table in your database for notifications: aspnet_regsql -S [server] -E -d [database] -et -t [table] Where: [server] is the name or IP address of your SQL Server [database] is the name of the database to enable [table] is the name of the table to enable
To see all of the options available for configuring your database with the commandline tool, enter the following command: aspnet_regsql -?
The second option for configuring your database for notifications is to use the methods of the SqlCacheDependencyAdmin class. Examples 16-3 (VB) and 16-4 (C#) show the code required to configure your database programmatically. Methods are shown for enabling the database, enabling tables, disabling tables, and disabling the database.  | A reference will need to be added to your project for the System.Web.Caching and System.Web.Data.SqlClient assemblies to configure your database programmatically. |
|
After configuring your database, you need to add the <sqlCacheDependency> element to your web.config to configure ASP.NET to poll your database: <?xml version="1.0"?> <configuration> <system.web> … <caching> <sqlCacheDependency enabled = "true" pollTime = "60000" > <databases> <add name="ASPNetCookbook" connectionStringName = "sqlConnectionString" /> </databases> </sqlCacheDependency> </caching> … </system.web> </configuration>
The pollTime attribute defines the rate at which the AspNet_SqlCacheTablesForChangeNotification table is queried to determine if any data in the monitored tables has changed. The units are in milliseconds. The name attribute of the <add> element of the <databases> element defines the name for the database that will be used in your application when defining SQL dependencies for pages. This does not have to be the database name. The connectionStringName attribute must be set to the name of a connection string in the <connectionStrings> element of web.config. | A pollTime attribute can be added to the <add> element for a database to use different poll rates for each database. When the pollTime is specified for an individual database, it overrides the setting in the <sqlCacheDependency> element. |
|
Now that everything is configured, you can cache pages using the database dependency by adding the @ OutputCache directive to your pages: <%@ Page Language="VB" MasterPageFile="~/ASPNetCookbookVB.master" AutoEventWireup="false" CodeFile="CH16CachedByDatabaseDependencyVB.aspx.vb" Inherits="ASPNetCookbook.VBExamples.CH16CachedByDatabaseDependencyVB" Title="Cache By Database Dependency" %> <%@ OutputCache Duration="86400" VaryByParam="None" SqlDependency="ASPNetCookbook:Book" %> …
The SqlDependency attribute is set to the name of the database (as defined in web.config) and the table that should be used as the dependency for the page. You can specify multiple databases and tables by providing a semicolon-separated list of databases and tables. | When using SQL Server 7, 2000, or MSDE, the finest granularity for determining if data has changed is the table. If any data is added, edited, or deleted in the table, ASP.NET will see it as a change and cause the cache to expire even if it is data that is not explicitly used in your page. This will even occur if a row is updated and the data is not changed. |
|
The ability to cache data dependent on the database contents is a powerful addition to ASP.NET 2.0. This recipe only touches on the functionality available. Refer to Recipe 16.8 and the MSDN Library (search for SqlCacheDependency) for more information of the possibilities available in your application. See Also Recipe16.8 and the MSDN Library (search for SqlCacheDependency) Example 16-3. Methods for programmatically configuring your database for cache expiration (.vb) '''*********************************************************************** ''' <summary> ''' This routine provides the ability to enable the database defined in ''' the passed connection string for notifications that are used for ''' SQL dependency cache invalidations ''' </summary> ''' ''' <param name="connectionStr"> ''' Set to the connection string to the database for which notifications ''' are to be enabled ''' </param> Public Shared Sub enableDatabaseNotifications(ByVal connectionStr As String) Try 'enable the database for notifications SqlCacheDependencyAdmin.EnableNotifications(connectionStr) Catch exc As UnauthorizedAccessException 'user specified in the connection string does not have permissions 'to create tables, etc. in the database 'production application should handle this exception as required Throw Catch exc As SqlException 'other SQL error occurred 'production application should handle this exception as required Throw End Try End Sub 'enableDatabaseNotifications '''*********************************************************************** ''' <summary> ''' This routine provides the ability to enable the the passed table ''' for notifications used for SQL dependency cache invalidations ''' </summary> ''' ''' <param name="connectionStr"> ''' Set to the connection string to the database containing the table ''' to enable ''' </param> ''' <param name="tableName"> ''' Set to the name of the table to be enabled for notifications ''' </param> Public Shared Sub enableTableNotifications(ByVal connectionStr As String,_ ByVal tableName As String) Try 'enable the passed table for notifications SqlCacheDependencyAdmin.EnableTableForNotifications(connectionStr, _ tableName) Catch exc As UnauthorizedAccessException 'user specified in the connection string does not have permissions 'to create triggers, etc in the database 'production application should handle this exception as required Throw Catch exc As SqlException 'other SQL error occurred 'production application should handle this exception as required Throw End Try End Sub 'enableTableNotifications '''*********************************************************************** ''' <summary> ''' This routine provides the ability to disable notifications for the ''' database defined in the passed connection string ''' </summary> ''' ''' <param name="connectionStr"> ''' Set to the connection string to the database for which notifications ''' are to be disabled ''' </param> Public Shared Sub disableDatabaseNotifications(ByVal connectionStr As String) Try 'disable the database notifications SqlCacheDependencyAdmin.DisableNotifications(connectionStr) Catch exc As UnauthorizedAccessException 'user specified in the connection string does not have permissions 'to delete tables, etc. in the database 'production application should handle this exception as required Throw Catch exc As SqlException 'other SQL error occurred 'production application should handle this exception as required Throw End Try End Sub 'disableDatabaseNotifications '''*********************************************************************** ''' <summary> ''' This routine provides the ability to disable the the passed table ''' for notifications used for SQL dependency cache invalidations ''' </summary> ''' ''' <param name="connectionStr"> ''' Set to the connection string to the database containing the table ''' to disable ''' </param> ''' <param name="tableName"> ''' Set to the name of the table to be disabled ''' </param> Public Shared Sub disableTableNotifications(ByVal connectionStr As String,_ ByVal tableName As String) Try 'enable the passed table for notifications SqlCacheDependencyAdmin.DisableTableForNotifications(connectionStr,_ tableName) Catch exc As UnauthorizedAccessException 'user specified in the connection string does not have permissions 'to delete triggers, etc in the database 'production application should handle this exception as required Throw Catch exc As SqlException 'other SQL error occurred 'production application should handle this exception as required Throw End Try End Sub 'disableTableNotifications
|
Example 16-4. Methods for programmatically configuring your database for cache expiration (.cs) '''*********************************************************************** /// <summary> /// This routine provides the ability to enable the database defined in /// the passed connection string for notifications that are used for /// SQL dependency cache invalidations /// </summary> /// /// <param name="connectionStr"> /// Set to the connection string to the database for which notifications /// are to be enabled /// </param> public static void enableDatabaseNotifications(String connectionStr) { try { // enable the database for notifications SqlCacheDependencyAdmin.EnableNotifications(connectionStr); } catch (UnauthorizedAccessException exc) { // user specified in the connection string does not have permissions // to create tables, etc. in the database // production application should handle this exception as required throw; } catch (SqlException exc) { // other SQL error occurred // production application should handle this exception as required throw; } } // enableDatabaseNotifications '''*********************************************************************** /// <summary> /// This routine provides the ability to enable the the passed table /// for notifications used for SQL dependency cache invalidations /// </summary> /// /// <param name="connectionStr"> /// Set to the connection string to the database containing the table /// to enable /// </param> /// <param name="tableName"> /// Set to the name of the table to be enabled for notifications /// </param> public static void enableTableNotifications(String connectionStr, String tableName) { try { // enable the passed table for notifications SqlCacheDependencyAdmin.EnableTableForNotifications(connectionStr, tableName); } catch (UnauthorizedAccessException exc) { // user specified in the connection string does not have permissions // to create triggers, etc in the database // production application should handle this exception as required throw; } catch (SqlException exc) { // other SQL error occurred // production application should handle this exception as required throw; } } // enableTableNotifications '''*********************************************************************** /// <summary> /// This routine provides the ability to disable notifications for the /// database defined in the passed connection string /// </summary> /// /// <param name="connectionStr"> /// Set to the connection string to the database for which notifications /// are to be disabled /// </param> public static void disableDatabaseNotifications(String connectionStr) { try { // disable the database notifications SqlCacheDependencyAdmin.DisableNotifications(connectionStr); } catch (UnauthorizedAccessException exc) { // user specified in the connection string does not have permissions // to delete tables, etc. in the database // production application should handle this exception as required throw; } catch (SqlException exc) { // other SQL error occurred // production application should handle this exception as required throw; } } // disableDatabaseNotifications '''*********************************************************************** /// <summary> /// This routine provides the ability to disable the the passed table /// for notifications used for SQL dependency cache invalidations /// </summary> /// /// <param name="connectionStr"> /// Set to the connection string to the database containing the table /// to disable /// </param> /// <param name="tableName"> /// Set to the name of the table to be disabled /// </param> public static void disableTableNotifications(String connectionStr, String tableName) { try { // enable the passed table for notifications SqlCacheDependencyAdmin.DisableTableForNotifications(connectionStr, tableName); } catch (UnauthorizedAccessException exc) { // user specified in the connection string does not have permissions // to delete triggers, etc in the database // production application should handle this exception as required throw; } catch (SqlException exception) { // other SQL error occurred // production application should handle this exception as required throw; } } // disableTableNotifications
|
| 