4.6. 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. 4.6.1. 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.
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: C:\WINDOWS\Microsoft.NET\Framework\version>aspnet_regsql /?
Parameters of the aspnet_regsql Utility Here is the summary of the options used for the aspnet_regsql utility in this lab:
- -S
-
SQL server instance
- -E
-
Authenticate with current Windows credentials
- -d
-
Database name
- -t
-
Table name
- -ed
-
Enable database
- -et
-
Enable table
|
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): C:\WINDOWS\Microsoft.NET\Framework\version>aspnet_regsql -S localhost -E -d Pubs -ed
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). 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. 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). Figure 4-42. The AspNet_SqlCacheNotification_Trigger trigger installed by aspnet_regsql 
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>
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.
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 runat="server" DataSource AutoGenerateColumns="False" DataKeyNames="au_id"> ... ... </asp:GridView> <asp:SqlDataSource 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.
The default Web Form should now look like Figure 4-43. Figure 4-43. The GridView and SqlDataSource controls 
Switch to Source View and modify the SqlDataSource control to enable SQL Cache Dependency: <asp:SqlDataSource runat="server" EnableCaching="true" SqlCacheDependency="Pubs:Authors" SelectCommand="SELECT * FROM [authors]" ConnectionString="<%$ ConnectionStrings:PubsDatabase %>"> </asp:SqlDataSource>
Level of Caching in SQL Server 7, 2000, and 2005 In SQL Server 7 and 2000, a change in a record in a table invalidates the cache, regardless of the number of pages being cached. In SQL Server 2005, page-level caching is supported, thereby invalidating only the page that is affected by the change. In SQL Server 2005, there is no need for ASP.NET to constantly poll the database. Instead, SQL Server 2005 automatically notifies ASP.NET whenever a particular row of data has been modified. SQL Server 2005 does this by using the Notification Delivery Services, which directly interacts with IIS to notify ASP.NET of updates to specific rows. |
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.
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.
4.6.2. 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. 4.6.3. 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. |
