A new wave: ASP.NET 2.0 Caching
One of the biggest changes in the ASP.NET 2.0 framework concerns how you access database data in an ASP.NET page. The ASP.NET 2.0 framework includes a new set of controls, known collectively as the DataSource controls, which enable you to represent a data source such as a database or an XML file.
In the ASP.NET 1.1 framework, the controls are bounded with DataSet or DataReader. In the ASP.NET 2.0 framework, we bind a control to a DataSource control instead. The DataSource control is having a better functionality and greater capabilities. It minimizes the code to be written to develop a database driven page. In ASP.NET 2.0 following three DataSource controls are there:
SqlDataSource: Represents any SQL data source, i.e. Microsoft SQL Server or an Oracle database.
AccessDataSource: A specialized SqlDataSource control designed for working with a MS Access.
ObjectDataSource: Represents a custom business object that acts as a data source.
Caching with the DataSource Controls
Th DataSource controls provide direct support for caching the database data. The DataSource control properties can be defined to cache automatically the data represented by a DataSource control in memory.
For example, if you want to cache the Northwind database table in memory for at least 10 minutes, you can declare a SqlDataSource control using following code:
ConnectionString="Server=localhost;database=Northwind"
SelectCommand="SELECT CategoryId FROM Categories"Runat="server" />
The EnableCaching property controls automatic caching of the data retrieved by the SelectCommand. The CacheDuration property specifies the duration of caching.
By default, the SqlDataSource will cache data using an absolute expiration policy. This can also be configured to use a sliding expiration policy.
SQL Cache Invalidation: The Most Awaited
SQL Cache Invalidation is one of the most anticipated new features of the ASP.NET 2.0 framework. It is a mechanism which enables you to automatically update data in the cache whenever the data changes in the underlying database.
How it works?
SQL Cache Invalidation works by constantly polling the database to check for changes. After every definite milliseconds period, the ASP.NET framework checks whether or not there have been any changes to the database. If the ASP.NET framework detects any changes, then any items added to the cache that depend on the database are removed from the cache (they are invalidated).
SQL Cache Invalidation only works with Microsoft SQL Server version 7 and higher. This is not supported in MS Access or Oracle.
Configuring SQL Cache Invalidation
To configure SQL Cache Invalidation, we have to perform following steps:
1.Configure SQL Server to support SQL Cache Invalidation
2.Configure Application’s Web Configuration File
Configuring SQL Server for SQL Cache Invalidation
To configure SQL Server for Cache Invalidation we can either use the aspnet_regsql command line tool, or SqlCacheDependencyAdmin class. We’ll configure using aspnet_regsql tool.
Enabling SQL Cache Invalidation with aspnet_regsql tool
The aspnet_regsql tool is a command line utility to enable SQL Cache Invalidation. The aspnet_regsql tool is located in your Windows\Microsoft.NET\Framework\[version] folder. This tool can be directly run using “Visual Studio .NET Whidbey Command Prompt” option in the Programs Visual studio tools Menu. If you run this tool directly, this will start a wizard which looks like the figure 1a. However, to set the SQL Cache Invalidation, we need to use it with certain arguments.
In order to support SQL Cache Invalidation when using the Northwind database, you need to execute the following command.aspnet_regsql -E -d Northwind -ed
The -E option causes the aspnet_regsql tool to use integrated security when connecting to your database server. The -d option selects the Northwind database. Finally, the -ed option enables the database for SQL Cache Invalidation.
This command creates a new table named AspNet_SqlCacheTablesForChangeNotification in the database. This table contains a list of all of the database tables that are enabled for SQL Cache Invalidation. The command also adds a set of stored procedures to the database.After this, you need select the particular table in the database to which you want to enable the Cache Invalidation. The following command will do that:
aspnet_regsql -E -d Northwind -t Categories –et
The -t option selects a database table. The -et option enables a database table for SQL Cache Invalidation. For multiple tables, you need to execute the command for each table.
This command adds a trigger to the database table. The trigger fires whenever you make a modification to the table and it updates the AspNet_SqlCacheTablesForChangeNotification table.
Application Web Configuration Settings for SQL Cache Invalidation
The next step is to configure the ASP.NET framework to poll the databases that you have enabled for SQL Cache Invalidation. Following listing is the web.config file for your application.
Web.Config File listing
In the Web configuration file, the <CONNECTIONSTRINGS>section is used to create a database connection string to the Northwind database named CacheSqlServer.
The section is used to configure the SQL Cache Invalidation polling. You can specify different polling intervals for different databases in the section.