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.
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.
To use the aspnet_regsql.exe utility, go to the command prompt and change to the following directory: C:\WINDOWS\Microsoft.NET\Framework\<version>.
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:
There are two steps you need to take to enable SQL Cache Dependency:
Enable the database.
Enable the table(s).
To enable a database, use the -ed option (see Sidebar 1-1):
version>aspnet_regsql -S localhost -E -d Pubs -ed
To enable the table(s), use the -et option:
version>aspnet_regsql -S localhost -E -t Authors -d Pubs -et
Steps 3 and 4 create a new table in your database:
in Figure 4-40).
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.
SQL Server knows when to increment the
AspNet_SqlCacheTablesForChangeNotification table because a trigger
installed by aspnet_regsql during the enabling
process, and this trigger is invoked whenever the table is modified
(see Figure 4-42).
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.
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.
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>
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.
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>
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.
The default Web Form should now look like Figure 4-43.
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>
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.
...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.
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.