ASP.NET 2.0: A Developeramp;#039;s Notebook [Electronic resources] نسخه متنی

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 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.

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 ID="SqlDataSource1" 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/sqlcachedependencyl.

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.