Combine ASP. NET output cache with SQL Server

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

An advanced function of ASP. net output cache is the SQL cache dependency. The SQL cache dependency allows you to cache pages that depend on data in SQL Server tables. You can configure SQL Server and ASP. NET to cache page requests to reduce server workload until the page dependent data has been updated in SQL Server. SQL cache dependencies are useful for data that maintains static product directories or customer registration information.

The tasks described in this walkthrough include:

• Create and configure pages to display data in the northwind database.

• Enable database for SQL cache notifications.

• Specify the SQL cache dependency in the page and the web. config file.

• Modify the northwind database and view the cache behavior.

Prerequisites

To complete this drill, you must:

• Access to SQL Server 2000 or SQL Server 2005 with a northwind database.

• The access permission of the SQL Server northwind database. For information on downloading and installing the SQL Server Sample Database northwind, see "installing sample databases" on the Microsoft SQL Server website ).

  Note:
  If you need information about how to log on to the computer that runs SQL Server, contact the server administrator.

• Microsoft Data Access Component (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 may need to upgrade the MDAC installed on your computer. For more information, see msdnArticle"MDAC installation" (MDAC installation ).

• . NET Framework 2.0.

Create a website

If you have created a website in Visual Web Developer (see Walkthrough: Create a base page in Visual Web Developer ), you can use this website and go to the "enable cache notification for SQL Server" section after this walkthrough ". Otherwise, follow the steps below to create a new website and webpage.

Create a file system website

1. Open visual web developer.

2. On the File menu, click New website ". (In Visual Web Developer quick release, click New on the File menu, and then click website ".)

   The "New Website" dialog box appears.

3. Under Visual Studio installed templates, click ASP. NET Website ".

4. In the "location" box, enter the name of the folder to save the website page.

   For example, enter the folder name c: \ websites.

5. In the language list, clickProgramming Language.

6. Click OK ".

   Visual Web Developer creates this folder and a new page named default. aspx.

Enable cache notification for SQL Server

You must configure SQL server to provide appropriate notifications to ASP. NET for changes in dependent data. You must have administrative privileges to configure the server.

Enable cache notification for SQL Server

2. In the windows "start" menu, point to "allProgram", Pointing to" attachment ", and then clicking" command prompt "to open the Command Prompt window.

4. Find the aspnet_regsql.exe executable file in the disk drive. This file comes. install Net Framework 2.0 in the following location:

   copy the Code

    % WinDir % \ Microsoft. net \ framework \ frameworkversion 

   make sure % WINDIR % indicates the Windows directory and.. NET Framework Version 2.0 or later. The path may look like the following:

   copy the Code

    C: \ WINDOWS \ Microsoft. net \ framework \ v2.0.40217 

6. Use the following command to enable cache notification for the employees table in the northwind database:

   Copy code

   Aspnet_regsql.exe-S <Server>-u <username>-P <password>-ed-D northwind-et-T employees

   Note:
   You must have administrative privileges, or manage accounts and passwords. If you do not have this information, contact the database administrator.

   A message indicating the success or failure of enabling the database appears. The following message indicates success:

   Copy code

   Enabling the table for SQL cache dependency... finished.

Add a data connection to a project

To use the SQL Server database in Visual Web Developer, you need to add a connection to the northwind database.

Add a data source to a project

