Performance is a key requirement for any application or piece of code that you develop.

This article focuses on the new SQL invalidation caching capabilities that ASP.NET 2.0 provides. When you are using SQL cache invalidation, if the result set from SQL Server changes, the output cache can be triggered to change. This ensures that the end user always sees the latest result set, and the data presented is never stale.

To utilize the new SQL Server Cache Dependency feature in ASP.NET 2.0, you must perform a one-time setup of your SQL Server database. To set up your SQL Server, use the aspnet_regsql.exe tool found atC:\Windows\Microsoft.NET\Framework\v2.0xxxxx\. This tool makes the necessary modifications to SQL Server so that you can start working with the new SQL cache invalidation features.

Step 1: Understanding aspnet_regsql.exe

To start, you need to get at the aspnet_regsql.exe tool. Open up the Visual Studio Command Prompt by choosing Start-->All Programs-->Microsoft Visual Studio 2005-->Visual Studio Tools--> Visual Studio Command Prompt from the Windows Start menu. After the prompt launches, type this command:

aspnet_regsql.exe -?

This code outputs the help command list for this command-line tool, as shown in the following:

SQL CACHE DEPENDENCY OPTIONS

d <database> Database name for use with SQL cache dependency. The database can optionally be specified using the connection string with the -c option instead.
(Required)
ed Enable a database for SQL cache dependency.
dd Disable a database for SQL cache dependency.
et Enable a table for SQL cache dependency. Requires -t option.
dt Disable a table for SQL cache dependency. Requires -t option.
t <table> Name of the table to enable or disable for SQL cache dependency. Requires -et or -dt option.
lt List all tables enabled for SQL cache dependency.

Step 2: Enabling Databases for SQL Server Cache Invalidation

aspnet_regsql.exe -S localhost -U sa -P word -d Northwind -ed

This produces something similar to the following output:

Enabling the database for SQL cache dependency.
..
Finished.

Step 3: Enabling Tables for SQL Server Cache Invalidation

You enable or more tables by using the following command:

aspnet_regsql.exe -S localhost -U sa -P word -d Northwind -t Customers -et
aspnet_regsql.exe -S localhost -U sa -P word -d Northwind -t Products -et

Go ahead and enable both the Customers and Product tables. You run the command once per table. After a table is successfully enabled, you receive the following response:

Enabling the table for SQL cache dependency.
.
Finished.

Step 4: Looking at SQL Server

Now that the Northwind database and the Customers and Products tables have all been enabled for SQL cache invalidation, look at what has happened in SQL Server. If you open up the SQL Server Enterprise Manager, you see a new table contained within the Northwind database-

AspNet_SqlCacheTablesForChangeNotification

At the top of the list of tables in the right-hand pane, you see the AspNet_SqlCacheTablesForChangeNotification table. This is the table that ASP.NET uses to learn which tables are being monitored for change notification and also to make note of any changes to the tables being monitored.
You can see three columns in this new table.

The first is the tableName column. This column simply shows a String reference to the names of the tables contained in the same database. Any table named here is enabled for SQL cache invalidation.

The second column, notificationCreated, shows the date and time when the table was enabled for SQL cache invalidation.

The final column, changeId, is used to communicate to ASP.NET any changes to the included tables. ASP.NET monitors this column for changes and, depending on the value, either uses what is stored in memory or makes a new database query.

Step 5: Looking at the Tables That Are Enabled(Command Prompt)

aspnet_regsql.exe -S localhost -U sa -P word -d Northwind -lt

Inputting this command produces the following results:

Listing all tables enabled for SQL cache dependency:

Customers

Products

Step 6: Configuring Your ASP.NET Application

After you enable a database for SQL Server cache invalidation and also enable a couple of tables within this database, the next step is to configure your application for SQL Server cache invalidation.

Web.config file

<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
<connectionStrings>
<add name="AppConnectionString1" connectionString="Data Source=localhost;
User ID=yourid;word=yourword;Database=Northwind;Persist Security Info=False"
providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>
<caching>
<sqlCacheDependency enabled="true">
<databases>
<add name="Northwind" connectionStringName="AppConnectionString1"
pollTime="500" />
</databases>
</sqlCacheDependency>
</caching>
</system.web>
</configuration>

Step 7: Testing SQL Server Cache Invalidation

.aspx file

<%@ Page Language="C#" %>
<%@ OutputCache Duration="3600" VaryByParam="none"
SqlDependency="Northwind:Customers"%>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Sql Cache Invalidation</title>
</head>
<body>
<form id="form1" runat="server">
<asp:Label ID="Label1" Runat="server"></asp:Label><br />
<br />
<asp:GridView ID="GridView1" Runat="server" DataSourceID="SqlDataSource1">
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" Runat="server"
SelectCommand="Select * From Customers"
ConnectionString="<%$ ConnectionStrings:AppConnectionString1 %>"
ProviderName="<%$ ConnectionStrings:AppConnectionString1.providername %>">
</asp:SqlDataSource>
</form>
</body>
</html>

.cs file

protected void Page_Load(object sender, System.EventArgs e)
{
Label1.Text = "Page created at " + DateTime.Now.ToShortTimeString();
}

The first and most important part of this page is the OuputCache page directive that is specified at the top of the file. Typically, the OuputCache directive specifies how long the page output is held in the cache using the Duration attribute.

Next is the VaryByParam attribute.

The new addition is the SqlDependency attribute. This enables a particular page to use SQL Server cache invalidation.

Step 8: Adding More Than One Table to a Page

To add more than one table, you use the OuputCache directive shown here:

SqlDependency="database:table;database:table"