O'Reilly logo

ASP.NET 2.0: A Developer's Notebook by Wei-Meng Lee

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Cache Using Dependency

Note

Instead of just invalidating a database cache based on a specific duration, it is much more effective to invalidate a cache if a change is detected in the database.

Specifying duration for a cache is not always a practical approach. For example, you may cache the data from a database for 10 minutes, but due to unforeseen circumstances (such as price fluctuations of stocks reacting to sudden happenings in world events) the data in the database may be updated more than once during the 10-minute interval. In such cases, it would be much better if the cache duration is dependent on the changes of data in the database.

In ASP.NET 2.0, a new SQL Cache Dependency is available that allows you to invalidate your cache whenever certain types of changes are made to the database. You can now be assured that your database cache is always up to date whenever your database changes.

How do I do that?

To use SQL Cache Dependency, you first need to prepare your database (SQL Server 2000 in this example) and its tables for change notification (so that your ASP.NET application can be notified of the changes in the database). You do this by running a utility called aspnet_regsql.exe on the databases and tables that you want to enable for SQL Cache Dependency. Once the database is ready, you will then build an application and use the GridView and SqlDataSource controls to test out SQL Cache Dependency.

Tip

You need not perform Steps 1-4 if you are using SQL Server 2005.

  1. To use the aspnet_regsql.exe utility, go to the command prompt and change to the following directory: C:\WINDOWS\Microsoft.NET\Framework\<version>.

  2. The aspnet_regsql.exe utility is a mixed-mode (both graphical and command-based) tool that helps you configure your SQL Server database for use with your ASP.NET application. To see the various options available with it, use:

    C:\WINDOWS\Microsoft.NET\Framework\version>aspnet_regsql /?
  3. There are two steps you need to take to enable SQL Cache Dependency:

    1. Enable the database.

    2. Enable the table(s).

  4. To enable a database, use the -ed option (see Sidebar 1-1):

    C:\WINDOWS\Microsoft.NET\Framework\version>aspnet_regsql
       -S localhost -E -d Pubs -ed
  5. To enable the table(s), use the -et option:

    C:\WINDOWS\Microsoft.NET\Framework\version>aspnet_regsql
       -S localhost -E -t Authors -d Pubs -et

    Steps 3 and 4 create a new table in your database: AspNet_SqlCacheTablesForChangeNotification (shown in Figure 4-40).

    The newly created AspNet_SqlCacheTablesForChangeNotification table

    Figure 4-40. The newly created AspNet_SqlCacheTablesForChangeNotification table

    The AspNet_SqlCacheTablesForChangeNotification table has three fields, shown in Figure 4-41, and contains the last modification date of the table you are monitoring (through the notificationCreated field), as well as the total number of changes. Each time your table is modified, the value in the changeId field is incremented; it is this table that is tracked by ASP.NET for SQL Cache Dependency.

    The content of the AspNet_SqlCacheTablesForChangeNotification table

    Figure 4-41. The content of the AspNet_SqlCacheTablesForChangeNotification table

    SQL Server knows when to increment the AspNet_SqlCacheTablesForChangeNotification table because a trigger called AspNet_SqlCacheNotification_Trigger was installed by aspnet_regsql during the enabling process, and this trigger is invoked whenever the table is modified (see Figure 4-42).

    The AspNet_SqlCacheNotification_Trigger trigger installed by aspnet_regsql

    Figure 4-42. The AspNet_SqlCacheNotification_Trigger trigger installed by aspnet_regsql

  6. Now that the database has been enabled, to test SQL Cache Dependency, launch Visual Studio 2005 and create a new web site project. Name the project C:\ASPNET20\Chap04-SQLCacheDep.

  7. Add the Web.config file to the project by right-clicking the project name in Solution Explorer and then selecting Add New Item.... Select Web Configuration File.

  8. The next step is to add a database connection string in your application. To do so, you need to first modify the Web.config file (note the added code in bold in Example 4-5).

    Example 4-5. Modifying Web.config to support SQL Cache Dependency

    <?xml version="1.0"?>
    <configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
       <appSettings/>
       <connectionStrings>
         <add name="PubsDatabase"
            connectionString="Server=(local);Integrated Security=True;
            Database=pubs;Persist Security Info=True"
            providerName="System.Data.SqlClient" />
       </connectionStrings>
    
       <system.web>
         <compilation debug="true"/>
         <authentication mode="Windows"/>
         <caching>
            <sqlCacheDependency enabled="true">
               <databases>
                  <add
                     name="Pubs" 
                     connectionStringName="PubsDatabase"
                     pollTime="10000" />
               </databases>
            </sqlCacheDependency>
         </caching>        
       </system.web>
    </configuration>

    Tip

    If you are using SQL Server 2005, you do not need to add in the <caching> element (and its child elements) in Web.config.

  9. In the Web.config file in Example 4-5, you have specified a connection string that connects to the pubs database. You have also specified the use of SQL Server caching and indicated a polling time of 10 seconds (unit is in milliseconds). This is the time the ASP.NET runtime (a background thread that is spun off for this purpose) polls the database (specifically, the AspNet_SqlCacheTablesForChangeNotification table) for changes. As this table is small, this process is very efficient and will not slow down the system. Hence, it would be good to specify a low number so that the application is always displaying the most up to date data.

  10. Populate the default Web Form with a GridView control. Configure the GridView control to use a SqlDataSource control. In particular, the SqlDataSource control will use the connection string stored in Web.config. The Source View of the GridView should now look something like this:

    <asp:GridView ID="GridView1" runat="server" 
         DataSourceID="SqlDataSource1" 
         AutoGenerateColumns="False"
         DataKeyNames="au_id">
         ...
         ...
    </asp:GridView>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server"  
         SelectCommand="SELECT * FROM [authors]"
         ConnectionString="<%$ ConnectionStrings:PubsDatabase %>">
    </asp:SqlDataSource>

    Tip

    Refer to the lab Section 4.1, earlier in this chapter, if you are not sure how to configure the GridView control to use a SqlDataSource control.

  11. The default Web Form should now look like Figure 4-43.

    The GridView and SqlDataSource controls

    Figure 4-43. The GridView and SqlDataSource controls

  12. Switch to Source View and modify the SqlDataSource control to enable SQL Cache Dependency:

    <asp:SqlDataSource ID="SqlDataSource1" runat="server"  
       EnableCaching="true" SqlCacheDependency="Pubs:Authors" 
       SelectCommand="SELECT * FROM [authors]"
       ConnectionString="<%$ ConnectionStrings:PubsDatabase %>">
    </asp:SqlDataSource>

    Tip

    The SqlCacheDependency attribute has the format of database:table for SQL Server 2000 polling. If you are using SQL Server 2005, the SqlCacheDependency attribute should be set to CommandNotification.

  13. Press F5 to test the application. Modify the data in the authors table (using tools such as SQL Enterprise Manager or Query Analyzer), and refresh the web browser. You will notice that the data in the GridView will be updated every 10 seconds (approximately) only if there are changes in the authors table.

