February 20, 2019
Hot Topics:

ASP.NET 2.0's New and Improved Caching Features

  • October 27, 2004
  • By Thiru Thangarathinam
  • Send Email »
  • More Articles »

SQL Cache Invalidation in a SqlDataSource Control

So far, you have seen how to implement the cache invalidation based on the time duration set in the CacheDuration property of the SqlDataSource control. This section explains the steps involved in implementing a cache invalidation mechanism based on data in SQL Server tables. This cache invalidation mechanism is a new feature in ASP.NET 2.0 wherein a cached page or a data source control can be tied to a particular table in a SQL Server database. Once you perform this initial association, changing the contents of the table results in the cached object being automatically invalidated. You should consider using SQL cache invalidation with the data source controls when you need to work with the same database data in multiple pages.

The SQL Server-based cache invalidation mechanism works with SQL Server 7.0 and above. However, SQL Server 7.0 and 2000 support only the table-level cache invalidation mechanism. This means that the cached items are automatically invalidated any time data in the table changes. The next release of SQL Server (SQL Server 2005) will also feature a row-level cache invalidation mechanism that provides a finer level of accuracy over the cached data.

In SQL Server 7 and SQL Server 2000, table-level cache invalidation is supported using a polling system. The ASP.NET process polls the database (pull model) every so many seconds to monitor which tables have changed since it last checked. Even though the pull model works for most cases, it is not an efficient approach. However, this will be enhanced in SQL Server 2005 to have SQL Server actually notify (Push model) ASP.NET whenever a particular row of data has been modified. SQL Server 2005 accomplishes this by using a feature named Notification Delivery Services (that uses port 80), which directly interacts with HTTP.SYS of IIS 6.0 to notify the Web server of updates to specific rows. This article considers SQL Server 7 and 2000 and explains how to configure caching for those versions.

Before you can establish cache dependency with SQL Server 7 or SQL Server 2000, you need to perform the following steps:

  • Configure SQL Server to support SQL Cache invalidation. This is a one-time setup of the tables or databases in the SQL Server database that you want to monitor.
  • Add the necessary configuration information to the web.config file.

Let's walk through the above steps in detail, starting with a look at the configuration of SQL Server.

Configuration of SQL Server to Support SQL Cache Invalidation

You can perform the configuration of SQL Server 2000 to support SQL Cache invalidation in two ways:

  1. Using the aspnet_regsql utility
  2. Using the EnableTableForNotifications method of the SqlCacheDependencyAdmin class

For this article, consider the first method. Basically, the aspnet_regsql utility creates an extra table named AspNet_SqlCacheTablesForChangeNotification that is used to keep track of the changes to all the monitored tables in the database. It also creates a number of triggers and stored procedures to enable this capability. To run the aspnet_regsql utility, open up the Visual Studio command prompt and enter the command shown in the following screenshot.

Click here for a larger image.

The command enables the Pubs database to support SQL cache invalidation:

  • S—Name of the Server
  • U—User ID to use to connect to the SQL Server
  • P—Password to use to connect to the SQL Server
  • d—The name of the database
  • ed—Enables the database for SQL Server-triggered cache invalidation

Once this is done at the database level, you need to enable cache invalidation at the individual table level. This is shown in the following screenshot.

Click here for a larger image.

In the above command:

  • t—Specifies the name of the table
  • et—Enables the table for SQL Server-triggered cache invalidation

The preceding screenshot shows how to enable SQL cache invalidation for the authors table in the Pubs database. Once you configure the authors table to send notifications, any time data in the table changes, it notifies ASP.NET to invalidate the corresponding item in the cache.

Web Configuration Settings for SQL Cache Invalidation

The next step, before you can use SQL cache invalidation in your ASP.NET application, is to update the Web configuration file. You need to instruct the ASP.NET framework to poll the databases that you have enabled for SQL cache invalidation. The following Web configuration file contains the necessary configuration information to poll the Pubs database at periodic intervals:

    <add name="Pubs"
                           pwd=thiru" />
       <sqlCacheDependency enabled="true">
          <add name="Pubs"
             pollTime="60000" />

