Browse DevX
Sign up for e-mail newsletters from DevX


Learn Better Ways to Manage Your Cache in ASP.NET : Page 3

Learn how to use caching effectively today and tomorrow with some new (and some not-quite-as-new) cache management techniques for ASP.NET 1.x and 2.0.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Technique #4: Using SQL Server Cache Dependency
(ASP.NET 2.0 only)
In the earlier example, I showed how you can use cache dependency to invalidate the cache when the content of a file has changed. While this is useful, it has its limitations. The biggest one being that data is usually stored in a database such as SQL Server. Therefore, it's more common to cache data from a database, rather than a text file.

ASP.NET 2.0 has a new facility, the SQL Cache Dependency, which allows you to invalidate your cache based on the changes in the database. To use it, you first need to prepare your database and tables for change notification using a tool called aspnet_regsql.exe. To run the tool, go to Start->Programs->Visual Studio 2005 Beta->Visual Studio Tools->Visual Studio .NET Whidbey Command Prompt.

The aspnet_regsql.exe is a mixed mode (both graphical and command-based) tool that allows configuring your SQL Server for use with your ASP.NET application. To see the various options available with it, use the "/?" option:

C:\Program Files\Microsoft Visual Studio 8\VC>aspnet_regsql /?

There are two steps you need to take to enable SQL Cache Dependency:
  • Enable the database for change notifications
  • Enable the tables for change notifications
To enable a database, use the tool's -ed option (see Figure 3) by entering the following on the command line:

C:\Program Files\Microsoft Visual Studio 8\VC>aspnet_regsql -S localhost -E -d Northwind -ed

To enable the tables, use the tool's -et option (see Figure 4) by entering the following on the command line:

C:\Program Files\Microsoft Visual Studio 8\VC>aspnet_regsql -S localhost -E -t Products -d Northwind -et

Figure 3. Change Notification: Use the -ed option on the command line to enable the database.
Figure 4. Change Notification: Use the -et option on the command line to enable the table.

Once you have run the aspnet_regsql tool, a table named AspNet_SqlCacheTablesForChangeNotification will be created within the specified database (Northwind, in this case). This table (see Figure 5) simply contains the last modification date of the named table as well as the total number of changes. Every time your table changes, this table is modified. AspNet_SqlCacheTablesForChangeNotification is where ASP.NET checks to track for SQL Cache Dependency.

The next step would be to enable SQL Cache Dependency in your application. To do so, you need to first modify Web.config file (note the addition in bold):

<?xml version="1.0"?> <configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0"> <appSettings/> <connectionStrings> <add name="NorthwindDatabase" connectionString="Server=(local);Integrated Security=True;
Database=Northwind;Persist Security Info=True" providerName="System.Data.SqlClient" /> </connectionStrings> <system.web>
<compilation debug="true"/> <authentication mode="Windows"/> <caching> <sqlCacheDependency enabled="true"> <databases> <add name="Northwind" connectionString="NorthwindDatabase" pollTime="5000" /> </databases> </sqlCacheDependency> </caching> </system.web> </configuration>

Author's Note: In Beta 1, the element name for the cache configuration is and not as is widely documented in the help files.

Figure 5. Cache Crash: The AspNet_SqlCacheTablesForChangeNotification table is where SQL Server checks for cache dependency.
Here, I have specified a connection string that connects to the Northwind database. I have also specified that I want to enable SQL Server caching and indicate a polling time of 5 seconds (unit is in milliseconds). This is the frequency at which the ASP.NET runtime (actually it is a background thread that is spun off for this purpose) polls the database (the AspNet_SqlCacheTablesForChangeNotification file) for changes. As the file is small, this process is very efficient and will not slow down the system. Hence it makes sense to specify a low number so that the application is always displaying the most updated data.

Finally, modify the data source control properties (assuming you are using a SqlDataSource control here) on which you want to enable SQL Cache Dependency (see Figure 6):

  • EnableCaching: "True"
  • SqlCacheDependency: "Northwind:Products"
  • Figure 6. Picture This: Here you can see how to enable a SqlDataSource control for SQL Cache Dependency.
    The SqlCacheDependency attribute has the format of: database:table.

    To see if SQL Server Cache dependency works, modify the rows in the Products table and refresh the page a few times. The table will refresh every 5 seconds if there is a change in the related database.

    In this article, you have seen a number of techniques for caching your Web pages to enhance its performance. Most notably in ASP.NET 2.0, you have the ability to fine-tune the caching options, such as fragment caching as well as SQL Server Cache Dependency. While we are still some time away from the release of ASP.NET 2.0, it is time to investigate the new features that will make your applications more powerful.

Wei-Meng Lee is a Microsoft .NET MVP and co-founder of Active Developer, a training company specializing in .NET and wireless technologies. He is a frequent speaker and author of numerous books on .NET, XML, and wireless technologies.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date