WindowsDevCenter.com
oreilly.comSafari Books Online.Conferences.

advertisement


AddThis Social Bookmark Button

Using SQL Cache Dependency

by Wei-Meng Lee
01/17/2005

Caching has long been recognized as one of the more effective ways to improve your ASP.NET web applications. However, one particular caching feature that was dearly missing in ASP.NET 1.x was SQL cache dependency. SQL cache dependency is the ability to invalidate a database cache if data in a table is changed. In ASP.NET 2.0, Microsoft has, much to the joy of developers, added the new SQL cache dependency feature. In this article, I will discuss SQL cache dependency in ASP.NET 2.0, as well as discuss how you can manually implement your own SQL cache dependency in ASP.NET 1.x.

SQL Cache Dependency in ASP.NET 2.0

To illustrate SQL cache dependency in ASP.NET 2.0, let's first create a new web application. I have populated the default.aspx web form with two controls: SqlDataSource and GridView (see Figure 1).

Figure 1
Figure 1. The SqlDataSource and GridView controls

The SqlDataSource control is connected to the titles table of the Pubs database on a SQL Server 2000 database. The GridView control, in turn, is bound to the SqlDataSource control:


<asp:SqlDataSource ID="SqlDataSource1" Runat="server" 
     ConnectionString="<%$ ConnectionStrings:PubsDatabase %>" 
     SelectCommand="SELECT [title_id], [title], [pub_id], [price] 
                    FROM [titles]" >
</asp:SqlDataSource><br />

<asp:GridView ID="GridView1" Runat="server" 
     DataSourceID="SqlDataSource1" DataKeyNames="title_id" 
     AutoGenerateColumns="False" BorderWidth="1px" BackColor="White" 
     ...
</asp:GridView>

To use SQL cache dependency, you first need to prepare the database server. There are two steps needed to enable SQL cache dependency:

  • Enable the database for change notifications.
  • Enable the table(s) for change notifications.

You can perform the above two tasks by using the included aspnet_regsql.exe utility. To run the utility, go to Start -> Programs -> Visual Studio 2005 Beta -> Visual Studio Tools -> Visual Studio .NET Whidbey Command Prompt.

The aspnet_regsql.exe program is a mixed mode (both graphical and command-line-based) tool that lets you configure SQL Server for use with your ASP.NET application. To see the various options available, use the /? option:


C:\Program Files\Microsoft Visual Studio 8\VC>aspnet_regsql /?

To enable a database for change notifications, use the -ed option (see Figure 2). In this case, I am enabling the Pubs database:


C:\Program Files\Microsoft Visual Studio 8\VC>aspnet_regsql -S localhost 
     -E -d Pubs -ed

Figure 2
Figure 2. Enabling the Pubs database for change notifications

To enable the tables, use the -et option (see Figure 3). In this case, I am enabling the titles table in the Pubs database:


C:\Program Files\Microsoft Visual Studio 8\VC>aspnet_regsql -S localhost 
     -E -t titles -d Pubs -et

Figure 3
Figure 3. Enabling the titles table in the Pubs database for change notifications

Essentially, the above two steps create a new table in your database: AspNet_SqlCacheTablesForChangeNotification (see Figure 4).

Figure 4
Figure 4. The newly created AspNet_SqlCacheTablesForChangeNotification table

The AspNet_SqlCacheTablesForChangeNotification table has three fields (see Figure 5) and contains the last modification date of the table you are monitoring, 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.

Figure 5
Figure 5. The content of the AspNet_SqlCacheTablesForChangeNotification table

How does SQL Server know when to increment the AspNet_SqlCacheTablesForChangeNotification table? The answer lies in the fact that a trigger called AspNet_SqlCacheNotification_Trigger was installed by aspnet_regsql.exe during the enabling process, and is invoked whenever the table is modified (see Figure 6).

Figure 6
Figure 6. The trigger installed by aspnet_regsql.exe

Once the database server is prepared, it is now time to configure the ASP.NET web application for SQL cache dependency.

Add the <caching> element (and its child elements) to Web.config:


...
    <connectionStrings>
       <add name="PubsDatabase" 
            connectionString="Server=(local);Integrated 
            Security=True;Database=pubs;Persist Security Info=True" 
            providerName="System.Data.SqlClient"/>
    </connectionStrings>

    <system.web>
       <caching>
         <sqlCacheDependency enabled="true">
             <databases>
                <add name="Pubs" 
                     connectionStringName="PubsDatabase" 
                     pollTime="5000" />
             </databases>
        </sqlCacheDependency>
      </caching>              
