Improved Data Caching in ASP.NET 2.0

To build fast and efficient data driven web pages role of caching is important. In this article I am not going to discuss caching in earlier versions of ASP.NET (i.e. ASP.NET 1.x). Focus of article is only on new features offered for data caching in ASP.NET 2.0. ASP.NET 2.0 introduced series of new DataSource controls to work with database. With this new data source controls caching model is also improved in ASP.NET 2.0. It delivers better performance than earlier versions.

System.Web.Caching.CacheDepenedency

This is a new caching purpose class introduced in ASP.NET 2.0. It enables you to write your own implementation of cache dependency. You can use this class with new DataSource controls in ASP.NET 2.0.

Let’s look at following SqlDataSource tag:

<asp:SqlDataSource ID="SqlDataSource1" Runat="server" EnableCaching="True" CacheDuration="500" CacheExpirationPolicy="Sliding"
ConnectionString="Server=localhost;database=Payroll"
SelectCommand="SELECT * FROM Employee" />

Description about caching related attributes:

EnableCaching: Set it to “True” to enable caching.

CacheDuration: It’s time of cache duration. This is a time in seconds. In ASP.NET 1.x, we set very little time to cache duration due to fear of old data would be served from cache. But now ASP.NET 2.0 offers new feature called SQL cache invalidation, this gives notification about data changes in the database. So whenever data is changed at database side, new data is fetched from database instead of cache. So you can set big time duration for cache duration attribute. In this way it helps to reduce network load.

CacheExpirationPolicy: By default “absolute”. Absolute is cause to refresh data from server after so many seconds. You can have an option to set it to “Sliding”. In sliding data will not be dropped as long as it continues to be accessed. This expiration policy is important when you want to cache large number of items.

SQL Cache Invalidation

As I said SQL cache invalidation is a new feature in ASP.NET 2.0 to notify about changes in database, so fetching of wrong data from cache is avoided. This is a great improvement over ASP.NET 1.x caching model. 

At background SQL cache invalidation polls the database to track changes. The meaning of invalidate is when any changes found at database side then that items are removed from cache, means invalidated, and changed items will be added.

SQL cache invalidation only works with SQL Server database (Version 7.0/2000). This feature is not available for Oracle, MS Access, DB2 or other databases. In SQL server 7.0/2000 there is no built in model for notifications. So ASP.NET application has to constantly monitor the database to check for changes. SQL server 2005 has inbuilt model for notifications. So SQL server 2005 can be configured to notify after changes in database, table or data row.

To configure SQL cache invalidation two steps are required, first configure SQL server to cache invalidation and second add configuration information in “caching” section under “system.web” in web.config file. 

Following are the important steps to configure SQL server:

I’ve used database named “Payroll”. In payroll, I am going to set SQL cache validation on “Employee” table which contains detailed information of working employees.

1. Open Visual Studio .NET 2005 command prompt. (By navigating to Microsoft Visual Studio 2005 -> Visual Studio Tools -> Visual Studio .NET 2005 Command Prompt) 
2. Use aspnet_regsql tool to enable SQL cache invalidation. The aspnet_regsql tool is present in your Windows\Microsoft.NET\Framework\[version] folder. Use command prompt to navigate for this folder.
3. To enable SQL cache invalidation on payroll table in SQL Server issue following command.

aspnet_regsql -E -d Payroll –ed

Press enter to execute the command.

Following is the information about options used with aspnet_regsql command:
-E: Used to enable integrated security when connecting to your database server. 
-d: Used to select the Payroll database. 
-ed: Used to enable the database for SQL Cache Invalidation.
payroll: It is the name of database.

After successful execution of an above command AspNet_SqlCacheTablesForChangeNotification, new table is added to the payroll database. This table contains a list of all of the database tables that are enabled for SQL cache invalidation. Some new stored procedures are also added to the database.


4. Now select a table in payroll database for SQL cache invalidation. Let’s select “Employee” table.

aspnet_regsql -E -d Payroll -t Employee –et

Following is the information about option used in above commands:
-t: Used to select a database table. In my case it is Employee table.
-et: Used to enable a database table for SQL cache invalidation. 

To enable cache invalidation for multiple tables re-execute this command for each database table.
This command adds trigger to database table. Trigger fires whenever changes are occurred in that table.

By doing above steps configuration at SQL server is complete here. But second part is still remaining that is adding some entries in web.config file without this caching won’t work even if you configured it for SQL Server.


File Name: web.config

*PayrollConnection is a name of connection string for Payroll database.

PollTime: This attribute is used to set interval time in millisecond. By this interval application will check cache table for changes. According to frequency of changes to database, you can increase/decrease this time interval. To less periodic changes you can set higher interval. In databases section, you can add multiple databases with different pollTime intervals.

Caching is work better only with “Dataset” mode of SqlDataSource. If “DataReader” is used as a return type, then it will not work perfectly because DataReader continuously needs live connection with database. 

Caching for ObjectDataSource is weird complicated. ObjectDataSource control is not work directly with connection string to connect with a database. It has middle tier data access class. For ObjectDataSource control if you use return type for SelectMethod other than DataSet, then NotSupported exception is fired.

So, go ahead with new caching features in ASP.NET 2.0 and build better performance-oriented data driven web applications.


About me:

I am Kuldeep Deokule. I’ve total 4 years IT experience on various Microsoft tools and technologies. I am Microsoft Certified Solution Developer (MCSD.NET). Currently working with Comtel Systems, Pune as a Sr. Software Engineer.

Email Address: kuldeep.deokule@gmail.com