Implementing SQL Server 7/2000 Dependency Caching

You are now going to configure your Web application to use SQL Server 7/2000 dependency caching and then add a page that takes advantage of it.

The ASP.NET 2.0 web.config schema has a new <caching> tag for configuring SQL Server 7/2000 dependency caching.

1.  Open the web.config file. After the <system.web> element place the the following <caching> element:

<caching>
    <sqlCacheDependency enabled="true" pollTime="5000">
      <databases>
        <add name="pubs" connectionStringName="pubsConnectionString1" />
      </databases>
    </sqlCacheDependency>
</caching>

The pollTime attribute specifies how frequently ASP.NET should check the cache table. The unit is milliseconds, so in this case the polling occurs every five seconds. The connectionStringName is obvious, as it refers to the connection string already specified.  The name "pubs" is the key that is referenced in the OutputCache directive discussed below.

Before using dependency caching, create a new Web Form by copying the existing Authors.aspx page and making some slight modifications.

2. In the Solution Explorer, copy and paste Authors.aspx. Rename the resulting "Copy of Authors.aspx" file to "DependencyCaching.aspx". Right-click the new file and select View Code. Change the class name from "Authors" to "DependencyCaching".

3. Open DependencyCaching.aspx. In the Page directive, change the value of the Inherits attribute to "DependencyCaching". After the <div> tag add "<h2>Dependency Caching</h2>", and then change "Current Date/Time" to "Timestamp".

4. Add the following OutputCache directive below the Page directive: <%@ OutputCache Duration="3600" SqlDependency="pubs:authors" VaryByParam="none" %>.

The duration attribute specifies that if the page does not get invalidated by an underlying data change within one hour, it will automatically invalidate itself. The SqlDependency attribute uses a "database name:table name" format that maps to what was specified in the web.config file. The following figure should clarify this relationship:

5. Press F5 to run the application. Use the menu to run your SqlCaching.aspx page.  Refresh it a few times to see that the page is being cached (the date/time will not be changing because it is cached):

6. For any row, click Edit and make a change. Note the timestamp and then click Update. If the timestamp has not changed it means ASP.NET has not polled SQL Server since the update (remember, the polling interval is set to 5 seconds). Wait a few seconds and then refresh the page again to see the new timestamp.

7. Close the browser.