Wrox Home  
Search
Professional ASP.NET 2.0 Special Edition
by Bill Evjen, Scott Hanselman, Devin Rader, Farhan Muhammad, Srinivasa Sivakumar
October 2006, Hardcover


Excerpt from Professional ASP.NET 2.0 Special Edition

Using the ASP.NET 2.0 SQL Server Cache Dependency

By Scott Hanselman

Performance is a key requirement for any application or piece of code that you develop. The browser helps with client-side caching of text and images, whereas the server-side caching you choose to implement is vital for creating the best possible performance. Caching is the process of storing frequently used data on the server to fulfill subsequent requests. You will discover that grabbing objects from memory is much faster than re-creating the Web pages or items contained in them from scratch each time they are requested. Caching increases your application's performance, scalability, and availability. The more you fine-tune your application's caching approach, the better it performs.

This article focuses on the new SQL invalidation caching capabilities that ASP.NET 2.0 provides. When you are using SQL cache invalidation, if the result set from SQL Server changes, the output cache can be triggered to change. This ensures that the end user always sees the latest result set, and the data presented is never stale. This feature was frequently requested by developers using ASP.NET 1.0/1.1, so the ASP.NET team worked hard to bring it to ASP.NET 2.0.

To utilize the new SQL Server Cache Dependency feature in ASP.NET 2.0, you must perform a one-time setup of your SQL Server database. To set up your SQL Server, use the aspnet_regsql.exe tool found at C:\Windows\Microsoft.NET\Framework\v2.0xxxxx\. This tool makes the necessary modifications to SQL Server so that you can start working with the new SQL cache invalidation features.

Follow these steps when using the new SQL Server Cache Dependency features:

  1. Enable your database for SQL Cache Dependency support.
  2. Enable a table or tables for SQL Cache Dependency support.
  3. Include SQL connection string details in the ASP.NET application's web.config.
  4. Utilize the SQL Cache Dependency features by adding a SqlDependency attribute to an OutputCache directive. Additionally, you have two other options to use this which are not presented here but are in the Chapter 22, "Caching," of the book, Professional ASP.NET 2.0 Special Edition (Wrox, 2006, ISBN: 0470041781). Those two options are:
    • Programmatically creating a SqlCacheDependency object in code.
    • Adding a SqlCacheDependency instance to the Response object via Response.AddCacheDependency.

To start, you need to get at the aspnet_regsql.exe tool. Open up the Visual Studio Command Prompt by choosing Start-->All Programs-->Microsoft Visual Studio 2005-->Visual Studio Tools--> Visual Studio Command Prompt from the Windows Start menu. After the prompt launches, type this command:

aspnet_regsql.exe -?

This code outputs the help command list for this command-line tool, as shown in the following:

                      -- SQL CACHE DEPENDENCY OPTIONS --
-d <database>             Database name for use with SQL cache dependency. The
                          database can optionally be specified using the
                          connection string with the -c option instead.
                          (Required)
-ed                       Enable a database for SQL cache dependency.
-dd                       Disable a database for SQL cache dependency.
-et                       Enable a table for SQL cache dependency. Requires -t
                          option.
-dt                       Disable a table for SQL cache dependency. Requires -t
                          option.
-t <table>                Name of the table to enable or disable for SQL cache
                          dependency. Requires -et or -dt option.
-lt                       List all tables enabled for SQL cache dependency.

The following sections show you how to use some of these commands.

Enabling Databases for SQL Server Cache Invalidation

To use SQL Server cache invalidation with SQL Server 7 or 2000, begin with two steps. The first step enables the appropriate database. In the second step, you enable the tables that you want to work with. You must perform both steps for this process to work. If you want to enable your databases for SQL cache invalidation and you are working on the computer where the SQL Server instance is located, you can use the following construct. If your SQL instance is on another computer, change localhost in this example to the name of the remote machine.

aspnet_regsql.exe -S localhost -U sa -P password -d Northwind -ed

This produces something similar to the following output:

Enabling the database for SQL cache dependency.
..
Finished.

From this command prompt, you can see that we simply enabled the Northwind database (the sample database that comes with SQL Server) for SQL cache invalidation. The name of the SQL machine was passed in with -S, the username with -U, the database with -d, and most importantly, the command to enable SQL cache invalidation was -ed.

Now that you have enabled the database for SQL cache invalidation, you can enable one or more tables contained within the Northwind database.

Enabling Tables for SQL Server Cache Invalidation

You enable or more tables by using the following command:

