Enabling Database Cache InvalidationIn 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.
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
Table 11.1. Options for aspnet_regsqlcache.exe
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 InvalidationBefore 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
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
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 InvalidationAfter 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
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 InvalidationTo 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
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. |