Walkthrough: Using ASP.NET Output Caching with SQL Server

[アーティクル]
10/22/2014

This walkthrough shows how to cache ASP.NET pages that depend on data in a SQL Server database.

An advanced feature of ASP.NET output caching is SQL cache dependency. SQL cache dependency enables you to cache pages that are dependent on data from SQL Server tables. You can configure SQL Server and ASP.NET to cache page requests, reducing server workload, until the data on which the page depends has been updated in SQL Server. SQL cache dependency is useful for data such as product catalogs or customer registration information that remains comparatively static.

Tasks illustrated in this walkthrough include:

Creating and configuring a page to display data from the Northwind database.
Enabling a database for SQL cache notification.
Specifying the SQL cache dependency on your page and in your Web.config file.
Making changes to the Northwind database and viewing the caching behavior.

Prerequisites

In order to complete this walkthrough, you will need:

Access to SQL Server 2000 or SQL Server 2005 with the Northwind database.
Access to the SQL Server Northwind database. For information about downloading and installing the SQL Server sample Northwind database, see Installing Sample Databases on the Microsoft SQL Server Web site.

Note

If you need information about how to log on to the computer running SQL Server, contact the server administrator.
Microsoft Data Access Components (MDAC) version 2.7 or later.

If you are using Windows XP or Windows Server 2003, you already have MDAC 2.7. However, if you are using Windows 2000, you might to need to upgrade the MDAC already installed on your computer. For more information, see the MSDN article MDAC Installation.
The .NET Framework version 2.0.

Creating the Web Site

If you have already created a Web site in Visual Web Developer (see Walkthrough: Creating a Basic Web Page in Visual Web Developer), you can use that Web site and go to "Enabling Cache Notification for SQL Server" later in this walkthrough. Otherwise, create a new Web site and page using the following procedure.

To create a file system Web site

Open Visual Web Developer.
On the File menu, click New Web Site. (In Visual Web Developer Express Edition, on the File menu click New, and then click Web Site.)

The New Web Site dialog box appears.
Under Visual Studio installed templates, click ASP.NET Web Site.
In the Location box, enter the name of the folder where you want to keep the pages of your Web site.

For example, type the folder name C:\WebSites.
In the Language list, click the programming language you prefer to work in.
Click OK.

Visual Web Developer creates the folder and a new page named Default.aspx.

Enabling Cache Notification for SQL Server

You must configure SQL Server to provide proper notification to ASP.NET regarding changes in dependent data. You will need administrative privileges to configure the server.

To enable cache notification for SQL Server

On the Windows Start menu, point to All Programs, point to Accessories, and then click Command Prompt to open a command prompt window.
Locate the Aspnet_regsql.exe executable file on your disk drive. This file is installed with the .NET Framework version 2.0 in the following location:
```
%windir%\Microsoft.NET\Framework\FrameworkVersion
```
Be sure that %windir% represents your Windows directory and that the .NET Framework version is 2.0 or later. The path might look like the following:
```
C:\WINDOWS\Microsoft.NET\Framework\v2.0.40217
```
Use the following command to enable cache notification for the Employees table in the Northwind database:
```
aspnet_regsql.exe -S <Server> -U <Username> -P <Password> -ed -d Northwind -et -t Employees
```
Note

You will need administrative privileges or the administrative account and password. If you do not have this information, contact your database administrator.

A message will appear indicating success or failure in enabling the database. The following message indicates success:
```
Enabling the table for SQL cache dependency.
..
Finished.
```

Adding a Data Connection to the Project

To work with the SQL Server database in Visual Web Developer, you need to add a connection to the Northwind database.

To add a data source to the project

In Server Explorer (Database Explorer in Visual Web Developer Express Edition), right-click Data Connections, and then click Add Connection.

Note

Server Explorer (Database Explorer in Visual Web Developer Express Edition) is typically docked behind Solution Explorer.

If Server Explorer (Database Explorer in Visual Web Developer Express Edition) is not visible, in the View menu, click Server Explorer (Database Explorer in Visual Web Developer Express Edition).
If the Choose Data Source dialog box is displayed, do the following:
1. In the Data source list, click Microsoft SQL Server.
2. In the Data provider list, click .NET Framework Data Provider for SQL Server.
3. Click Continue.
In the Add Connection dialog box, provide the details (server name, login credentials, and so on) for your database, and then select the Northwind database.
Click OK.

Adding a Time Stamp and Data to the Web Page

You can now create a Web page to demonstrate caching. In this section you will add a time stamp to track page creation times and a GridView control to view the Employees table of the Northwind database.

To add a time stamp and data to the Web page

Switch to or open the Default.aspx page.
Switch to Design view.
From the Standard group in the Toolbox, drag a Label control onto the page, leaving the default name of Label1.
In Server Explorer (Database Explorer in Visual Web Developer Express Edition), expand Data Connections.
Expand the node for the data connection you created earlier.
Expand the Tables node.
Drag the Employees table to your page.

