Asp.Net 2.0's New SQL Server-Based Cache Invalidation Mechanism

With the advent of SQL Server 2005, your application can request SQL Server to notify it when critical data in a database has changed. Up to that point, an application can safely continue to retrieve data from the cache. This provides a much more granular level of control and takes any guesswork out of the question of how often the cache should be refreshed. This is possible via a new feature called Query Notification, used in conjunction with .NET 2.0.

There are various types of caching available in .Net. Then what's special in Sqlserver query notification. Basically one of the pitfalls with ASP.NET is that it is app domain specific. So if you have a web farm environment, each web server has its own copy of the cache, and this means there is a potential for data being out of sync. With SQL cache dependency you can cache the data on the web server but have SQL Server notify you when the data changes.

An overview of Query Notification

A multi-user web application should be able to provide the latest critical data to its users. To be sure of meeting this need, your application may have to retrieve the results from a database every time the user issues the query. However, this unnecessarily increases network round-trips, and is an inefficient use of resources. To reduce the number of round-trips, developers have traditionally relied on caching mechanisms. Once a query is run, the data is subsequently retrieved from the application's local cache. However, the lifetime value of this cache is generally defined so that the application can periodically update the cache with new values from the database, irrespective of whether the data in the back end has changed or not. This is much better, but it is still not precise.

With query notification enabled then when you run a query against the back-end database, you not only retrieve the data into the cache, but also tell SQL Server to register a subscription for notification if the underlying data changes in a way that will affect the result of the query.

When the notification is received, the event handler in your application invalidates the cache and the next time the application runs the query, it will fetch the data from the back-end server.

All this is done without the need to write any complex application code.

Before you can establish cache dependency with SQL Server 2005, you need to perform the following steps:

• Configure SQL Server to support SQL Cache invalidation. This is a one-time setup of the tables or databases in the SQL Server database that you want to monitor.
• Add the necessary configuration information to the web.config file.

Configuration of SQL Server to Support SQL Cache Invalidation

You can perform the configuration of SQL Server 2000 to support SQL Cache invalidation in two ways:

1. Using the aspnet_regsql utility
2. Using the EnableTableForNotifications method of the SqlCacheDependencyAdmin class

For this article, consider the first method. Basically, the aspnet_regsql utility creates an extra table named AspNet_SqlCacheTablesForChangeNotification that is used to keep track of the changes to all the monitored tables in the database. It also creates a number of triggers and stored procedures to enable this capability. To run the aspnet_regsql utility, open up the Visual Studio command prompt and enter the command shown below.

Command : aspnet_regsql -S localhost -U sa -P atanu -d TestDB -ed

Click the image to enlarge

The command enables the TestDB (My database..you can use your own) database to support SQL cache invalidation:

• S—Name of the Server
• U—User ID to use to connect to the SQL Server
• P—Password to use to connect to the SQL Server
• d—The name of the database
• ed—Enables the database for SQL Server-triggered cache invalidation

Once this is done at the database level, you need to enable cache invalidation at the individual table level.

Command :aspnet_regsql -S localhost -U sa -P atanu -t Products -d TestDB -et

Click the image to enlarge

In the above command:

• t—Specifies the name of the table
• et—Enables the table for SQL Server-triggered cache invalidation

The preceding screenshot shows how to enable SQL cache invalidation for the Products table in the TestDB database. Once you configure the Products table to send notifications, any time data in the table changes, it notifies ASP.NET to invalidate the corresponding item in the cache.

Web Configuration Settings for SQL Cache Invalidation

The next step, before you can use SQL cache invalidation in your ASP.NET application, is to update the Web configuration file. You need to instruct the ASP.NET framework to poll the databases that you have enabled for SQL cache invalidation. The following Web configuration file contains the necessary configuration information to poll the TestDB database at periodic intervals:

Click the image to enlarge

The preceding Web configuration file contains two sections, [connectionStrings] and [caching]. The connectionStrings section creates a database connection string to the TestDB database named "TestDbConnection". The caching section configures the SQL cache invalidation polling. Within the databases subsection, you list one or more databases that you want to poll for changes. The add section inside the databases section indicates that the database represented by "TestDB" is polled once a minute (every 60,000 milliseconds). You can specify different polling intervals for different databases. Remember, the server must do a little bit of work every time the database is polled for changes. If you don't expect the data in the database to change very often, you should increase the polling interval.

Enabling query notification in ASP.NET using SQLCacheDependency

Now that you have performed the required configurations, you are ready to take advantage of the SQL cache invalidation feature in your ASP.NET Web page. Add a new Web form named Default.aspx to your Web site. The following is the code for the Default.aspx where we are going to bind a Grid with the data of the Products table.

using System.Data.SqlClient;
using System.Web.Caching;

protected void Page_Load(object sender, EventArgs e)
{
GridView1.DataSource = bindCachedData();
GridView1.DataBind();
}

private DataSet bindCachedData()
{

DataSet dsProducts;
dsProducts = (DataSet)Cache.Get("Products");

if (dsProducts == null)
{
SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["TestDbConnection"]
.ConnectionString);
SqlDataAdapter adapter = new SqlDataAdapter("Select * from Products", sqlcon);
dsProducts = new DataSet();
adapter.Fill(dsProducts);

SqlCacheDependency dependency = new SqlCacheDependency("TestDbConnection", "Products");
Cache.Insert("Products", dsProducts, dependency);
}

return dsProducts;
}

In the above code, you start by creating an instance of the SqlConnection object passing in the connection string that is retrieved from the web.config file. Then you create an instance of the SqlDataAdapter object and pass in the SQL statement to be executed and the previously created SqlConnection object as its arguments. Then you execute the SQL query using the Fill method of the SqlDataAdapter object. After that you create an instance of the SqlCacheDependency object and supply the database name (that corresponds to the database name specified in the web.config file) and the table name as its arguments. Then you insert the categories dataset to the cache using the Insert method of the Cache object. At the time of inserting, you should also specify the SqlCacheDependency object so that the dataset can be invalidated when the data in the Product table changes. Finally, you bind the dataset to the GridView control.

The first time this application loads, there will be nothing in the application cache and dsProducts will be null. As a result, the query will be called. When this happens, the function will register a notification subscription with SQL Server. It will also load up the cache with the output of our query. When the user runs this query again the data will be retrieved from the cache. You can confirm this by running the app in debug mode.

To test if the SQL Server based cache invalidation mechanism works, modify the data in the Products table and then if you navigate to the page using the browser, you will get the changed data as retrieved from the database.