ASP.NET Caching: SQL Cache Dependency With SQL Server 2000

Introducing Cache Dependencies:

As time passes, the data source may change in response to other actions. However, if your code uses caching, you may remain unaware of the changes and continue using out-of-date information from the cache. To help mitigate this problem, ASP.NET supports cache dependencies. Cache dependencies allow you to make a cached item dependent on another resource so that when that resource changes the cached item is removed automatically. ASP.NET includes three types of dependencies:

• Dependencies on other cache items.
• Dependencies on files or folders.
• Dependencies on a database query.

Introducing SQL Cache Notifications:

SQL cache dependencies are one of the most wonderful new features in ASP.NET 2.0, the ability to automatically invalidate a cached data object (such as a DataSet or Custom Data Type) when the related data is modified in the database. This feature is supported in both SQL Server 2005 and in SQL Server 2000, although the underlying plumbing is quite different.

Cache Notifications in SQL Server 2000:

Before you can use SQL Server cache invalidation, you need to enable notifications for the database. This task is performed with the aspnet_regsql.exe command-line utility, which is located in the c:\[WinDir]\Microsoft.NET\Framework\[Version] directory. To enable notifications, you need to use the -ed command-line switch. You also need to identify the server (use -E for a trusted connection and -S to choose a server other than the current computer) and the database (use -d). Here's an example that enables notifications for the Northwind database on the current server:

aspnet_regsql -ed -E -d Northwind

After executing this command, a new table named SqlCacheTablesForChangeNotification is added to the database Northwind. The SqlCacheTablesForChangeNotification table has three columns: tableName, notificationCreated, and changeId. This table is used to track changes. Essentially, when a change takes place, a record is written into this table. The SQL Server polling queries this table. Also a set of stored procedures is added to the database as well. See the following table.

After this, need to enable notification support for each individual table. You can do this manually using the AspNet_SqlCacheRegisterTableStoredProcedure, to that, open your query analyzer and select your Database you've enabled for SQL Cache Notification for example Northwind database and write the following command:

exec AspNet_SqlCacheRegisterTableStoredProcedure 'TableName'

Or you can use aspnet_regsql, using the -et parameter to enable a able for sql cache dependency notifications and the -t parameter to name the table. Here's an example that enables notifications for the Employees table:

aspnet_regsql -et -E -d Northwind -t Products

Both options generates the notification trigger for the Products table as the following:

CREATE TRIGGER dbo.[Products_AspNet_SqlCacheNotification_Trigger] ON [Products]
FOR INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON
EXEC dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedure N'Products'
END

So any record is inserted, deleted or updated in Products table will update ChangeId field in AspNet_SqlCacheTablesForChangeNotification table.

How Notificaions Works:

The AspNet_SqlCacheTablesForChangeNotification contains a single record for every table you're monitoring. When you make a change in the table (such as inserting ,deleting or updating a record), the changeId column is incremented by 1 -see AspNet_SqlCacheUpdateChangeIdStoredProcedure procedure-. ASP.NET queries this table repeatedly and keeps track of the most recent changeId values for every table. When this value changes in a subsequent read, ASP.NET knows that the table has changed.

In this scenario, Any change to the table is deemed to invalidate any query for that table. In other words, if you use this query:

SELECT * FROM Products WHERE CategoryID=1

The caching still works in the same way. That means if any product record is touched, even if the product resides in another category (and therefore isn't one of the cached records), the notification is still sent and the cached item is considered invalid. Also keep in mind it doesn't make sense to cache tables that change frequently.

Enable ASP.Net Polling:

To enable ASP.NET polling , you need to use the <sqlCacheDepency> element in the web.config file. Set the enabled attribute to true to turn it on, and set the pollTime attribute to the number of milliseconds between each poll. (The higher the poll time, the longer the potential delay before a change is detected.) You also need to supply the connection string information.

Creating the Cache Dependency:

Now that we've seen how to set up a database to support SQL Server notifications, the only remaining detail is the code, which is quite straightforward. We can use our cache dependency with programmatic data caching, a data source control, and output caching.

For programmatic data caching, we need to create a new SqlCacheDependency and supply that to the Cache.Insert() method. In the SqlCacheDependency constructor, you supply two strings. The first is the name of the database you defined in the element in the section of the web.config file e.g: Northwind. The second is the name of the linked table e.g: Products.

Example:

private static void CacheProductsList(List<NWProductItem> products)
{

SqlCacheDependency sqlDependency = new SqlCacheDependency("Northwind", "Products");
HttpContext.Current.Cache.Insert("ProductsList", products, sqlDependency, DateTime.Now.AddDays(1), Cache.NoSlidingExpiration);

}

private static List<NWProductItem> GetCachedProductList()
{

return HttpContext.Current.Cache["ProductsList"] as List<NWProductItem>;

}

NWProductItem is business class, and here we are trying to cache a list of NWProductItem instead of DataSet or DataTable.

The following method is used by an ObjectDataSource Control to retrieve List of Products

public static List<NWProductItem> GetProductsList(int catId, string sortBy)
{

//Try to Get Products List from the Cache
List<NWProductItem> products = GetCachedProductList();
if (products == null)
{

//Products List not in the cache, so we need to query the Database by using a Data Layer
NWProductsDB db = new NWProductsDB(_connectionString);
DbDataReader reader = null;
products = new List<NWProductItem>(80);

if (catId > 0)
{

//Return Product List from the Data Layer
reader = db.GetProductsList(catId);

}
else
{

//Return Product List from the Data Layer
reader = db.GetProductsList();

}
//Create List of Products -List if NWProductItem-
products = BuildProductsList(reader);
reader.Close();

//Add entry to products list in the Cache
CacheProductsList(products);

}
products.Sort(new NWProductItemComparer(sortBy));

if (sortBy.Contains("DESC")) products.Reverse();
return products;

}

To perform the same trick with output caching, you simply need to set the SqlDependency property with the database dependency name and the table name, separated by a colon:

<%@ OutputCache Duration="600" SqlDependency="Northwind:Products" VaryByParam="none" %>

The same technique works with the SqlDataSource and ObjectDataSource controls:

<asp:SqlDataSource EnableCaching="True" SqlCacheDependency="Northwind:Products" ... />

Important Note:

ObjectDataSource doesn't support built in caching for Custom types such as the one in our example. It only support this feature for DataSets and DataTables.

To test this feature, download the attached demo. It contains an editable GridView. Set a break point at the GetProductList method and run in debug mode. Update any record and notice the changes. Also you can edit the solution and remove the cache dependency and note the deference after update.

Also you can remove the SqlDependency from the output cache in the OutputCaching.aspx page, and notice that whatever update you made to the data source, the page still retrieves the old version of data.