aspnet_regsql.exe -S localhost -U sa -P password -d Northwind -t Customers -et
aspnet_regsql.exe -S localhost -U sa -P password -d Northwind -t Products -et

You can see that this command is not much different from the one for enabling the database, except for the extra -t Customers entry and the use of -et to enable the table rather than -ed to enable a database. Customers is the name of the table that is enabled in this case.

Go ahead and enable both the Customers and Product tables. You run the command once per table. After a table is successfully enabled, you receive the following response:

Enabling the table for SQL cache dependency.
.
Finished.

After the table is enabled, you can begin using the SQL cache invalidation features. However, before you do, the following section shows you what happens to SQL Server when you enable these features.

Looking at SQL Server

Now that the Northwind database and the Customers and Products tables have all been enabled for SQL cache invalidation, look at what has happened in SQL Server. If you open up the SQL Server Enterprise Manager, you see a new table contained within the Northwind database-AspNet_SqlCacheTablesForChangeNotification (whew, that's a long one!). Your screen should look like Figure 1.


Figure 1

At the top of the list of tables in the right-hand pane, you see the AspNet_SqlCacheTablesForChangeNotification table. This is the table that ASP.NET uses to learn which tables are being monitored for change notification and also to make note of any changes to the tables being monitored. The table is actually quite simple when you look at the details, as illustrated in Figure 2.


Figure 2

In this figure, you can see three columns in this new table. The first is the tableName column. This column simply shows a String reference to the names of the tables contained in the same database. Any table named here is enabled for SQL cache invalidation.

The second column, notificationCreated, shows the date and time when the table was enabled for SQL cache invalidation. The final column, changeId, is used to communicate to ASP.NET any changes to the included tables. ASP.NET monitors this column for changes and, depending on the value, either uses what is stored in memory or makes a new database query.

Looking at the Tables That Are Enabled

Using the aspnet_regsql.exe tool, you can see (by using a simple command) which tables are enabled in a particular database. If you are working through the preceding examples, you see that so far you have enabled the Customers and Products tables of the Northwind database. To get a list of the tables that are enabled, use something similar to the following command:

aspnet_regsql.exe -S localhost -U sa -P password -d Northwind -lt

The -lt command produces a simple list of tables enabled for SQL cache invalidation. Inputting this command produces the following results:

Listing all tables enabled for SQL cache dependency:
Customers
Products

Disabling a Table for SQL Server Cache Invalidation

Now that you know how to enable your SQL Server database for SQL Server cache invalidation, take a look at how you remove the capability for a specific table to be monitored for this process. To remove a table from the SQL Server cache invalidation process, use the -dt command.

In the preceding example, using the -lt command showed that you have both the Customers and Products tables enabled. Next, you remove the Products table from the process using the following command:

aspnet_regsql.exe -S localhost -U sa -P password -d Northwind -t Products -dt

You can see that all you do is specify the name of the table using the -t command followed by a -dt command (disable table). The command line for disabling table caching will again list the tables that are enabled for SQL Server cache invalidation; this time, the Products table is not listed — instead, Customers, the only enabled table, is listed.

Disabling a Database for SQL Server Cache Invalidation

Not only can you pick and choose the tables that you want to remove from the process, but you can also disable the entire database for SQL Server cache invalidation. In order to disable an entire database, you use the -dd command (disable database).

Note that disabling an entire database for SQL Server cache invalidation also means that every single table contained within this database is also disabled.

This example shows the Northwind database being disabled on my computer:

C:\>aspnet_regsql -S localhost -U sa -P wrox -d Northwind -dd
Disabling the database for SQL cache dependency.
..
Finished.

To ensure that the table is no longer enabled for SQL Server cache invalidation, we attempted to list the tables that were enabled for cache invalidation using the -lt command. We received the following error:

C:\ >aspnet_regsql -S localhost -U sa -P wrox -d Northwind -lt
An error has happened. Details of the exception:
The database is not enabled for SQL cache notification. To enable a database for
SQL cache notification, please use SQLCacheDependencyAdmin.EnableNotifications 
method, or the command line tool aspnet_regsql.exe.

If you now open the Northwind database in the SQL Server Enterprise Manager, you can see that the AspNet_SqlCacheTablesForChangeNotification table has been removed for the database.

SQL Server 2005 Cache Invalidation

As you've seen, standard SQL Server 2000 cache invalidation uses a table-level mechanism using a polling model every few seconds to monitor what tables have changed.

SQL Server 2005 supports a different, more granular series of notification that doesn't require polling. Direct notification of changes are a built-in feature of SQL Server 2005 and are presented via the ADO.NET SqlCommand. For example:

Protected Sub Page_Load(ByVal sender as Object, ByVal e as System.EventArgs)
    Response.Write("Page created: " + DateTime.Now.ToLongTimeString())
        Dim connStr As String =
ConfigurationManager.ConnectionStrings("AppConnectionString1").ConnectionString
    SqlDependency.Start(connStr)
    Dim connection As New SqlConnection(connStr)    
    Dim command as New SqlCommand("Select * FROM Customers", connection)
    Dim depends as New SqlCacheDependency(command)
    Connection.Open
    GridView1.DataSource = command.ExecuteReader()
    GridView1.DataBind()
    Connection.Close
     'Now, do what you want with the sqlDependency object like:
    Response.AddCacheDependency(depends)
End Sub

SQL Server 2005 supports both programmatic and declarative techniques when caching. Use the string "CommandNotification" in the OutputCache directive to enable notification-based caching for a page as in this example. You can specify SQL caching options programmatically or declaratively, but not both. Note that you must first call System.Data.SqlClient.SqlDependency.Start, passing in the connection string, to start the SQL notification engine.

<%@ OutputCache Duration="3600" VaryByParam="none" 
    SqlDependency="CommandNotification"%>

Or, if you're using a SqlDataSource control from within your ASP.NET page:

<asp:SqlDataSource EnableCaching="true" SqlCacheDependency="CommandNotification"
    CacheDuration="2600" />

As data changes within SQL Server 2005, SQL and ADO.NET automatically invalidate data cached on the Web server.

Configuring Your ASP.NET Application

After you enable a database for SQL Server cache invalidation and also enable a couple of tables within this database, the next step is to configure your application for SQL Server cache invalidation.

To configure your application to work with SQL Server cache invalidation, the first step is to make some changes to the web.config file. In the web.config file, specify that you want to work with the Northwind database, and you want ASP.NET connected to it.

Listing 1 shows an example of how you should change your web.config file to work with SQL Server cache invalidation. The pollTime attribute isn't needed if you're using SQL Server 2005 notification.

Listing 1: Configuring the web.config file

<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
   <connectionStrings>
      <add name="AppConnectionString1" connectionString="Data Source=localhost; 
        User ID=sa;Password=wrox;Database=Northwind;Persist Security Info=False"
        providerName="System.Data.SqlClient" />
   </connectionStrings>
   <system.web>
      <caching>
         <sqlCacheDependency enabled="true">
            <databases>
               <add name="Northwind" connectionStringName="AppConnectionString1" 
                pollTime="500" />
            </databases>
         </sqlCacheDependency>
      </caching>
 </system.web>
</configuration>

From this listing, you can see that the first thing established is the connection string to the Northwind database using the <connectionStrings> element in the web.config file. Note the name of the connection string because it is utilized later in the configuration settings for SQL Server cache invalidation.

The SQL Server cache invalidation is configured using the new <caching> element. This element must be nested within the <system.web> elements. Because you are working with a SQL Server cache dependency, you must use a <sqlCacheDependency> child node. You enable the entire process by using the enabled="true" attribute. After this attribute is enabled, you work with the <databases> section. You use the <add> element, nested within the <databases> nodes, to reference the Northwind database. The following table explains all the attributes of the <add> element.

Attribute Description
Name The name attribute provides an identifier to the SQL Server database.
connectionStringName The connectionStringName attribute specifies the name of the connection. Because the connection string in the preceding example is called AppConnectionString1, you use this value for the connectionStringName attribute as well.
pollTime The pollTime attribute specifies the time interval from one SQL Server poll to the next. The default is .5 seconds or 500 milliseconds (as shown in the example). This is not needed for SQL Server 2005 notification.

Now that the web.config file is set up correctly, you can start using SQL Server cache invalidation on your pages. ASP.NET makes a separate SQL Server request on a completely different thread to the AspNet_SqlCacheTablesForChangeNotification table to see if the changeId number has been incremented. If the number is changed, ASP.NET knows that an underlying change has been made to the SQL Server table and that a new result set should be retrieved. When it checks to see if it should make a SQL Server call, the request to the small AspNet_SqlCacheTablesForChangeNotification table has a single result. With SQL Server cache invalidation enabled, this is done so quickly that you really notice the difference.

Testing SQL Server Cache Invalidation

Now that the web.config file is set up and ready to go, the next step is to actually apply these new capabilities to a page. For an example of a page using the new SQL Server cache invalidation process, look at Listing 2.

Listing 2: An ASP.NET page utilizing SQL Server cache invalidation

VB
<%@ Page Language="VB" %>
<%@ OutputCache Duration="3600" VaryByParam="none" 
    SqlDependency="Northwind:Customers"%>
<script runat="server">
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
        Label1.Text = "Page created at " & DateTime.Now.ToShortTimeString ()
    End Sub
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Sql Cache Invalidation</title>
</head>
<body>
    <form id="form1" runat="server">
        <asp:Label ID="Label1" Runat="server"></asp:Label><br />
        <br />
        <asp:GridView ID="GridView1" Runat="server" DataSourceID="SqlDataSource1">
        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource1" Runat="server" 
         SelectCommand="Select * From Customers"
         ConnectionString="<%$ ConnectionStrings:AppConnectionString1 %>" 
         ProviderName="<%$ ConnectionStrings:AppConnectionString1.providername %>">
        </asp:SqlDataSource>
    </form>
</body>
</html>

C#
<%@ Page Language="C#" %>
<%@ OutputCache Duration="3600" VaryByParam="none" 
    SqlDependency="Northwind:Customers"%>
<script runat="server">
    protected void Page_Load(object sender, System.EventArgs e)
    {
        Label1.Text = "Page created at " + DateTime.Now.ToShortTimeString();
    }
</script>

The first and most important part of this page is the OuputCache page directive that is specified at the top of the file. Typically, the OuputCache directive specifies how long the page output is held in the cache using the Duration attribute. Next is the VaryByParam attribute. The new addition is the SqlDependency attribute. This enables a particular page to use SQL Server cache invalidation. The following line shows the format of the value for the SqlDependency attribute:

SqlDependency="<i>database:table</i>"

The value of Northwind:Customers specifies that you want the SQL Server cache invalidation enabled for the Customers table within the Northwind database. The Duration attribute of the OuputCache directive shows you that, typically, the output of this page is stored in the cache for a long time-but this cache is invalidated if the Customers table has any underlying changes made to the data that it contains.

A change to any of the cells in the Customers table of the Northwind database invalidates the cache, and a new cache is generated from the result, which now contains a new SQL Server database request. Figure 3 shows an example of the page generated the first time it is run.


Figure 3

From this figure, you can see the contents of the customer with the CustomerID of ALFKI. For this entry, go to SQL Server and change the value of the ContactName from Maria Anders to Mary Anders. Before SQL Server cache invalidation, this change would have done nothing to the output cache. The original page output in the cache would still be present and the end user would still see the Maria Anders entry for the duration specified in the page's OuputCache directive. Because of SQL Server cache invalidation, after the underlying information in the table is changed, the output cache is invalidated, a new result set is retrieved, and the new result set is cached. When a change has been made, you see the results as shown in Figure 4.


Figure 4

Notice also that the text "Page created at" includes an updated time indicating when this page was rendered. Need to stop working so late, eh?

Adding More Than One Table to a Page

The preceding example shows how to use SQL Server cache invalidation for a single table on the ASP.NET page. What do you do if your page is working with two or more tables?

To add more than one table, you use the OuputCache directive shown here:

SqlDependency="database:table;database:table"

From this example, you can see that the value of the SqlDependency attribute separates the databases and tables with a semicolon. If you want to work with both the Customers table and the Products table of the Northwind database, you construct the value of the SqlDependency attribute as follows:

SqlDependency="Northwind:Customers;Northwind:Products"

Scott Hanselman is a co-author of Professional ASP.NET 2.0 Special Edition (Wrox, 2006, ISBN: 0470041781), as well as the best-selling predecessor to that title, Professional ASP.NET 2.0 (Wrox, 2005, ISBN: 0764576100). This article is excerpted from Chapter 22, "Caching," from Professional ASP.NET 2.0 Special Edition. Prior to his recent move to Microsoft, Scott was the chief architect at the Corillian Corporation, an eFinance enabler and he was both a Microsoft RD (regional director) as well as an MVP for both ASP.NET and Solutions architecture. He has more than 14 years of experience developing software in C, C++, VB, COM, and certainly VB.NET and C#. His Computer Zen blog is one of the more popular developer blogs on the Web. Scott's other articles featured on Wrox.com are Generating Custom XML from SQL 2005, also from Professional ASP.NET 2.0 Special Edition and Two ASP.NET HttpHandler Image Hacks, excerpted from his contribution to the book, ASP.NET 2.0 MVP Hacks and Tips (Wrox, 2006, ISBN 0764597663). Scott is also featured on Wrox.com in a series of 4 developer videos on the topic of developer productivity.