The preceding Web configuration file contains two sections, <connectionStrings> and <caching>. The connectionStrings section creates a database connection string to the Pubs database named "Pubs". The caching section configures the SQL cache invalidation polling. Within the databases subsection, you list one or more databases that you want to poll for changes. The add section inside the databases section indicates that the database represented by "Pubs" is polled once a minute (every 60,000 milliseconds). You can specify different polling intervals for different databases. Remember, the server must do a little bit of work every time the database is polled for changes. If you don't expect the data in the database to change very often, you should increase the polling interval.

Implementing SQL Cache Invalidation in a SqlDataSource Control

Now that you have performed the required configurations, you are ready to take advantage of the SQL cache invalidation feature in your ASP.NET Web page. Add a new Web form named SqlCacheInvalidation.aspx to your Web site. The following is the code for the SqlCacheInvalidation.aspx:

<%@ Page Language="C#" %>
    <title>SQL Cache Invalidation</title>
<form id="form1" runat="server">
    <asp:GridView ID="GridView1" Runat="server"
DataSourceID="SqlDataSource1" DataKeyNames="au_id"
          <asp:CommandField ShowEditButton="True" />
          <asp:BoundField ReadOnly="True" HeaderText="timestamp"
            DataField="timestamp" SortExpression="timestamp" />
          <asp:BoundField ReadOnly="True" HeaderText="au_id" 
            DataField="au_id" SortExpression="au_id" />
          <asp:BoundField HeaderText="au_lname" DataField="au_lname"
            SortExpression="au_lname" />
          <asp:BoundField HeaderText="au_fname" DataField="au_fname"
            SortExpression="au_fname" />
          <asp:BoundField HeaderText="phone" DataField="phone"
            SortExpression="phone" />
          <asp:BoundField HeaderText="address" DataField="address"
            SortExpression="address" />
          <asp:BoundField HeaderText="city" DataField="city"
            SortExpression="city" />
          <asp:BoundField HeaderText="state" DataField="state"
            SortExpression="state" />
          <asp:BoundField HeaderText="zip" DataField="zip"
            SortExpression="zip" />
          <asp:CheckBoxField HeaderText="contract"
            DataField="contract" />
    <asp:SqlDataSource ID="SqlDataSource1" Runat="server"
      SelectCommand="SELECT DatePart(second, GetDate())
      As timestamp, * FROM [authors]" ConnectionString="<%$
      ConnectionStrings:Pubs %>" EnableCaching="True"
      DeleteCommand="DELETE FROM [authors]
      WHERE [au_id] = @original_au_id"
      InsertCommand="INSERT INTO [authors] ([au_id], [au_lname],
      [au_fname], [phone], [address], [city], [state], [zip],
      [contract]) VALUES (@au_id, @au_lname, @au_fname, @phone,
      @address, @city, @state, @zip, @contract)"
      UpdateCommand="UPDATE [authors] SET [au_lname] = @au_lname,
      [au_fname] = @au_fname, [phone] = @phone,
      [address] = @address, [city] = @city, [state] = @state,
      [zip] = @zip, [contract] = @contract
      WHERE [au_id] = @original_au_id"
        <asp:Parameter Name="original_au_id" />
        <asp:Parameter Type="String" Name="au_lname" />
        <asp:Parameter Type="String" Name="au_fname" />
        <asp:Parameter Type="String" Name="phone" />
        <asp:Parameter Type="String" Name="address" />
        <asp:Parameter Type="String" Name="city" />
        <asp:Parameter Type="String" Name="state" />
        <asp:Parameter Type="String" Name="zip" />
        <asp:Parameter Type="Boolean" Name="contract" />
        <asp:Parameter Name="original_au_id" />
        <asp:Parameter Type="String" Name="au_id" />
        <asp:Parameter Type="String" Name="au_lname" />
        <asp:Parameter Type="String" Name="au_fname" />
        <asp:Parameter Type="String" Name="phone" />
        <asp:Parameter Type="String" Name="address" />
        <asp:Parameter Type="String" Name="city" />
        <asp:Parameter Type="String" Name="state" />
        <asp:Parameter Type="String" Name="zip" />
        <asp:Parameter Type="Boolean" Name="contract" />

The preceding code uses the SQL cache invalidation with the SqlDataSource control. As you can see, the SqlDataSource control contains both EnableCaching and SqlCacheDependency attributes. The SqlCacheDependency property uses the following syntax:


Page 2 of 3

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date