Home   |   QuickStart Welcome   |   ASP.NET   |   Web Services   |   How Do I...?   
  |   I want my samples in...      

ASP.NET 2.0 Quickstart Tutorials

SQL Cache Invalidation New in 2.0

In the previous example, the data was cached for 60 seconds, regardless of whether the data has changed in the database. SQL cache invalidation enables you to make the cache entry dependent on the database, so the cache entry will only be cleared when data in the database is changed.

Polling-based Invalidation

This mechanism uses polling to check if a table has been updated since the page was cached. To enable table based caching requires the following steps:

1) Enable notifications for the database using the aspnet_regsql.exe tool.
>aspnet_regsql.exe -S ".\SQLExpress" -E -d "pubs" -ed
This only needs to be done once for each database.

2) Enable notifications for the table(s) you want to have dependencies on using the aspnet_regsql.exe tool.
>aspnet_regsql.exe -S ".\SQLExpress" -E -d "pubs" -et -t "authors"
3) Register the notification in the configuration for the application.
<system.web>
    <caching>
      <sqlCacheDependency enabled="true" pollTime="1000" >
        <databases>
          <add name="PubsDB" connectionStringName="Pubs" />
        </databases>
      </sqlCacheDependency>
    </caching>
</system.web>
The poll time specifies how often the application checks to see whether the data has changed.

4) A SQL dependency can then be used on the OutputCache directive:
<%@ OutputCache Duration="999999" SqlDependency="Pubs:Authors" VaryByParam="none" %>
Or it can be specified directly on a datasource control:
<asp:SqlDataSource EnableCaching="true" CacheDuration="Infinite" SqlCacheDependency="PubsDB:Authors" ... />
The following example uses output caching for a sql datasource using a table based notification. Watch the timestamp at the bottom of the application which should stay static. You can use the GridView example to edit the data. Refeshing the page should then show the updated data and a new timestamp.

VB SqlCacheDependency
Run Sample View Source

Sql Server 2005 Notification-based Cache Invalidation

This mechanism uses the query change notification mechanism of Sql Server 2005 to detect changes to the results of queries. Unlike polling based invalidation for Sql Server 7.0 and 2000, notification based invalidation requires much less setup.

  1. Unlike polling based validation, no <sqlCacheDependency> needs to be registered in your application's configuration. Furthermore, no special configuration using the aspnet_regsql.exe tool is needed.


  2. A notification based dependency is configured on the OutputCache directive using the string CommandNotification. This value indicates to ASP.NET that a notification based dependency should be created for the page or datasource control.

    On a page:
    <%@ OutputCache Duration="999999" SqlDependency="CommandNotification" VaryByParam="none" %>
    On a datasource control:
    <asp:SqlDataSource EnableCaching="true" SqlCacheDependency="CommandNotification" CacheDuration="Infinite" ... />
Whenever a command is issued to Sql Server 2005, ASP.NET and ADO.NET will automatically create a cache dependency that listens to change notifications sent from the Sql Server. As data is changed in Sql Server, these notifications will cause the cached queries to be invalidated on the web server. The next time a page or datasource control associated with the dependency is requested, the page or datasource control will be executed again as opposed to serving cached information.

Note: There are some restrictions on the syntax of queries that support query notifications. For the list of the specific constraints, please see the topic "Creating a Query for Notification" in the Sql Server 2005 Books Online. Also, if it appears that queries are not being cached, and instead are being executed on every page request, it is likely that either the query does not follow the constraints required by Sql Server 2005, or that Sql Server 2005 generated an error when attempting to setup notifications for that query. Currently, either of these conditions cause a silent failure when attempting to setup a cache dependency, with the end result being that the cache dependency is always invalid and hence any associated queries are always executed on each page request.