Figure 12-4: Use the Advanced settings on the connection to set the Application Name to SqlCacheDependency, which will make it easier to monitor in the SQL Profiler.
ASP.NET offers various forms of data caching, but invalidating the cache has always been the biggest problem when you cache data from a backend database. Now you have an option to invalidate the cache by using a polling technique, which works on SQL Server 7 and later, or by using query notification, which works only on SQL Server 2005. The rest of this chapter looks at both techniques.
Although cache invalidation by polling works on SQL Server 7 and later, you will want to poll SQL Server only if you have SQL Server 7 or SQL Server 2000. If you have SQL Server 2005, you should skip ahead to the later section on query notification. My general feeling about techniques that use polling instead of being event driven is that they tend to be a bit clunky, although they work well.
To configure polling, you configure each SQL Server database that will be polled, and then configure each table that will be polled. We'll configure the Customers table in the Northwind database in our example. We'll use the aspnet_regsql.exe utility, which is run from the Visual Studio 2005 Command Prompt. Configure the Northwind database on the local default SQL Server instance by issuing the following command.
aspnet_regsql.exe -S "." -E -d "Northwind" -ed
This command needs to be executed only once for each database, and it displays a message stating that the database has been enabled. If you look at the Northwind database, you'll see that this command adds a new table called AspNet_SqlCacheTablesForChangeNotification to the database. It has three columns: tableName, notificationCreated, and changeId. It has no rows yet. This command also adds the following stored procedures to the database.
AspNet_SqlCachePollingStoredProcedure Selects tableName and changeId from the AspNet_SqlCacheTablesForChangeNotification table.
AspNet_SqlCacheQueryRegisteredTablesStoredProcedure Selects tableName from the AspNet_SqlCacheTablesForChangeNotification table.
AspNet_SqlCacheRegisterTableStoredProcedure Performs the table registration, which enables cache notification. You use the aspnet_regsql.exe utility to execute this stored procedure after the database has been registered. This stored procedure adds a row to the AspNet_SqlCacheTablesForChangeNotification table and adds a trigger to the table that is being registered.
AspNet_SqlCacheUnRegisterTableStoredProcedure Undoes everything the AspNet_SqlCacheRegisterTableStoredProcedure did when the table was registered by removing the table trigger and removing the row from the AspNet_SqlCacheTablesForChangeNotification table.
AspNet_SqlCacheUpdateChangeIdStoredProcedure Accepts a tableName parameter and increments its changeId in the AspNet_SqlCacheTablesForChangeNotification table.
| Note | The aspnet_regsql.exe utility is used to configure other SQL Server functionality, such as membership, personalization, and session state. If you run this utility without parameters, a wizard will start that takes you through the configuration of some of the options. Be sure to use the -? parameter to see all of the available options. |
To configure the Customers table for cache invalidation, use the following command, which executes AspNet_SqlCacheRegisterTableStoredProcedure.
aspnet_regsql.exe -S "." -E -d "Northwind" -t "Customers" -et
This command adds a row to the AspNet_SqlCacheTablesForChangeNotification table and also adds the following trigger to the Customers table.
SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON GO ALTER TRIGGER [dbo].[Customers_AspNet_SqlCacheNotification_Trigger] ON [dbo].[Customers] FOR INSERT, UPDATE, DELETE AS BEGIN SET NOCOUNT ON EXEC dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedure N'Customers' END
This trigger fires on any customer INSERT, UPDATE, or DELETE statement and simply increments the changeId of the table.
Now that the database server is configured for polling, it's time to set up a Web site that uses it. Create a Web site project called SqlCacheDependency. Add a Button control and a GridView control to the form. Set the Text property of the Button control to "Refresh".
Use the GridView Tasks window to configure a new data source. Use the SQL Server instance that you configured for polling and name the connection string NorthwindConnectionString. In the Advanced options for the connection string, set the Application Name property to SqlCacheDependency (as shown in Figure 12-4), which will make it easier for you to view this application in the SQL Profiler utility.
Figure 12-4: Use the Advanced settings on the connection to set the Application Name to SqlCacheDependency, which will make it easier to monitor in the SQL Profiler.
To keep this example simple, select the Customers table, but only select the CustomerID and the CompanyName fields. In the Advanced options, select the option that automatically generates the INSERT, UPDATE, and DELETE statements. Use the GridView Tasks window to enable editing. Finally, set the GridView control's EnableViewState property to false.
To test the application, start the SQL Server Profiler by clicking Start | All Programs | Microsoft SQL Server 2005 | Performance Tools | SQL Server Profiler and create a new SQL trace. In the Trace Properties window of the new trace, click the Events Selection tab, and then click the Column Filters button. In the ApplicationName filter, add SqlCacheDependency under the Like setting (as shown in Figure 12-5), and then click Run to start the trace.
Figure 12-5: Add the SqlCacheDependency to the ApplicationName filter of the new trace.
Next, run the Web application. Notice that every time you click the Refresh button, the SELECT statement is executed. You can see the results in the SQL Profiler trace window.
To enable polling in the Web application, you must modify the Web.config file by adding the following settings.
Web.config File Settings for Polling
<system.web> <caching> <sqlCacheDependency enabled="true" pollTime="3600000" > <databases> <add name="NW" connectionStringName="NorthwindConnectionString" pollTime="60000" /> </databases> </sqlCacheDependency> </caching> </system.web>
I intentionally placed two pollTime attributes in these settings just to make sure that you know you can set this twice. The first pollTime attribute defines the default pollTime if you don't identify a time at the database level. This is set to one hour, which is the 3,600,000-millisecond setting. The second setting is at the specific database, and this setting overrides the default. This is set to 1 minute, which is the 60,000-millisecond setting.
Notice that the connection string setting points to the connection string that was created earlier, which connects to the Northwind database.
The last thing to do is to set up the SqlDataSource object to use caching and to use the polling you configured. Click the SqlDataSource1 object on your Web form, modify the properties by enabling caching, and set SqlCacheDependency property to NW:Customers, as shown in Figure 12-6.
Figure 12-6: Enable caching and set the SqlCacheDependency on the SqlDataSource1 object.
Clear the SQL Profiler trace window by clicking the Clear Trace Window button (or Ctrl+Shift+Del). Run the application, and you'll see that the cache was filled by calling the SELECT statement. You can click the Refresh button on the Web browser many times, but you won't see a call to the SELECT statement. Once a minute, you will see a call to the stored procedure named dbo.AspNet_SqlCachePollingStoredProcedure, which retrieves the table names and their change ID numbers for all tables that are being polled in the Northwind database. If this change ID has changed on any table since the data was updated, the cache is invalidated and the next request for this data results in issuing the SELECT statement to the database.
Try opening SQL Server Management Studio and changing one of the company names. Then watch the SQL Profiler window until you see the call to the dbo.AspNet_SqlCachePollingStoredProcedure stored procedure. Refresh your Web page, and you should see that the SELECT statement was issued to the database and the change shows on the Web page.
One last thing: in addition to enabling caching on the SqlDataSource object, you can use the output cache directive on the Web form as follows.
<%@ OutputCache Duration="999999" SqlDependency="NW:Customers" VaryByParam="none" %>
This caches all of the HTML output for this Web form until the data changes. Because you want to use SqlDependency instead of using time to expire the cache, the Duration attribute is set to the maximum value of 999,999.
The command notification mechanism uses the SqlDependency class that was defined at the beginning of this chapter to receive change notifications when the result of a command changes. Command notification is also known as change notification and is supported only on SQL Server 2005.
Command notification is simple to set up, but you have to first perform all of the setup explained earlier in the "Using the SqlDependency Object" section of this chapter.
There is no need to register the <sqlCacheDependency> in the Web.config file, and you don't need to use the aspnet_regsql.exe utility to set up anything.
The command notification dependency is configured with the OutputCache directive, using the string CommandNotification. This value indicates to ASP.NET that a command notification dependency should be created for the page or SqlDataSource object.
This example uses the pubs database so you can be certain you are working with a fresh database. The following script can be run in SQL Server Management Studio to enable command notification on the pubs database. Note that if you have been executing the scripts in the "Using the SqlDependency Object" section of this chapter, you need to execute only the last line, which enables the service broker on the pubs database.
SQL: Enabling Command Notification
USE MSDB GRANT SEND ON SERVICE:: [http://schemas.microsoft.com/SQL/Notifications/QueryNotificationService] GO USE MASTER EXEC sp_configure 'clr enabled', 1 RECONFIGURE ALTER DATABASE Pubs SET ENABLE_BROKER
Now that the database server is configured for command notification, it's time to set up a Web site that uses it. Create a Web site project, and add a Button control and a GridView control to the form. Set the Button control's Text property to "Refresh".
Use the GridView Tasks window to configure a new data source. Use the SQL Server instance that you configured for polling, and name the connection string PubsConnectionString. In the Advanced options for the connection string, set the Application Name property to CommandNotification, which will make it easier for you to view this application in the SQL Profiler utility. To keep this example simple, select the Stores table, but select only the stor_id and the stor_name fields. In the Advanced options, select the option that automatically generates the insert, update, and delete statements. Use the GridView Tasks window to enable editing. Finally, set the GridView control's EnableViewState property to false.
Start the SQL Profiler and create a new SQL trace. In the Trace Properties window of the new trace, click the Events Selection tab, and then click the Column Filters button. In the ApplicationName filter, add CommandNotification under the Like setting, and then click Run to start the trace.
As with the polling application, when you run the Web application, every time you click the Refresh button, the SELECT statement is executed. You can see the results in the SQL Profiler trace window.
You don't need to modify the Web.config file. All you need to do is set up the SqlDataSource to use the caching you configured. Click the SqlDataSource1 object on your Web form, modify the properties by enabling caching, and set SqlCacheDependency to CommandNotification , as shown in Figure 12-7.
Figure 12-7: Enable caching and set the SqlCacheDependency on the SqlDataSource1 object.
You must make one more property change to the SqlDataSource1 object. As with the SqlDependency class described at the beginning of this chapter, you must change the SELECT query to use a two-part name for the table by adding the dbo prefix to the Stores table in the SELECT statement. You can do this in the properties window by clicking the SelectQuery property and editing the query as shown in Figure 12-8.
Figure 12-8: Command notification requires the use of two-part names to identify the table.
Clear the SQL Profiler trace window and run the application. You will see that the cache was filled by calling the SELECT statement. You can click the Refresh button on the Web browser many times, but you won't see a call to the SELECT statement.
Try opening SQL Server Management Studio and changing one of the company names. Then refresh your Web page, and you should see that the SELECT statement was issued to the database. The change shows on the Web page.
Just as with polling, in addition to enabling the caching on the SqlDataSource object, you can also use the output cache directive on the Web form as follows.
<%@ OutputCache Duration="999999" SqlDependency="CommandNotification" VaryByParam="none" %>
This caches all of the HTML output for this Web form until the data changes. Because you want to use SqlDependency instead of using time to expire the cache, you set the Duration attribute to the maximum value of 999,999.
