Chapter 11. CACHING

One of the most frequently requested features for the ASP.NET Cache is the ability to invalidate cached items from the database. This capability, along with some changes to the underlying plumbing of the ASP.NET Cache, has been added in ASP.NET 2.0. However, the Technology Preview release does not contain all the planned additions to the caching features of ASP.NET. Microsoft still intends to add some additional capabilities, but they won't be available until the beta release.

The caching features in ASP.NET are specifically designed to increase throughput and decrease load on the server by keeping frequently accessed content in memory versus constantly running code. There are two primary uses of the Cache .

1. Output caching involves storing the response generated by an ASP.NET page, User Control, or Web Service in memory and using the in-memory response on subsequent requests rather than executing code. This is extremely fast since the server simply needs to copy the response from memory. A good example of this technique is a report. The contents used to render the report are generated once and stored in the output cache. Then the cached versions are used for subsequent requests.

2. The Cache API is a programmatic, dictionary-based API for accessing and storing frequently used data. Unlike output caching, which caches the contents of the response, the Cache API is used within running code to cache frequently accessed data. For example, a DataSet used in 50% of the pages within an application could be cached so that each page that requires the data doesn't have to recalculate the data on each request.

Behind the scenes, pages marked for output caching are stored and retrieved through the Cache API. The Cache itself is simply a hashtable with enhanced capabilities.

• Least recently used (LRU) : When ASP.NET needs more memory, the Cache will automatically evict items by using an LRU algorithm to remove items that are accessed infrequently.

• Cache dependencies : Items added to the Cache can be made dependent on external conditions whereby they are removed. For example, ASP.NET 1.0 supported three dependencies: Time , File , and Key . Items could be expired at a point in time (by using the Time dependency), when a file changed (by using the File dependency), and when another item in the Cache changed (by using a Key dependency). This functionality is encapsulated in the CacheDependency class found in the System.Web.Caching namespace.

While the Cache and its dependency features allowed complex applications to be built, the ASP.NET team soon realized that most cached data came from a database and there was not a dependency that allowed for invalidation when data in the database changed. Database change dependency is now possible in the ASP.NET 2.0 Cache .

Listing 11.1 demonstrates how this is used in a pageusing the new sqldependency attribute of the <%@ OutputCache %> directive.

Listing 11.1 SQL ServerBased Cache Dependency

 <%@ Page Language="VB" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <%@ OutputCache duration="5555"                 varybyparam="none"  sqldependency="Northwind:Products"  %>  <h1>Last update: <%=DateTime.Now.ToString("r")%></h1> <hr> <form runat="server">     <asp:gridview id="GridView1"                   datasourceid="SqlDataSource1"                   runat="server" />     <asp:sqldatasource id="SqlDataSource1"                        runat="server"                        providername="System.Data.OleDb"                        selectcommand="SELECT * FROM dbo.[Products]"                        connectionstring="provider=sqloledb;                                          database=northwind;                                          uid=sa;pwd=00password" /> </form> 

Database cache invalidation allows for the removal of an item from the Cache when data stored in the database changes.

ASP.NET 2.0 adds support for database cache invalidation for Microsoft SQL Server 7, Microsoft SQL Server 2000, and the next version of Microsoft SQL Server (code-named "Yukon").

Although support for these three databases is built into ASP.NET, there are some significant differences.

• Microsoft SQL Server 7 and Microsoft SQL Server 2000 : For these database products, only table-level changes are supported. For example, if you cached data from the Products table in the Northwind database and the table was updated, all cached data dependent on that table would be evicted from the Cache .

• Microsoft SQL Server "Yukon" : "Yukon" has implicit support for invalidation built directly into the database. Data can be invalidated from the Cache when the specific results of a request, such as a stored procedure, change.

The level of granularity between the databases is very relative. For example, if you output cache a page for each product in the Products table, you would output cache 77 pages. If you were using SQL Server 7 or SQL Server 2000, each of the 77 pages would be invalidated if the Products table were updated. However, with "Yukon" you can be more selective about how items are invalidated; you can invalidate only output-cached pages that changed (e.g., the page displaying information for the product with ID 35).

We are not going to focus on the "Yukon" caching capabilities in this chapter but will instead examine how we enable database cache invalidation from Microsoft SQL Server 7 and 2000. In order to use database cache invalidation on these databases, we first need to enable it.