What about...

...page output caching?

You can use page output caching together with SQL Cache Dependency. For example, suppose a Web Form has a GridView control bound to a SqlDataSource control. You can specify output caching by using the OutputCache directive:

<%@ OutputCache  Duration="15000"  VaryByParam="none" 
    SqlDependency ="Pubs:Authors" %>

Assuming that the SQL Cache Dependency is set to 10 seconds, the GridView will be refreshed every 10 seconds (if there are changes). Alternatively, the page will expire every 15 seconds, and any changes would also be reflected when the page gets refreshed every 15 seconds.

...programmatically enabling a database and its tables for SQL Cache Dependency?

You have seen how to configure SQL Cache Dependency using the aspnet_regsql.exe utility. Besides using the tool, you can programmatically enable a database and table(s) for notification by using the SqlCacheDependencyAdmin class.

The code in Example 4-6 shows how to enable a database and its tables for notifications.

Example 4-6. Enabling a database and its tables for notifications

Public Sub EnableNotification(ByVal tableName As String)
    Dim connStr As String = "Server=.\SQLEXPRESS;Integrated 
        Security=True;Database=pubs;Persist Security 
        Info=True"
    Dim mustEnable As Boolean = True
    Try
        Dim tablesEnabled( ) As String
        '---Retrieve all tables enabled for notifications--
        tablesEnabled = _
           SqlCacheDependencyAdmin. _
            GetTablesEnabledForNotifications(connStr)
        If (tablesEnabled IsNot Nothing) Then
            Dim table As String
            Response.Write("<b>Tables Enabled For " & _
                           "Notification</b><br/>")
            For Each table In tablesEnabled
                Response.Write(table & "<br>")
                If (table.ToString.Equals(tableName)) Then
                    mustEnable = False
                End If
            Next
        End If
    Catch ex As Exception
        mustEnable = True
    End Try
    If mustEnable Then
        '--enables the database for notification
       SqlCacheDependencyAdmin.EnableNotifications(connStr)
        '--enables the table for notification
        SqlCacheDependencyAdmin. _
           EnableTableForNotifications(connStr, tableName)
        Response.Write(tableName & "<br>")
    End If
End Sub

The SqlCacheDependencyAdmin class performs administrative tasks on a SQL Server so that you can enable SQL Cache Dependency. The GetTablesEnabledForNotifications( ) method retrieves all the tables already enabled for notification in the database (as indicated in the connection string) and returns the table names as an array of strings. You then loop through the array of table names to see if the table that you need to enable for notification is found. If the name is in the list, that means the table is already enabled; otherwise, you need to enable it for notifications.

You need to first enable a database and then enable the table for SQL Cache Dependency to work. So, use the EnableNotifications( ) method to enable the database first, and then use the EnableTableForNotifications( ) method to enable the individual tables.

Where can I learn more?

To see how you can implement your own SQL Cache Dependency in .NET 1.1 applications, check out my O'Reilly Network article at http://www.ondotnet.com/pub/a/dotnet/2005/01/17/sqlcachedependency.html.

To learn how you can use caching in ASP.NET 1.x and 2.0 applications, check out my DevX.com article at http://www.devx.com/asp/Article/21751.

For a good discussion on the differences in how SQL Server 2000 and SQL Server 2005 handle SQL Cache Dependency, check out this link: http://beta.asp.net/GuidedTour/s20.aspx.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required