Stephen Walther
Microsoft Corporation
June 2004
Applies to:
Microsoft ASP.NET 2.0
Microsoft ASP.NET framework
Microsoft SQL Server
Microsoft Visual Studio .NET
Summary: Stephen Walther looks at the new caching features included in ASP.NET 2.0, and how you can use them to improve the performance and scalability of your ASP.NET applications. (20 printed pages)
Contents
Data Caching Made Easy
Using SQL Cache Invalidation
Using Post-Cache Substitution
Conclusion
The most dramatic way to improve the performance of a database driven Web application is through caching. Retrieving data from a database is one of the slowest operations that you can perform in a Web site. If, however, you can cache the database data in memory, then you can avoid accessing the database with every page request, and dramatically increase the performance of your application.
The one and only drawback to caching is the problem of stale data. If you cache the contents of a database table in memory, and the records in the underlying database table change, then your Web application will display old, inaccurate data. For certain types of data you might not care if the data being displayed is slightly out of date, but for other types of data—such as stock prices and auction bids—displaying data that is even slightly stale is unacceptable.
The initial release of the Microsoft ASP.NET framework did not provide a good solution to this problem. When using the ASP.NET 1.0 framework, you just had to live with this tradeoff between performance and stale data. Fortunately, the Microsoft ASP.NET 2.0 framework includes a new feature called SQL Cache Invalidation that solves this very problem.
In this article, you'll learn about many of the new caching enhancements in the ASP.NET 2.0 framework. First, you'll learn how caching support has been integrated into the new DataSource controls. Next, you'll learn how to configure and take advantage of SQL Cache Invalidation. Finally, we'll take a look at a new control introduced with the ASP.NET 2.0 framework, which enables you to inject dynamic content into a cached page: the Substitution control.
Data Caching Made Easy
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.0 framework, you displayed database data with a control by binding the control to either a DataSet or a DataReader. In the ASP.NET 2.0 framework, you'll typically bind a control to a DataSource control instead. By taking advantage of the DataSource controls, you can build ASP.NET pages that display database data without writing any code for accessing the database.
When working with database data, you'll typically use one of the following three DataSource controls:
- SqlDataSource—Represents any SQL data source, such as a Microsoft SQL Server or an Oracle database.
- AccessDataSource—A specialized SqlDataSource control designed for working with a Microsoft Access database.
- ObjectDataSource—Represents a custom business object that acts as a data source.
For example, imagine that you need to display a list of book titles retrieved from a database in a DropDownList control (see Figure 1). The page in Listing 1 illustrates how you can bind a DropDownList control to a SqlDataSource control.
.gif)
Figure 1. Using the SqlDataSource control to retrieve data
Listing 1. DisplayTitles.aspx
<html>
<head runat="server">
<title>Display Titles</title>
</head>
<body>
<form id="form1" runat="server">
<asp:DropDownList
ID="DropDownList1"
DataSourceId="SqlDataSource1"
DataTextField="Title"
Runat="server" />
<asp:SqlDataSource
ID="SqlDataSource1"
ConnectionString="Server=localhost;database=Pubs"
SelectCommand="SELECT Title FROM Titles"
Runat="server" />
</form>
</body>
</html>
Notice that the SqlDataSource control in Listing 1 is used to provide the connection string, and the SQL SELECT command used for retrieving the records from the database. The DropDownList control is bound to the SqlDataSource control through its DataSourceID property.
Caching with the DataSource Controls
The DataSource controls not only enable you to connect more easily to a database, they also make it easier for you to cache database data. Simply by setting a couple of properties on the SqlDataSource control, you can automatically cache the data represented by a DataSource control in memory.
For example, if you want to cache the Titles database table in memory for at least 10 minutes, you can declare a SqlDataSource control like this.
<asp:SqlDataSource
ID="SqlDataSource1"
EnableCaching="true"
CacheDuration="600"
ConnectionString="Server=localhost;database=Pubs"
SelectCommand="SELECT Title FROM Titles"
Runat="server" />
When the EnableCaching property has the value true, the SqlDataSource will automatically cache the data retrieved by the SelectCommand. The CacheDuration property enables you to specify, in seconds, how long the data should be cached before it is refreshed from the database.
By default, the SqlDataSource will cache data using an absolute expiration policy—every so many seconds, the data is refreshed from the database. You also have the option of enabling a sliding expiration policy. When the SqlDataSource is configured to use a sliding expiration policy, the data will not be dropped as long as it continues to be accessed. Employing a sliding expiration policy is useful whenever you have a large number of items that need to be cached, since this expiration policy enables you to keep only the most frequently accessed items in memory.
For example, the following SqlDataSourceControl is configured to use a sliding expiration policy of 10 minutes.
<asp:SqlDataSource
ID="SqlDataSource1"
EnableCaching="true"
CacheExpirationPolicy="Sliding"
CacheDuration="600"
ConnectionString="Server=localhost;database=Pubs"
SelectCommand="SELECT Title FROM Titles"
Runat="server" />
Since the CacheExpirationPolicy property is set to the value Sliding and the CacheDuration property is set to the value 600, the data represented by this SqlDataSource will remain in memory just as long as it continues to be accessed within a 10 minute window.
Using SQL Cache Invalidation
SQL Cache Invalidation is one of the most anticipated new features of the ASP.NET 2.0 framework. By taking advantage of SQL Cache Invalidation, you get all of the performance benefits of caching without the problem of stale data. SQL Cache Invalidation enables you to automatically update data in the cache whenever the data changes in the underlying database.
Behind the scenes, SQL Cache Invalidation works by constantly polling the database to check for changes. Every so many milliseconds, the ASP.NET framework checks whether or not there have been any updates 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).
Note Microsoft SQL Server 2005 supports a completely different method of SQL Cache Invalidation. You can configure SQL Server 2005 to notify your ASP.NET application whenever changes have been made to a database, a database table, or a database row. This means that the ASP.NET Framework does not need to constantly poll a SQL Server 2005 database for changes.
It is important to understand that SQL Cache Invalidation only works with Microsoft SQL Server version 7 and higher. You cannot use this feature with other databases such as Microsoft Access or Oracle.
You can use SQL Cache Invalidation when caching the output of an entire page, when working with the DataSource controls, or when working directly with the Cache object. We'll examine all three scenarios.
Configuring SQL Cache Invalidation
Before you can take advantage of SQL Cache Invalidation in your Web application, you must first perform some configuration steps. You must configure Microsoft SQL Server to support SQL Cache Invalidation and you must add the necessary configuration information to your application's Web configuration file.
There are two ways that you can configure SQL Server. You can either use the aspnet_regsql command line tool, or you can take advantage of the SqlCacheDependencyAdmin class.
Enabling SQL Cache Invalidation with ASPNET_REQSQL
The aspnet_regsql tool enables you to configure SQL Cache Invalidation from the command line. The aspnet_regsql tool is located in your Windows\Microsoft.NET\Framework\[version] folder. You must use this tool by opening a command prompt and navigating to this folder.
In order to support SQL Cache Invalidation when using the Pubs database, you need to execute the following command.
aspnet_regsql -E -d Pubs -ed
The -E option causes the aspnet_regsql tool to use integrated security when connecting to your database server. The -d option selects the Pubs database. Finally, the -ed option enables the database for SQL Cache Invalidation.
When you execute this command, a new database table named AspNet_SqlCacheTablesForChangeNotification is added to 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 you enable a database for SQL Cache Invalidation, you must select the particular tables in the database that you will enable for SQL Cache Invalidation. The following command enables the Titles database table.
aspnet_regsql -E -d Pubs -t Titles -et
The -t option selects a database table. The -et option enables a database table for SQL Cache Invalidation. You can, of course, enable multiple tables by re-executing this command for each database table.
When you execute this command, a trigger is added to the database table. The trigger fires whenever you make a modification to the table and it updates the AspNet_SqlCacheTablesForChangeNotification table.
Finally, if you want to get the list of tables that are currently enabled for SQL Cache Invalidation in a particular database, you can use the following command.
aspnet_regsql -E -d Pubs -lt
This method selects the list of tables from the AspNet_SqlCacheTablesForChangeNotification. Alternatively, you could retrieve this information by performing a query directly against this database table.
Using the SqlCacheDependencyAdmin Class
Behind the scenes, the aspnet_regsql tool uses the methods of the SqlCacheDependencyAdmin class to configure Microsoft SQL Server. If you prefer, you can use the methods of this class directly from within an ASP.NET page.
The SqlCacheDependencyAdmin class has five important methods:
- DisableNotifications—Disables SQL Cache Invalidation for a particular database.
- DisableTableForNotifications—Disables SQL Cache Invalidation for a particular table in a database.
- EnableNotifications—Enables SQL Cache Invalidation for a particular database.
- EnableTableForNotifications—Enables SQL Cache Invalidation for a particular table in a database.
- GetTablesEnabledForNotifications—Returns a list of all tables enabled for SQL Cache Invalidation.
For example, the ASP.NET page in Listing 2 enables you to configure SQL Cache Invalidation for any table in the Pubs database (see Figure 2).
.gif)
Figure 2. Enabling SQL Cache Invalidation from an ASP.NET page
Listing 2. EnableSCI.aspx (C#)
<%@ Page Language="c#" %>
<%@ Import Namespace="System.Web.Caching" %>
<script runat="server">
const string connectionString = "Server=localhost;Database=Pubs";
void Page_Load()
{
if (!IsPostBack)
{
SqlCacheDependencyAdmin.EnableNotifications(
connectionString);
SqlDataSource1.SelectParameters.Add("connectionString",
connectionString);
}
}
void EnableTable(Object s, EventArgs e)
{
try
{
SqlCacheDependencyAdmin.EnableTableForNotifications(
connectionString, txtTableName.Text);
}
catch (Exception ex)
{
lblErrorMessage.Text = ex.Message;
}
txtTableName.Text = "";
}
</script>
<html>
<head runat="server">
<title>Enable SQL Cache Invalidation</title>
</head>
<body>
<form id="form1" runat="server">
<h1>SQL Cache Invalidation</h1>
The following tables are enabled for SQL Cache Invalidation:
<p>
<asp:GridView id="grdTables"
DataSourceID="SqlDataSource1" CellPadding="10"
ShowHeader="false" Runat="Server" />
</p>
<asp:ObjectDataSource
ID="SqlDataSource1"
TypeName="System.Web.Caching.SqlCacheDependencyAdmin"
SelectMethod="GetTablesEnabledForNotifications"
Runat="Server" />
<p>
<asp:Label ID="lblErrorMessage" EnableViewState="false"
ForeColor="red" Runat="Server" />
</p>
<asp:TextBox ID="txtTableName" Runat="Server" />
<asp:Button Text="Enable Table" OnClick="EnableTable"
Runat="Server" />
</form>
</body>
</html>
Listing 2. EnableSCI.aspx (Visual Basic .NET)
<%@ Page Language="vb" %>
<%@ Import Namespace="System.Web.Caching" %>
<script runat="server">
Const connectionString As String = "Server=localhost;Database=Pubs"
Sub Page_Load()
If Not IsPostBack Then
SqlCacheDependencyAdmin.EnableNotifications( _
connectionString)
SqlDataSource1.SelectParameters.Add("connectionString", _
connectionString)
End If
End Sub
Sub EnableTable(ByVal s As Object, ByVal e As EventArgs)
Try
SqlCacheDependencyAdmin.EnableTableForNotifications( _
connectionString, txtTableName.Text)
Catch ex As Exception
lblErrorMessage.Text = ex.Message
End Try
txtTableName.Text = ""
End Sub
</script>
<html>
<head id="Head1" runat="server">
<title>ConfigureSCI</title>
</head>
<body>
<form id="form1" runat="server">
<h1>SQL Cache Invalidation</h1>
The following tables are enabled for SQL Cache Invalidation:
<p>
<asp:GridView id="grdTables" DataSourceID="SqlDataSource1"
CellPadding="10" ShowHeader="false" Runat="Server" />
</p>
<asp:ObjectDataSource
ID="SqlDataSource1"
TypeName="System.Web.Caching.SqlCacheDependencyAdmin"
SelectMethod="GetTablesEnabledForNotifications"
Runat="Server" />
<p>
<asp:Label ID="lblErrorMessage" EnableViewState="false"
ForeColor="red" Runat="Server" />
</p>
<asp:TextBox ID="txtTableName" Runat="Server" />
<asp:Button ID="Button1" Text="Enable Table"
OnClick="EnableTable" Runat="Server" />
</form>
</body>
</html>
In Listing 2, the connectionString constant is used to select the database for which SQL Cache Invalidation is enabled (You can change the value of this constant when you want to enable SQL Cache Invalidation for a database other than the Pubs database). Within the Page_Load method, the EnableNotifications method on the SqlCacheDependencyAdmin class is called to enable SQL Cache Invalidation for the database specified by the connectionString constant.
The GridView in Listing 2 displays all of the database tables that are currently enabled for SQL Cache Invalidation. The GridView is bound to an ObjectDataSource control that calls the GetTablesneabledForNotifications method for its SelectMethod.
Finally, you can use the page in Listing 2 to enable additional tables for SQL Cache Invalidation. When you enter the name of a table in the textbox and click the Enable Table button, the EnableTableForNotifications method is called.
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 your Web configuration file. You need to configure the ASP.NET framework to poll the databases that you have enabled for SQL Cache Invalidation.
The Web configuration file in Listing 3 contains the necessary configuration information to poll the Pubs database.
Listing 3. Web.Config
<configuration>
<connectionStrings>
<add name="mySqlServer"
connectionString="Server=localhost;Database=Pubs" />
</connectionStrings>
<system.web>
<caching>
<sqlCacheDependency enabled="true">
<databases>
<add
name="Pubs"
connectionStringName="mySqlServer"
pollTime="60000" />
</databases>
</sqlCacheDependency>
</caching>
</system.web>
</configuration>
The Web configuration file in Listing 3 contains two sections. The <connectionStrings> section is used to create a database connection string to the Pubs database named mySqlServer.
The caching section is used to configure the SQL Cache Invalidation polling. Within the <databases> subsection, you can list one or more databases that you want to poll for changes. In Listing 3, the database represented by the mySqlServer is polled once a minute (every 60000 milliseconds).
You can specify different polling intervals for different databases. 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, then you can increase the polling interval.
Using SQL Cache Invalidation with Page Output Caching
Now that we've gotten all of the configuration steps for SQL Cache Invalidation out of the way, we can start taking advantage of it in our ASP.NET pages. One way that you can use SQL Cache Invalidation is with page output caching. Page output caching enables you to cache the entire rendered contents of a page in memory. By taking advantage of SQL Cache Invalidation, you can automatically update the cached page when, and only when, a change is made to a database table.
For example, the page in Listing 4 displays the contents of the Titles database table in a GridView control. At the top of the page, the OutputCache directive is used to cache the contents of the page in memory. The SqlDependency attribute causes the page to be updated whenever the Titles database table changes.
Listing 4. OutputCacheTitles.aspx
<%@ OutputCache SqlDependency="Pubs:Titles"
Duration="6000" VaryByParam="none" %>
<html>
<head runat="server">
<title>Output Cache Titles</title>
</head>
<body>
<form id="form1" runat="server">
<%= DateTime.Now %>
<asp:GridView
ID="grdTitles"
DataSourceID="SqlDataSource1"
Runat="Server" />
<asp:SqlDataSource
ID="SqlDataSource1"
SelectCommand="Select * FROM Titles"
ConnectionString="<%$ ConnectionStrings:mySqlServer %>"
Runat="Server" />
</form>
</body>
</html>
Notice that the SqlDependency attribute references the name of the database defined within the Web configuration file. Since we specified that the Pubs database should be polled once every minute for changes, if a change is made to the database the page in Listing 4 will be updated within a minute.
You can list more than one database and/or more than one database table for the value of the SqlDependency attribute. To create more than one dependency, simply separate each dependency with a semicolon.
Using SQL Cache Invalidation with the DataSource Control
As an alternative to using SQL Cache Invalidation with page output caching, you can use SQL Cache Invalidation directly with the DataSource controls. You should consider using SQL Cache Invalidation with the DataSource controls when you need to work with the same database data in multiple pages. The SqlDataSource, AccessDataSource, and ObjectDataSource controls all support a SqlCacheDependency property.
For example, the page in Listing 5 uses the SQL Cache Invalidation with the SqlDataSource control.
Listing 5. SqlDataSourceCaching.aspx
<html>
<head id="Head1" runat="server">
<title>SqlDataSource Caching</title>
</head>
<body>
<form id="form1" runat="server">
<%= DateTime.Now %>
<asp:GridView
ID="grdTitles"
DataSourceId="SqlDataSource1"
Runat="server" />
<asp:SqlDataSource
ID="SqlDataSource1"
EnableCaching="true"
SqlCacheDependency="Pubs:Titles"
SelectCommand="select * from titles"
ConnectionString="<%$ ConnectionStrings:mySqlServer %>"
Runat="server" />
</form>
</body>
</html>
In Listing 5, the SqlDataSource control is declared with both an EnableCaching attribute and a SqlCacheDependency attribute. The SqlCacheDependency property uses the same syntax as the OutputCache directive's SqlDependency attribute. You list the name of the database, followed by the name of the database table.
Using SQL Cache Invalidation with the Cache Object
A final option is to use SQL Cache Invalidation with the Cache object. This option provides you with the greatest degree of programmatic control over SQL Cache Invalidation.
To use SQL Cache Invalidation with the Cache object, you need to create an instance of the SqlCacheDependency object. You can use the SqlCacheDependency object when inserting a new object into the Cache with the Insert method.
For example, the page in Listing 6 displays the number of records in the Titles database table. The count is cached with a dependency on the underlying database table.
Listing 6. DisplayTitleCount.aspx (C#)
<%@ Page Language="c#" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script runat="server">
void Page_Load()
{
int count = 0;
if (Cache["TitleCount"] != null)
{
count = (int)Cache["TitleCount"];
}
else
{
string connectionString =
ConfigurationSettings.ConnectionStrings[
"mySqlServer"].ConnectionString;
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new
SqlCommand("SELECT Count(*) FROM Titles", con);
con.Open();
count = (int)cmd.ExecuteScalar();
con.Close();
Cache.Insert("TitleCount", count,
new SqlCacheDependency("Pubs", "Titles"));
}
lblTitleCount.Text = count.ToString();
}
</script>
<html>
<head runat="server">
<title>Display Title Count</title>
</head>
<body>
<form id="form1" runat="server">
<asp:Label ID="lblTitleCount" Runat="Server" />
</form>
</body>
</html>
Listing 6. DisplayTitleCount.aspx (Visual Basic .NET)
<%@ Page Language="vb" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script runat="server">
Sub Page_Load()
Dim count As Integer = 0
If Not Cache("TitleCount") Is Nothing Then
count = Convert.ToInt32(Cache("TitleCount"))
Else
Dim connectionString As String = _
ConfigurationSettings.ConnectionStrings( _
"mySqlServer").ConnectionString
Dim con As New SqlConnection(connectionString)
Dim cmd As New _
SqlCommand("SELECT Count(*) FROM Titles", con)
con.Open()
count = Convert.ToInt32(cmd.ExecuteScalar())
con.Close()
Cache.Insert("TitleCount", count, _
new SqlCacheDependency("Pubs", "Titles"))
End If
lblTitleCount.Text = count.ToString()
End Sub
</script>
<html>
<head id="Head1" runat="server">
<title>Display Titles Count</title>
</head>
<body>
<form id="form1" runat="server">
<asp:Label ID="lblTitleCount" Runat="Server" />
</form>
</body>
</html>
Using Post-Cache Substitution
There are many situations in which you need to cache part of a page, but not the entire page. For example, on the home page of your Web site, you might want to display both a random banner advertisement and the records from a database table. If you cache the entire page, then every user will see the same banner advertisement on each and every page request.
One way to handle this problem of mixing dynamic and cached content is to use Web User Controls. Since you can add an OutputCache directive to a Web User Control, you can cache the contents of a Web User Control even when you do not cache the contents of the containing page.
Sometimes, however, this gets things backwards. You can use Web User Controls to add cached content to a dynamic page. In many situations, however, what you really want to do is add dynamic content to a cached page. For example, imagine that you want to cache the entire contents of a page, except for one little area where you want to display the current user's username. This is a perfect situation in which you'll want to take advantage of post-cache substitution.
The ASP.NET 2.0 framework introduces a new control called the Substitution control. You can use the Substitution control to inject dynamic content into a cached page. The page in Listing 7 uses the Substitution control to inject a username into cached content (see Figure 3).
.gif)
Figure 3. Displaying a username with the Substitution control
Listing 7. PostCacheSubstitution.aspx (C#)
<%@ Page Language="C#" %>
<%@ OutputCache Duration="6000" VaryByParam="none" %>
<script runat="server">
static string DisplayUsername(HttpContext context)
{
if (!context.Request.IsAuthenticated)
return "Anonymous";
else
return context.User.Identity.Name;
}
</script>
<html>
<head runat="server">
<title>Post Cache Substitution</title>
</head>
<body>
<form id="form1" runat="server">
Welcome <asp:Substitution
MethodName="DisplayUsername" Runat="Server" />!
<p>
This page is cached as you'll notice from the fact that the time
<%= DateTime.Now.ToString("t") %> never changes.
</p>
</form>
</body>
</html>
Listing 7. PostCacheSubstitution.aspx (Visual Basic .NET)
<%@ Page Language="vb" %>
<%@ OutputCache Duration="6000" VaryByParam="none" %>
<script runat="server">
Shared Function DisplayUsername(ByVal context As HttpContext) _
As String
If Not context.Request.IsAuthenticated Then
Return "Anonymous"
Else
Return context.User.Identity.Name
End If
End Function
</script>
<html>
<head id="Head1" runat="server">
<title>Post Cache Substitution</title>
</head>
<body>
<form id="form1" runat="server">
Welcome <asp:Substitution
ID="Substitution1"
MethodName="DisplayUsername"
Runat="Server" />!
<p>
This page is cached as you'll notice from the fact that the time
<%= DateTime.Now.ToString("t") %> never changes.
</p>
</form>
</body>
</html>
The Substitution control has one important property: the MethodName property. The MethodName property is used to represent the method that is called to return the dynamic content. The method called by the Substitution control must be a static method (shared in the Visual Basic .NET world). Furthermore, the method must have one parameter that represents the current HttpContext.
In the ASP.NET 2.0 framework, the AdRotator control has been modified to support post-cache substitution. If you add the AdRotator control to a page that uses the OutputCache directive, the AdRotator control is automatically exempt from its containing page's caching policy.
Conclusion
Caching has a dramatic impact on the performance of database-driven Web applications. Fortunately, the ASP.NET 2.0 framework includes a number of significant new enhancements that make it easier to take advantage of caching in your applications.
The new DataSource controls include properties that make it easy to cache database data in memory. By taking advantage of the DataSource controls, you can retrieve database data and cache the data without writing a single line of code.
The new support for SQL Cache Invalidation enables you to automatically reload database data in the cache whenever the data is modified in the underlying database. This feature provides you with all the performance benefits of caching, without the worries of stale data.
Finally, the new Substitution control enables you to more easily mix dynamic content in a cached page. The Substitution control gives you an island of dynamic content in an otherwise cached page.
Related Books
About the author
Stephen Walther wrote the best-selling book on ASP.NET, ASP.NET Unleashed. He was also the architect and lead developer of the ASP.NET Community Starter Kit, a sample ASP.NET application produced by Microsoft. He has provided ASP.NET training to companies across the United States, including NASA and Microsoft, through his company Superexpert (http://www.superexpert.com).
.gif)
Copy Code.gif)
RECENT COMMENT