Enabling Database Cache Invalidation

Enabling Database Cache Invalidation

In the Technology Preview release of ASP.NET 2.0, the command-line tool aspnet_regsql.exe is used to configure databases for SQL cache invalidation. In the beta release, this tool will likely no longer exist and will be replaced with aspnet_regsqlcache.exe , a combination command-line and GUI tool. Additionally, in the beta release you will also be able to use the new Web Administration Tool (see Chapter 6) to configure SQL Server cache invalidation. Note that this tool is only required to enable Microsoft SQL Server 7 and Microsoft SQL Server 2000 databases. Microsoft SQL Server "Yukon" has implicit support for database change notification.

IMPORTANT

You can also use the SqlCacheDependencyAdmin class in the System .Web.Caching namespace to enable the database and tables for Microsoft SQL Server 7 and 2000 databases. This functionality is not examined in this book.

To use aspnet_regsqlcache.exe , first open a command prompt window and navigate to the installation directory of the .NET Framework:

 \Windows\Microsoft.NET\Framework\v1.2.30609\ 

Note that your version-specific directory ( \v1.2.30609\ ) will differ . Use the highest number available.

Next, type aspnet_regsqlcache , as shown in Figure 11.1.

Figure 11.1. Use of aspnet_regsqlcache.exe

graphics/11fig01.gif

Table 11.1. Options for aspnet_regsqlcache.exe

Flag

Description

-?

Displays a help listing of the various flags supported by the tool.

-S

Names the SQL Server to connect to. This can be either the computer name or the IP address.

-U

Names the user to connect as when using SQL Server Authentication (e.g., the SQL Server administrator account, sa ).

-P

Used in conjunction with the U flag to specify the user's password.

-E

Connects to the SQL Server when using Windows Authentication and the current user has administrator capabilities on the database. The U and P flags are not needed when using E .

-t

Specifies the table to apply necessary changes for SQL Server cache invalidation to.

-d

Specifies the database to apply changes for SQL Server cache invalidation to.

-ed

Enables a database for SQL cache dependency.

-dd

Disables a database for SQL cache dependency.

-et

Enables a table for SQL cache dependency.

-dt

Disables a table for SQL cache dependency.

-lt

Lists all tables enabled for SQL cache dependency.

The command-line tool options should be familiar if you have used command-line SQL tools such as osqlw.exe . Table 11.1 describes the various options in more detail. Note that the tool is case-sensitive.

The following subsections contain some sample sessions using aspnet_regsqlcache.exe .

Enabling a Database for SQL Cache Invalidation

Before a database table can participate in SQL cache invalidation, both the database and table must be enabled. To enable a database on the same machine:

 aspnet_regsqlcache.exe U [  user  ] P [  password  ] d [  database  ] ed 
Table 11.2. Columns of AspNet_SqlCacheTablesForChangeNotification

Column

Description

tableName

Stores the name of all tables in the current database capable of participating in change notifications.

notificationCreated

Sets the timestamp indicating when the table was enabled for notifications.

changeId

Sets the numeric change ID incremented when a table is changed.

In Figure 11.2 we assume that SQL Server is running on the same machine and we specify a user sa with the password 00password on the Northwind database. This creates a new table named AspNet_SqlCacheTables ForChangeNotification.

Figure 11.2. Enabling a database for SQL cache invalidation

graphics/11fig02.gif

This new table contains the columns shown in Table 11.2.

Now that the database is enabled for change notifications, we need to enlist tables that we wish to watch for changes.

Enabling a Table for SQL Cache Invalidation

After we enable the database for change notifications, we need to enlist selected tables for change notifications. For example, if we desire to enable the Customers, Employees, and Products tables in the Northwind database, we execute aspnet_regsqlcache.exe with the following parameters:

 aspnet_regsqlcache.exe U [  user  ] P [  password  ]                        d [  database  ] t [  table  ] et 

In Figure 11.3 we enable the Products table in the Northwind database. This creates a trigger Products_AspNet_SqlCacheNotification_Trigger on the Products table and also adds an entry into the AspNet_SqlCache TablesForChangeNotification table for the Products table. Whenever data within the Products table is updated, inserted, or deleted, the trigger causes the changeId value stored in the AspNet_SqlCacheTablesForChangeNotification table to be incremented.

Figure 11.3. Enabling a table for SQL cache invalidation

graphics/11fig03.gif

We'll see how this all works in conjunction with ASP.NET shortly. Let's first look at how we list all the tables participating in change notifications for a database.

Listing Tables Enabled for SQL Cache Invalidation

To list tables for a particular database that are enabled for change notifications, use this code:

 aspnet_regsqlcache.exe U [  user  ] P [  password  ] d [  database  ] lt 

In Figure 11.4 we use the lt flag to retrieve a listing of all the tables in the Northwind database enabled for SQL cache invalidation.

Figure 11.4. Listing tables enabled for SQL cache invalidation

graphics/11fig04.gif

Now that we've enabled the Northwind database and several tables to participate in SQL Server cache invalidation, let's see how we can use this in our ASP.NET application.



A First Look at ASP. NET v. 2.0 2003
A First Look at ASP. NET v. 2.0 2003
ISBN: N/A
EAN: N/A
Year: 2004
Pages: 90

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net