Caching in ASP.NET with the SqlCacheDependency Class
ASP.NET allows you to use the SqlCacheDependency class to create a cache item dependency on a table or row in a database. When a change occurs in the table or in a specific row, the item that has a dependency is invalidated and removed from the cache. You can set a dependency on a table in Microsoft SQL Server 7.0, SQL Server 2000, and SQL Server 2005. If you are using SQL Server 2005 you can also set a dependency on a specific record.
Using caching with a SQL dependency can dramatically increase application performance in certain scenarios. For example, imagine you are building an e-commerce application that displays product information from a database. Without caching, your application must request the data from the database each time a user wants to view a product. You could cache the product information for a day at a time, ensuring fast response times because the product information is already in memory. However, if product information changes, the cached product information might then be out of sync with the data for up to a day.
Using SQL cache dependency, you could cache your product information and create a dependency on a database table or row change. When the data changes—and only then—the cache items based on that data are invalidated and removed from the cache. The next time you request that item from the cache, if it is not in the cache, you can re-add the updated version to the cache and be assured that you have the latest data.
SQL cache dependency is also available for the page output cache. For example, you could create a page named ViewProduct.aspx that shows information about a particular product. You could set that page's cache policy to be a SQL dependency as you would for an item you added manually to the cache. The page would then be stored in the cache until the table or row that it was dependent on changed. When the data changed, the page would be recreated and stored in the output cache again.
For more information see ASP.NET Caching Overview.
Features
ASP.NET SQL cache dependency offers the following features:
You can use SQL cache dependency for both the application cache and the page output cache.
You can use SQL cache dependency with SQL Server 7.0 and later versions.
You can use SQL cache dependency in a Web garden (multiple processors on one server) or a Web farm (multiple servers running the same application).
The database operations associated with SQL cache dependency are simple and therefore do not incur a heavy processing cost on the server.
You do not need extensive SQL knowledge to configure SQL cache dependency in your application and in SQL Server. ASP.NET includes tools that automate the configuration. Additionally, you can use the SqlCacheDependencyAdmin class to programmatically configure SQL cache dependency.
SQL Server 7.0 and SQL Server 2000 Implementation
ASP.NET implements a poll model for SQL Server 7.0 and SQL Server 2000 cache dependency. A thread within the ASP.NET process polls the SQL Server database at a specified time interval to determine whether the data has changed. If so, dependent cache items are invalidated and removed from the cache. You can specify the poll interval in your application declaratively in the Web.config file or programmatically using the SqlCacheDependency class.
SQL cache dependency is limited to data changes at the table level for SQL Server 7.0 and SQL Server 2000. You can configure ASP.NET to poll the database for changes in a table, but not in a specific row.
Enabling SQL Caching
In order to use SQL cache dependency in SQL Server 7.0 and SQL Server 2000, you must configure SQL Server to support it. ASP.NET provides utilities to configure SQL caching on SQL Server, including a tool named Aspnet_regsql.exe and the SqlCacheDependencyAdmin class. For more information on enabling SQL cache dependency with SQL Server see How to: Cache Page Output with Cache Key Dependencies.
SQL Server 2005 Implementation
SQL Server 2005 implements a different model for cache dependency than SQL Server 7.0 and SQL Server 2000. You do not need to go through any special configuration steps to enable SQL cache dependency on SQL Server 2005. Additionally, SQL Server 2005 implements a change notification model where notifications are sent to subscribing application servers, rather than relying on the polling model required in earlier versions of SQL Server.
SQL Server 2005 cache dependency is more flexible in the types of changes that receive notification. SQL Server 2005 monitors changes to the result set of a particular SQL command. If a change occurs in the database that would modify the results set of that command, the dependency causes the cached item to be invalidated. This allows SQL Server 2005 to provide row-level notification.
There are some requirements for the query used to test for changes. You must provide fully qualified table names, including the owner name (for example, dbo.authors). In general, SQL 2005 notification supports Select queries as well as stored procedures, and supports multiple and nested queries, but does not support aggregate operations such as COUNT(*). For more information about what queries are supported and the rules for notification in SQL Server 2005, see the SQL Books Online topic named "Creating a Query for Notification."
Configuring SQL Caching in ASP.NET Application
Once you have configured SQL Server 7.0 or SQL Server 2000 for cache dependencies, or have created the appropriate command dependency in SQL Server 2005, you can configure your application to use SQL cache dependency just as you would configure any other cache dependency. For example, you can create a cache profile in the Web.config file and then reference that cache profile on each page that should use the SQL cache dependency. You could also use SQL cache dependency by enabling it programmatically using the SqlCacheDependency class. For more information, see How to: Cache Page Output with Cache Key Dependencies.
See Also
Tasks
How to: Cache Page Output with File Dependencies