Caching and SQL Server Invalidation

The DataSource controls described in Chapter 3, which covers the new and enhanced data access, offer the capability for integrated caching (except for the SiteMapDataSource control). The approach of these controls corresponds in principle to the ones you already know from versions 1.0 and 1.1. The data is held in memory either absolutely or sliding, depending on a specific time value. This is a possible approach, but it doesn't correspond with the practical requirements.

The new SQL Server Cache Invalidation feature will close the gap. Now you can have the caching depend on changes in an entire database table or even a single line. The latter approach will work only in combination with Yukon notifications (Yukon is the code name for the successor of SQL Server 2000, currently in Beta as well). Table-based invalidation works as well with SQL Server 2000 and even 7.0. Here, the table is polled internally with a configurable time span (standard is 60000 and minimum is 500 milliseconds) and is tested for changes.

Configuring SQL Server Cache Invalidation

To use SQL Server Cache Invalidation you must configure the monitored database first. This is done by the program aspnet_regsqlcache, which you'll find in the following directory:

 <windir>\Microsoft.NET\Framework\<version>\ 

The command-line program knows various parameters. To configure the Northwind database on the local SQL server, for example, you can use the following command:

 aspnet_regsqlcache -d Northwind -ed -E 

In a second call of the program, you must activate the desired table:

 aspnet_regsqlcache -d Northwind -t Customers -et -E 

To receive a listing of the currently activated tables, you can use the following command:

 aspnet_regsqlcache -d Northwind -lt -E 

Caution

Please note that all parameters are currently case sensitive. If you have any difficulties activating the invalidation, please double-check your input.

The program will create a new table with the (endless) name AspNet_SqlCacheTablesForChangeNotification within the specified database. For each table to be monitored, the table contains an entry with name of the table, a timestamp of the creation, and a version number. This number is incremented with each change through a trigger within the database to be monitored. By polling and comparing, the version changes can be identified and the cache gets invalidated.

 CREATE TRIGGER dbo.[Customers_AspNet_SqlCacheNotification_Trigger]     ON [Customers] FOR INSERT, UPDATE, DELETE AS BEGIN     IF NOT EXISTS (SELECT tableName FROM         dbo.AspNet_SqlCacheTablesForChangeNotification         WHERE tableName = 'Customers')         RETURN     ELSE     BEGIN         UPDATE dbo.AspNet_SqlCacheTablesForChangeNotification             SET changeId = changeId + 1             WHERE tableName = 'Customers'     END END 

Once the database, including the table, is configured, you must add both to the web.config configuration file. To allow you to do this, the new section <sqlCacheDependency> works together with the also new section <connectionStrings>. If you want to get monitoring on the Northwind database on the local SQL Server for example, your web.config file looks like this:

 <configuration>     <connectionStrings>         <add name="Northwind" connectionString="server=.;             database=Northwind;Integrated Security=SSPI;" />     </connectionStrings>     <system.web>         <cache>             <sqlCacheDependency enabled="true" pollTime="5000">                 <databases>                     <add name="Northwind" connectionStringName="Northwind" />                 </databases>             </sqlCacheDependency>         </cache>     </system.web> </configuration> 

Using SQL Server Cache Invalidation with Data Source Controls

Although it's quite time consuming to configure the new caching possibility, it's much easier to use afterward, as the example in the following listing clearly shows. In principle, you must only activate the caching of the used SqlDataSource control and assign the desired dependency to the SqlCacheDepedency property. The dependency consists of the database name configured in the web.config file as well as the table name. Both values are separated by colon—in this case, Northwind:Customers.

 <%@ page language="C#" %> <html> <head runat="server">     <title>Untitled Page</title> </head> <body>     <form runat="server">         <asp:gridview  runat="server" allowpaging="True"             datasource allowsorting="True"                 autogeneratecolumns="False"             selectedindex="0" datakeynames="CustomerID">         </asp:gridview>         <asp:sqldatasource  runat="server"             selectcommand="SELECT CustomerID, CompanyName,                 ContactName FROM Customers "             providername="System.Data.OleDb"             connectionstring="..."              sqlcachedependency="Northwind:Customers"              enablecaching="True">         </asp:sqldatasource>     </form> </body> </html> 

You can test this example very easily by setting the polling value in the configurations file to 5 or more seconds, and afterward you can change any value in the table. If you refresh the page in the browser, the change will only become effective after a few seconds. A look at the table AspNet_SqlCacheTablesForChangeNotification shows that the version has been incremented.

Using SQL Server Cache Invalidation Programmatically

You can use the new SqlCacheDependency class directly in the source code to place any elements in the cache. This is particularly useful for data that should be globally available.