1. In server resource manager, right-click data connection, and then click Add connection ".

   Note:
   "Server resource manager" ("Database Resource Manager" in the quick release of Visual Web Developer) is usually docked behind solution Resource Manager.

   If "server resource manager" ("Database Resource Manager" in quick release of Visual Web Developer) is invisible, click "server resource manager" in the "View" menu ("Database Resource Manager" in Visual Web Developer quick release ").

2. If the "Select data source" dialog box is displayed, perform the following operations:

   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 ".

3. In the "add connection" dialog box, provide detailed information (server name, login creden。, etc.) for the database, and then select the northwind database.

4. Click OK ".

Add Timestamp and data to a webpage

You can now create a webpage to demonstrate cache processing. In this section, you will add a time stamp to track the page creation time and add a gridview control to view the Employees table of the northwind database.

Add Timestamp and data to a webpage

2. Switch to or open the default. aspx page.

4. Switch to the design view.

6. Drag a label control from the "standard" group of the toolkit to the page and retain the default nameLabel1.

8. In "server resource manager" ("Database Resource Manager" for Visual Web Developer quick release), expand "Data Connection ".

10. Expand nodes for previously created data connections.

12. Expand the "table" node.

14. Drag the Employees table to the page.

    Visual Web designer createsGridviewControl, which is configured to use the selected connection and table.

16. On the "gridview task" menu, click "configure data source.

    Default data connection string nameNorthwindconnectionstring1Appears in the first step of the "configure data source" Wizard.

18. Click "Next ".

20. In the "Configure SELECT statement" pane, select "specify columns from tables or views ".

22. In the name list, click employees.

24. In the "column" list, select the columns employeeid, lastname, and firstname.

26. Click "Next ".

28. Click Finish ".

    Visual Web designer ConfigurationGridviewControl to display the selected data.

    Note:
    If you see a message asking if you want to refreshGridviewControl fields and keys, and click "yes ".

30. Double-click the blank area of the page.

    Designer generationPage_loadMethod and switch the view.

31. Add the following highlightedCodeTo display the timestamp created on the page:

    Copy code in Visual Basic

    Protected SubPage_load (ByvalSenderAs Object,_ByvalEAsSystem. eventargs )_Handles Me. Load <B> label1.text = system. datetime. Now </B>End Sub
     

     

    C # copy code

    Protected VoidPage_load (Object sender, system. eventargs e) {<B> label1.text = system. datetime. Now. tostring (); </B>}
     

33. Save the file.

Test pages that do not use the cache Function

Now you can run the page and observe the behavior of not using the cache function. At this time, the page is loaded and the current server time is displayed. Then, the data is retrieved and placed on the page.

Test pages that do not use the cache Function

1. Press Ctrl + F5 to run the page.

2. Refresh the page in the browser

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

Configure web pages for the cache Function

In this part of the walkthrough, you will configure the SQL cache dependency Page Based on the employees table of the northwind database.

Configure web pages for the cache Function

1. Switch to the "Source" view.

2. At the top of the page, add the following command to indicate the dependency:

   Copy code

   <% @ Outputcache duration = "3600" sqldependency = "northwind: employees" varybyparam = "NONE" %>

VarybyparamAttribute indicates whether ASP. NET should consider page parameters (such as query strings or publish values) during cache ). WhenVarybyparamSetNoneNo parameters are taken into account. No matter what additional parameters are provided, the same page will be sent to all users. SetVarybyparamSet*(Asterisk) indicates that a unique page is cached for each unique combination of request parameters. HoweverVarybyparamSet*Many different versions of the page will be cached. Therefore, if you know the parameters for cache changes, we recommend that youVarybyparamExplicitly specify these parameters in the property. For more information, see multiple versions on the cache page.

Set cache configuration in the web. config file

Except forOutputcacheYou must specify the cache details in the web. config file.

Create and update the web. config file

2. If your website already has a web. config file, go to step 4.

4. In Solution Explorer, right-click the website name and click "Add new project ".

6. In the "add item" dialog box, click "Web configuration file", and then click "add ".

   Make sure that the name web. config is used.

8. Use the following XMLSystem. WebAdd the child level of the element to the Web. config file:

   Copy code

   <! -- Caching section group --> <caching> <sqlcachedependency enabled = "true" polltime = "1000"> <databases> <Add name = "northwind" connectionstringname = "northwindconnectionstring1" polltime =" 1000 "/> </databases> </sqlcachedependency> </caching>

   Note:
   You have created a connection string name when creating a data connection.Northwindconnectionstring1. If your connection string has different names, replace them with this name.

   Note:
   The account creden。 specified in the connection string must have sufficient privileges to poll the database.

10. Save the file and close it.

Pages for testing the cache Function

When cache is enabled, page refreshing will no longer result in timestamp or database query updates, because ASP. NET will meet page requests from the cache.

Pages for testing the cache Function

• Press Ctrl + F5 to run the page.

  Note that the timestamp remains unchanged every time the page is refreshed. The page will be retrieved from the cache.

Change Data

Now you can change the data in the database, see that the cache is verified, and create a new page.

Change Data

1. In "server resource manager" (in the quick release of Visual Web Developer, "Database Resource Manager"), expand the "Data Connection" node.

2. Expand the connection you created earlier.

3. Expand the "table" node.

4. Right-click "employees" and click "show table data ".

5. Update the database by editing any field in the data table and ensuring that it is a field displayed on the webpage.

   Alternatively, you can use the SQL pane (if the SQL pane is invisible, click the pane in the query designer menu, and then click SQL "). Directly enter the SQL command, and then click the Execute SQL button in the "query designer" toolbar. For example, run the following command:

   Copy code

   Update DBO. Employees set lastname = 'doe 'Where (employeeid = 5)

   This will change employee 5's name to doe.

6. Disable the data view.

Test SQL cache Dependencies

Now you can test the page to check whether the data and timestamp have been changed.

Test SQL cache Dependencies

1. Press Ctrl + F5 to run the page.

   Note that the timestamp has been changed and new data is displayed.

2. Press Ctrl + F5 to run the page again.

   The timestamp remains unchanged because the data is not changed and the page is being retrieved from the cache again.

Subsequent steps

You can also use other parameters to cache pages. For more information, see Walkthrough: Use the output cache to enhance website performance. If you are not familiar with data access, see Drill: basic data access on the webpage.

See

Reference

Defaulthttpcachepolicy
Outputcachesettingssection
Concept

Use the sqlcachedependency class to cache data in ASP. NET