Using SQL Cache Dependencyby Wei-Meng Lee
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:
GridView (see Figure 1).
Figure 1. 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
<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.
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
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
C:\Program Files\Microsoft Visual Studio 8\VC>aspnet_regsql -S localhost -E -d Pubs -ed
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
C:\Program Files\Microsoft Visual Studio 8\VC>aspnet_regsql -S localhost -E -t titles -d Pubs -et
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. The newly created
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. The content of the
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. The trigger installed by
Once the database server is prepared, it is now time to configure the ASP.NET web application for SQL cache dependency.
<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> ...
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,
SqlCacheDependency, to the
<asp:SqlDataSource ID="SqlDataSource1" Runat="server" ConnectionString="<%$ ConnectionStrings:PubsDatabase %>" SelectCommand="SELECT [title_id], [title], [pub_id], [price] FROM [titles]" EnableCaching="True" SqlCacheDependency="Pubs:titles"> ...
SqlCacheDependency attribute indicates the database and table name (separated by a colon) to enable SQL cache dependency.
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. Managing triggers for a table
Create a new trigger called
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. 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
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.
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.
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.
Showing messages 1 through 4 of 4.
Why use polling method if query notifications can be used?
2008-04-02 07:18:29 Skyfish [View]
Polling and Performance
2007-08-30 11:36:17 Skyfish [View]
Cache and Timing
2007-08-29 09:27:03 Skyfish [View]
Notify windows service when Sql table changed???
2006-09-16 02:43:23 maulikCE [View]