Listing 11-19 shows the query and the caching of a DataSet within the cache object. An instance of the new SqlCacheDependency class is passed as a parameter when you add the object to the cache. Similar to the SqlDataSource control, the class was prepared for the Northwind database. In case of changes, the object will be invalidated in the cache, which results in a new creation of the DataSet through CacheItemRemovedCallback.

Listing 11-19: Yukon Allows Line-Based Cache Invalidation

 <%@ page language="C#" %> <%@ import namespace="System.Data" %> <%@ import namespace="System.Data.SqlClient" %> <script runat="server"> void Page_Load(object sender, System.EventArgs e) {     if (this.Cache["ds"] == null)     {         this.CreateDataSet();     }     if (this.IsPostBack == false)     {         this.LB_Customers.DataSource = this.Cache["ds"];         this.LB_Customers.DataBind();         this.LT_LastUpdate.Text = ((DateTime)this.Cache["LastUpdate"]).ToString();     } } void CreateDataSet() {     string connString = ConfigurationSettings.ConnectionStrings["Yukon"];     string cmdText = "Select CompanyName FROM Customers";     SqlConnection connect = new SqlConnection(connString);     SqlDataAdapter adapter = new SqlDataAdapter(cmdText, connect);     DataSet ds = new DataSet();     adapter.Fill(ds);     SqlCacheDependency dependency = new SqlCacheDependency("Yukon",                                                            "Customers");     this.Cache.Add("ds", ds, dependency, DateTime.MaxValue, TimeSpan.Zero,         CacheItemPriority.Default, new CacheItemRemovedCallback(         this.DataSetRemoveFromCache));     this.Cache.Insert("LastUpdate", DateTime.Now); } void DataSetRemoveFromCache(string key, object value,                             CacheItemRemovedReason reason) {     this.CreateDataSet(); } </script> <html> <head runat="server">     <title>Untitled Page</title> </head> <body>     <form runat="server">         <p>             <asp:listbox  runat="server"                 height="200px" width="300px" datatextfield="CompanyName">             </asp:listbox>         </p>         <p>             Last update:             <asp:literal  runat="server">             </asp:literal>         </p>     </form> </body> </html> 

If you're using Yukon, you can pass a SqlCommand (instead of the specified name of the database and the index) to the constructor of the SqlCacheDependency class, in a way similar to this example. This way, you can use single-line-oriented SQL Cache Invalidation.

Tip

If you want to use the SQL Server Invalidation without caching, you can use the SqlDependency class from the System.Data.SqlClient namespace.

Other Caching Enhancements

In previous version of ASP.NET, you already had the option to cache depending on a time span, a file, or a different cache entry using the CacheDependency class. Beyond this, the caching system couldn't be enhanced by developers, however, because the class was marked as sealed.

In the new version of the Framework, the class isn't sealed anymore and even offers some overridable methods. By inheriting from the class, you can now implement totally individual cache dependencies and integrate them seamlessly in the caching framework.

Two classes deriving from CacheDependency are already shipped with ASP.NET. You have already learned about the SqlCacheDependency class. The second class is called AggregateCacheDependency. This class allows the aggregation of several other dependencies. This way, you can, for example, make a cached object depend on two different tables. You aren't limited to a specific type; you can even mix the existing ones with custom dependencies.

Using Page and Control Caching

Do you remember the @OutputCache directive? Of course you do! This directive allows you to specify a SQL Server dependency for complete pages as well as for controls. You can place the desired dependency through the new sqldependency attribute in the familiar form database:table. Listing 11-20 shows you how it works, and Figure 11-17 shows you how it looks.

Figure 11-17: Any page or control can be cached with SQL Server Cache Invalidation.

Listing 11-20: The @OutputCache Directive Now Supports SQL Server Cache Invalidation

 <%@ page language="C#" %> <%@ outputcache duration="600" varybyparam="none"     sqldependency="Northwind:Customers" %> <script runat="server"> void Page_Load(object sender, System.EventArgs e) {     this.LT_LastUpdate.Text = DateTime.Now.ToString(); } </script> <html> <head runat="server">     <title>Untitled Page</title> </head> <body>     <form runat="server">         <asp:gridview  runat="server" allowpaging="True"             datasource allowsorting="True"                 autogeneratecolumns="False"             selectedindex="0" datakeynames="CustomerID">         </asp:gridview>         <asp:sqldatasource  runat="server" selectcommand="SELECT             CustomerID, CompanyName, ContactName FROM Customers"             providername="System.Data.OleDb" connectionstring="...">         </asp:sqldatasource>         <p>             Last update:             <asp:literal  runat="server"/>         </p>     </form> </body> </html>