Visual Web Designer creates a GridView control that is configured to use the connection and table you selected.
In the GridView Tasks menu, click Configure Data Source.

The default data connection string name NorthwindConnectionString1 appears in the first step of the Configure Data Source wizard.
Click Next.
In the Configure Select Statement pane, select Specify columns from a table or view.
In the Name list, click Employees.
In the Columns list, select the EmployeeID, LastName, and FirstName columns.
Click Next.
Click Finish.

Visual Web Designer configures the GridView control to display the data you have selected.

Note

If you see a message asking whether you want to refresh the fields and keys for the GridView control, click Yes.
Double-click a blank part of the page**.**

The designer generates a Page_Load method and switches views.

Add the following highlighted code to display a time stamp indicating page creation:

Protected Sub Page_Load(ByVal sender As Object, _
        ByVal e As System.EventArgs) _
        Handles Me.Load
     Label1.Text = System.DateTime.Now
End Sub

protected void Page_Load(Object sender, System.EventArgs e)
{
     Label1.Text = System.DateTime.Now.ToString();
}

Save the file.

Testing the Page without Caching

Now you can run the page and observe the behavior without caching. The page is loaded and the current server time is displayed, and then the data is retrieved and placed on the page.

To test the page without caching

Press CTRL+F5 to run the page.
Refresh the page in the browser

Note that the time stamp changes with each page refresh. The data remains the same.

Configuring the Web Page for Caching

In this part of the walkthrough, you will configure the page for SQL cache dependency based on the Employees table of the Northwind database.

To configure the Web page for caching

Switch to Source view.

At the top of the page, add the following directive to indicate the dependency:

<%@ OutputCache Duration="3600" SqlDependency="Northwind:Employees" VaryByParam="none" %>

The VaryByParam attribute indicates whether ASP.NET should take into consideration page parameters (such as query string or posted values) when caching. When VaryByParam is set to none, no parameters will be considered; all users are sent the same page no matter what additional parameters are supplied. Setting VaryByParam to * (an asterisk) means that for each unique combination of request parameters a unique page will be cached. However, setting VaryByParam to * can cause many different versions of the page to be cached, so if you know the parameters to vary caching by, it is recommended that you explicitly specify them in the VaryByParam attribute. For details, see Caching Multiple Versions of a Page.

Setting Caching Configuration in the Web.config File

In addition to the OutputCache declaration on your Web page in the preceding section, you need to specify caching details in the Web.config file.

To create and update the Web.config file

If your Web site already has a Web.config file, go to step 4.
In Solution Explorer, right-click the name of your Web site and then click Add New Item.
In the Add Item dialog box, click Web Configuration File, and then click Add.

Be sure to use the name Web.config.
Add the following XML to the Web.config file as a child of the system.web element:
```

<caching>
  <sqlCacheDependency enabled = "true" pollTime = "1000" >
    <databases>
      <add name="Northwind" 
         connectionStringName="NorthwindConnectionString1"
         pollTime = "1000"
      />
    </databases>
  </sqlCacheDependency>
</caching>
```
Note

The connection string name NorthwindConnectionString1 was established earlier when you created the data connection. If your connection string has a different name, substitute that name instead.

Note

The account credentials specified in the connection string must have sufficient privileges to poll the database.
Save the file and then close it.

Testing the Page with Caching

With caching enabled, refreshing the page will no longer result in an updated time stamp or a database query, because ASP.NET will fulfill the page request from the cache.

To test the page with caching

Press CTRL+F5 to run the page.

Note that the time stamp remains the same with each page refresh. The page is being retrieved from the cache.

Changing the Data

Now you can change the data in the database and see that the cache is invalidated and a new page is created.

To change the data

In Server Explorer (Data Explorer in Visual Web Developer Express Edition), expand the Data Connections node.
Expand the connection you created earlier.
Expand the Tables node.
Right-click Employees, and then click Show Table Data.
Update the database by editing any field in the data table, ensuring it is a field your Web page is displaying.

Alternatively, you can use the SQL pane (if the SQL pane is not visible, in the Query Designer menu, click Pane, and then click SQL). Enter a SQL command directly and then click the Execute SQL button in the Query Designer toolbar. For example, run the following command:
```
UPDATE dbo.Employees SET LastName = 'Doe' WHERE (EmployeeID = 5)
```
This changes the name of employee 5 to Doe.
Close the view of the data.

Testing the SQL Cache Dependency

You can now test the page to see whether the data and timestamp have changed.

To test SQL cache dependency

Press CTRL+F5 to run the page.

Note that the time stamp has changed and that the new data is displayed.
Press CTRL+F5 to run the page again.

This time the time stamp remains the same, because the data has not changed, and the page is now being retrieved once again from the cache.

Next Steps

You can cache pages using other parameters as well. For information, see Walkthrough: Using Output Caching to Enhance Web Site Performance. If you are not familiar with data access, see Walkthrough: Basic Data Access in Web Pages.