<system.web>
...

The pollTime attribute specifies the frequency at which the ASP.NET runtime will poll the database server for changes in the AspNet_SqlCacheTablesForChangeNotification table. The unit is in milliseconds. As the polling does not take up many resources, you should preferably set this to a small value if you need your web application to be always up to date.

Lastly, remember to add the two attributes, EnableCaching and SqlCacheDependency, to the SqlDataSource control.


<asp:SqlDataSource ID="SqlDataSource1" Runat="server" 
     ConnectionString="<%$ ConnectionStrings:PubsDatabase %>" 
     SelectCommand="SELECT [title_id], [title], [pub_id], [price] FROM 
                   [titles]" 
     EnableCaching="True" 
     SqlCacheDependency="Pubs:titles">
...

The SqlCacheDependency attribute indicates the database and table name (separated by a colon) to enable SQL cache dependency.

Press F5 to run the application and make changes to the titles table and then refresh your web browser. You will realize that the content of the GridView control will be updated every five seconds.

SQL Cache Dependency in ASP.NET 1.1

While ASP.NET 2.0 supports the new SQL cache dependency, it is not available in ASP.NET 1.x. In ASP.NET 1.x, you need to write some code to achieve database cache invalidation.

Suppose I have an ASP.NET 1.1 web form containing a DataGrid control. I will databind this DataGrid control to a dataset, which is populated from a table in SQL Server. To improve the performance of this page, I will cache the dataset using the Cache object. When the table in the database server changes, I will invalidate this cache and update the dataset again.

In SQL Server 2000, right-click on the table that you want to use. In my case, I have chosen the authors table. Select Manage Triggers... (see Figure 7).

Figure 7
Figure 7. Managing triggers for a table

Create a new trigger called UpdateCacheDependencyFile:


CREATE TRIGGER UpdateCacheDependencyFile ON [dbo].[Authors]
FOR INSERT, UPDATE, DELETE
AS
EXEC sp_makewebtask @outputfile = 'C:\cache.txt', 
@query ='SELECT TOP 1 au_fname FROM Authors'

Click OK (see Figure 8).

Figure 8
Figure 8. Adding a new trigger

This trigger will be fired whenever records in the authors table are inserted, updated, or deleted. I am making use of the built-in stored procedure sp_makewebtask to create a web page whenever this trigger is fired. The stored procedure will create the file in the specified directory (c:\cache.txt). The content of the file is indicated by the @query parameter.

My intention for creating this file is to use file cache dependency to invalidate my Cache object whenever the table is modified (since the file would be created every time the table is modified and hence would have a new modification date). The content of the file is not important; as long as the query is a valid T-SQL statement, the stored procedure will not complain.

In the Page_Load event of my web form, I used the Cache object to store the dataset retrieved from the table. I have also used a CacheDependency object to invalidate the cache whenever the file c:\cache.txt is modified:


Private Sub Page_Load(ByVal sender As System.Object, _
                      ByVal e As System.EventArgs) _
                      Handles MyBase.Load

    Dim ds As New DataSet
    If Cache("ds") Is Nothing Then
        '---loading for the first time
        Dim sql As String = "SELECT * FROM authors"
        Dim conn As New SqlConnection( _
            "Server=(local);Integrated Security=True;" & _
            "Database=pubs;Persist Security Info=True")
        Dim comm As New SqlCommand(sql, conn)
        Dim dataAdapter As New SqlDataAdapter(comm)
        dataAdapter.Fill(ds, "Authors")

        Dim depends As New _
            System.Web.Caching.CacheDependency _
            ("c:\cache.txt")
        Cache.Insert("ds", ds, depends)
    Else
        '---retrieve from cache
        ds = CType(Cache("ds"), DataSet)
    End If

    DataGrid1.DataSource = ds
    DataGrid1.DataBind()

End Sub

That's it! To test the application, press F5 to view the records displayed in the DataGrid control. Make some changes to a particular row in the authors table and refresh the web browser. You will see the changes updated immediately in the browser.

Summary

SQL cache dependency is a very useful caching strategy, especially if your web application accesses the database frequently. While you are still waiting for ASP.NET 2.0 to be released, you can now improvise SQL cache dependency by writing some triggers and code for your ASP.NET 1.x applications.

Wei-Meng Lee (Microsoft MVP) http://weimenglee.blogspot.com is a technologist and founder of Developer Learning Solutions http://www.developerlearningsolutions.com, a technology company specializing in hands-on training on the latest Microsoft technologies.


Return to ONDotnet.com.