<%@ Page Language="C#" MasterPageFile="~/aspnet/section.master" %> <%@ Register TagPrefix="Acme" TagName="SourceRef" Src="~/util/SrcRef.ascx" %> <%@ Register TagPrefix="Acme" Namespace="Acme" %>

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. Refreshing the page should then show the updated data and a new timestamp.

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" ... />
  3. System.Data.SqlClient.SqlDependency.Start() method must be called somewhere in the application before the first SQL query is executed. This method could be placed in Application_Start() event in global.asax file.
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.

Common issues that prevent queries from working with Sql Server 2005 query notifications are:
  1. Queries must explicitly include column names in the SELECT statement. Using "SELECT *" results in a query that will not be registered with Sql Server 2005 query notifications.
  2. Table names in queries must include the ownername. For example, if you issue a query against the authors table in the pubs database, the query must reference the table as "dbo.authors".
  3. The security identity running the query must have rights to register queries for notification in Sql Server 2005. This right can be granted with the following T-SQL command: GRANT SUBSCRIBE QUERY NOTIFICATIONS TO username.
  4. The security identity running the query must also have rights to send query notifications from Sql Server 2005. This right can be granted with the following T-SQL command: GRANT SEND ON SERVICE::SqlQueryNotificationService TO username.
Note: There are a number of 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 in ASP.NET, with the end result being that the cache dependency is always invalid and hence any associated queries are always executed on each page request. <%-- Notification-based Invalidation

This mechanism uses the query change notification mechanism of the "Yukon" version of SQL Server to detect changes to the results of queries. To enable this mechanism, set the SqlCacheDependency property to "CommandNotification" instead of a connection/table pair.

--%>