ASP.NET 2.0 Unleashed
| One of the most powerful new features added to the ASP.NET 2.0 Framework is SQL cache dependencies. This feature enables you to reload cached database data automatically whenever the data in the underlying databases changes. There is a tradeoff when you use either an absolute or sliding cache expiration policy. The tradeoff is between performance and stale data. For example, if you cache data in memory for 20 seconds, then the data that is displayed on your web pages might be 20 seconds out of date. In the case of most applications, displaying slightly stale data does not really matter. For example, if you are building a discussion forum, then everyone can live with the fact that new posts might not appear immediately. However, there are certain types of applications in which you cannot afford to display any stale data at all. For example, if you are creating a stock trading website or an auction website, then every second might count. The ASP.NET Framework's support for SQL cache dependencies enables you to take advantage of caching but minimize stale data. When you use a SQL cache dependency, you can automatically detect when data has changed in the underlying database and refresh the data in the cache. The ASP.NET Framework supports two types of SQL cache dependencies: Polling and Push. You can use Polling SQL cache dependencies with any recent version of Microsoft SQL Server, including Microsoft SQL Server 2005 Express, Microsoft SQL Server 2000, and Microsoft SQL Server 7.0. The second type of cache dependency, Push SQL cache dependencies, works with only Microsoft SQL Server 2005 or Microsoft SQL Server 2005 Express because it requires the SQL Server 2005 Service Broker. You can use either type of SQL cache dependencies with Page Output Caching, DataSource Control Caching, and Data Caching. The following sections examine each scenario. Using Polling SQL Cache Dependencies
A Polling SQL cache dependency is the most flexible type of SQL cache dependency, and I recommend that you use Polling rather than Push SQL cache dependencies for most applications. You can use a Polling SQL cache dependency to detect any type of modification to a database table. Behind the scenes, a Polling SQL cache dependency uses a database trigger. When a table is modified, the trigger fires and a row in a database table named AspNet_SqlCacheTablesForChangeNotification is updated to record the fact that the table has been changed. The ASP.NET Framework uses a background thread to poll this database table for changes on a periodic basis. If there has been a change, then any item in the cache that is dependent on the database table is dropped from the cache. If you use a Polling SQL cache dependency, then you can eliminate the majority of your database traffic. Unless a database table changes, the only traffic between your web server and the database server is the query that checks for changes in the AspNet_SqlCacheTablesForChangeNotification table. Because a Polling SQL cache dependency must poll the database for changes, an item cached with a SQL Polling cache dependency won't be dropped from the cache immediately after there is a change in the database. The polling interval determines the staleness of your cached data. You can configure the polling interval to be any value you need. Configuring Polling SQL Cache Dependencies
Before you can use a Polling SQL cache dependency, you must perform two configuration steps:
Let's examine each of these steps. Configuring a Database for Polling SQL Cache Dependencies
You can configure a SQL Server database to support Polling SQL cache dependencies by using a class in the Framework named the SqlCacheDependencyAdmin class. This class has the following methods:
You should not use the SqlCacheDependencyAdmin class in an ASP.NET page because calling the methods of this class requires database permissions to create tables, stored procedures, and triggers. For security reasons, the ASP.NET process should not be given these permissions. Instead, you should use the SqlCacheDependencyAdmin class in a command-line tool. The ASP.NET Framework includes a command-line tool named aspnet_regsql that enables you to configure a database to support Polling SQL cache dependencies. This tool works with Microsoft SQL Server 7.0, Microsoft SQL Server 2000, and Microsoft SQL Server 2005. Unfortunately, the aspnet_regsql command-line tool does not work with a local instance of Microsoft SQL Server 2005 (but we'll fix this limitation in a moment). The aspnet_regsql tool is located in the following folder: c:\Windows\Microsoft.NET\Framework\[version]
Note If you open the SDK Command Prompt from the Microsoft .NET Framework SDK Program group, then you do not need to navigate to the Microsoft.NET folder to execute the aspnet_regsql command-line tool. Executing the following command enables the Pubs database for SQL cache dependencies: aspnet_regsql -C "Data Source=localhost;Integrated Security=True; Initial Catalog=Pubs" -ed This command creates the AspNet_SqlCacheTablesForChangeNotification database table and adds a set of stored procedures to the database specified in the connection string. After you enable a database, you can enable a particular table for SQL cache dependencies with the following command: [View full width] aspnet_regsql -C "Data Source=localhost;Integrated Security=True; Initial Catalog=Pubs"
This command enables the Titles database table for SQL cache dependencies. It creates a new trigger for the Titles database table and adds a new entry in the AspNet_SqlCacheTablesForChangeNotification table. Unfortunately, you cannot use the standard aspnet_regsql tool to enable a local SQL Server 2005 Express database for Polling SQL cache dependencies. The aspnet_regsql tool does not allow you to use the AttachDBFileName parameter in the connection string. To get around this limitation, I've written a custom command-line tool named enableNotifications that works with a local SQL Express database. This tool is included on the CD that accompanies this book. To use the enableNotifications tool, you need to open a command prompt and navigate to the folder that contains your local SQL Express database table. Next, execute the command with the name of the database file and the name of the database table that you want to enable for Polling SQL cache dependencies. For example, the following command enables the Movies database table located in the MyDatabase.mdf database: enableNotifications "MyDatabase.mdf" "Movies"
The enableNotifications tool works only with a local instance of Microsoft SQL Server Express 2005. You cannot use the tool with other versions of Microsoft SQL Server. Warning When using the enableNotifications tool, you must navigate to the same folder as the database that you want to enable for Polling SQL cache dependencies.
Configuring an Application for Polling SQL Cache Dependencies
After you set up a database to support Polling SQL cache dependencies, you must configure your application to poll the database. You configure Polling SQL cache dependencies with the sqlCacheDependency sub-element of the caching element in the web configuration file. For example, the file in Listing 23.41 causes your application to poll the AspNet_SqlCacheTablesForChangeNotification table every 5 seconds (5000 milliseconds) for changes. Listing 23.41. Web.Config
Using Polling SQL Cache Dependencies with Page Output Caching
After you configure Polling SQL cache dependencies, you can use a SQL dependency with Page Output Caching. For example, the page in Listing 23.42 is output cached until you modify the Movies database table. Listing 23.42. PollingSQLOutputCache.aspx
The page in Listing 23.42 includes an <%@ OutputCache %> directive with a SqlDependency attribute. The value of the SqlDependency attribute is the name of the database enabled for SQL dependencies in the web configuration file, followed by the name of a database table. If you open the page in Listing 23.42 in your browser and click your browser's Reload button multiple times, then you'll notice that the time displayed does not change. The page is output cached (see Figure 23.16). Figure 23.16. Using Page Output Caching with a Polling SQL cache dependency.
However, if you modify the Movies database, then the page is dropped from the cache automatically (within 5 seconds). The next time you click the Reload button, the modified data is displayed. If you want to make a page dependent on multiple database tables, then you can assign a semicolon-delimited list of database and table names to the SqlDependency attribute. Note You also can use Polling SQL cache dependencies with an <%@ OutputCache %> directive included in a User Control. In other words, you can use Polling SQL cache dependencies with Partial Page Caching.
Using Polling SQL Cache Dependencies with DataSource Caching
You can use Polling SQL cache dependencies with both the SqlDataSource and ObjectDataSource controls by setting the SqlCacheDependency property. For example, the page in Listing 23.43 caches the output of a SqlDataSource control until the Movies database table is modified. Listing 23.43. PollingSQLDataSourceCache.aspx
In Listing 23.43, the SqlDataSource control includes both an EnableCaching property and a SqlCacheDependency property. A database name and table name are assigned to the SqlCacheDependency property. (The database name must correspond to the database name configured in the <sqlCacheDependency> section of the web configuration file.) If you need to monitor multiple database tables, then you can assign a semicolon-delimited list of database and table names to the SqlCacheDependency property. Using Polling SQL Cache Dependencies with Data Caching
You also can use Polling SQL cache dependencies when working with the Cache object. You represent a Polling SQL cache dependency with the SqlCacheDependency object. For example, the page in Listing 23.44 creates a SqlCacheDependency object that represents the Movies database table. When a DataTable is added to the Cache object, the DataTable is added with the SqlCacheDependency object. Listing 23.44. PollingSQLDataCache.aspx
In Listing 23.44, an instance of the SqlCacheDependency class is created. A database name and table name are passed to the constructor for the SqlCacheDependency class. This class is used as a parameter with the Cache.Insert() method when the DataTable is added to the Cache. Note If you need to create dependencies on multiple database tables, then you need to create multiple SqlCacheDependency objects and represent the multiple dependencies with an instance of the AggregateCacheDependency class.
Using Push SQL Cache Dependencies
When using Microsoft SQL Server 2005, you have the option of using Push SQL cache dependencies rather than Polling SQL cache dependencies. Microsoft SQL Server 2005 includes a feature called query notifications, which use the Microsoft SQL Server 2005 Service Broker in the background. The Service Broker can automatically send a message to an application when data changes in the database. Warning You can create two types of databases with SQL Server Express: a Local or a Server database. You should not use Push dependencies with a Local database. You should use Push dependencies only with a Server database. You cannot create new Server databases when using Visual Web Developer. You can create a Server database by using the full version of Visual Studio .NET 2005 or by downloading Microsoft SQL Server Management Studio Express from the Microsoft MSDN website (msdn.microsoft.com). The advantage of using Push dependencies rather than Polling dependencies is that your ASP.NET application does not need to continuously poll your database for changes. When a change happens, your database is responsible for notifying your application of the change. Now the bad news. There are significant limitations on the types of queries that you can use with Push dependencies. Here are some of the more significant limitations:
This is not a complete list of query limitations. For the complete list, refer to the Creating a Query for Notification topic in the SQL Server 2005 Books Online or the MSDN website (msdn.Microsoft.com). For example, the following simple query won't work: SELECT * FROM Movies This query won't work for two reasons. First, you cannot use the asterisk (*) to represent columns. Second, you must supply a two-part table name. The following query, on the other hand, will work: SELECT Title, Director FROM dbo.Movies You can use Push SQL cache dependencies with stored procedures. However, each SELECT statement in the stored procedure must meet all the requirements just listed. Configuring Push SQL Cache Dependencies
You must perform two configuration steps to enable Push SQL cache dependencies:
In this section, you learn how to perform both of these configuration steps. Warning Unfortunately, when a Push SQL cache dependency fails, it fails silently, without adding an error message to the Event Log. This makes the situation especially difficult to debug. I recommend that after you make the configuration changes discussed in this section that you restart both your web server and database server.
Configuring a Database for Push SQL Cache Dependencies
Before you can use Push SQL cache dependencies, you must enable the Microsoft SQL Server 2005 Service Broker. You can check whether the Service Broker is activated for a particular database by executing the following SQL query: SELECT name, is_broker_enabled FROM sys.databases
If the Service Broker is not enabled for a database, then you can enable it by executing an ALTER DATABASE command. For example, the following SQL command enables the Service Broker for a database named MyMovies: ALTER DATABASE MyMovies SET ENABLE_BROKER
Finally, the ASP.NET process must be supplied with adequate permissions to subscribe to query notifications. When an ASP.NET page is served from Internet Information Server, the page executes in the context of the NETWORK SERVICE account (in the case of Microsoft Windows Server 2003) or the ASPNET account (in the case of other operating systems such as Windows XP). Executing the following SQL command provides the local ASPNET account on a server named YOURSERVER with the required permissions: GRANT SUBSCRIBE QUERY NOTIFICATIONS TO "YOURSERVER\ASPNET" When you request an ASP.NET page when using the Visual Web Developer web server, an ASP.NET page executes in the security context of your current user account. Therefore, when using a file system website, you'll need to grant SUBSCRIBE QUERY NOTIFICATIONS permissions to your current account. Note Push SQL cache dependencies do not use the SQL Server 2005 Notification Services.
Configuring an Application for Push SQL Cache Dependencies
Before you can receive change notifications in your application, you must enable the query notification listener. You can enable the listener with the Global.asax file in Listing 23.45. Listing 23.45. Global.asax
The Application_Start handler executes once when your application first starts. In Listing 23.45, the SqlDependency.Start() method is called with a connection string to a SQL Express server database named MyMovies. Warning The code in Listing 23.45 is commented out in the Global.asax file on the CD that accompanies this book so that it won't interfere with all the previous code samples discussed in this chapter. You'll need to remove the comments to use the code samples in the following sections. Using Push SQL Cache Dependencies with Page Output Caching
You can use Push SQL cache dependencies when caching an entire ASP.NET page. If the results of any SQL command contained in the page changes, then the page is dropped automatically from the cache. The SqlCommand object includes a property named the NotificationAutoEnlist property. This property has the value true by default. When NotificationAutoEnlist is enabled, a Push cache dependency is created between the page and the command automatically. For example, the page in Listing 23.46 includes an <%@ OutputCache %> directive that includes a SqlDependency attribute. This attribute is set to the special value CommandNotification. Listing 23.46. PushSQLOutputCache.aspx
The page in Listing 23.46 includes a SqlDataSource control that retrieves all the records from the Movies database table. Notice that the SqlDataSource control uses a SQL query that explicitly lists column names and uses a two-part table name. These are requirements when using Push dependencies. The page in Listing 23.46 displays the current time. If you request the page in your browser, and refresh the page, the time does not change. The time does not change until you modify the Movies database table. Warning The page in Listing 23.46 connects to a Server database named MyMovies. You should not use Push dependencies with a Local SQL Express database. The page uses a database table named Movies, which was created with the following SQL command: CREATE TABLE Movies ( Id int IDENTITY NOT NULL, Title nvarchar(100) NOT NULL, Director nvarchar(50) NOT NULL, EntryDate datetime NOT NULL DEFAULT GetDate() )
Warning You cannot use Push SQL cache dependencies with an <%@ OutputCache %> directive included in a User Control. In other words, you cannot use Push SQL cache dependencies with Partial Page Caching.
Using Push SQL Cache Dependencies with DataSource Caching
You also can use Push SQL cache dependencies with both the SqlDataSource and ObjectDataSource controls by setting the SqlCacheDependency property. When using Push rather than Polling dependencies, you need to set the SqlCacheDependency property to the value CommandNotification. For example, the page in Listing 23.47 contains a SqlDataSource control that has both its EnableCaching and SqlDependency properties set. Listing 23.47. PushSQLDataSourceCache.aspx
In Listing 23.47, the SqlDataSource control includes a Selecting event handler. Because this event is raised when the data cannot be retrieved from the cache, you can use this event to determine when the data is retrieved from the cache or the database server (see Figure 23.17). Figure 23.17. Using Push SQL cache dependencies with a DataSource control.
Warning The page in Listing 23.47 connects to a Server database named MyMovies. You should not use Push dependencies with a Local SQL Express database. The page uses a database table named Movies, which was created with the following SQL command: CREATE TABLE Movies ( Id int IDENTITY NOT NULL, Title nvarchar(100) NOT NULL, Director nvarchar(50) NOT NULL, EntryDate datetime NOT NULL DEFAULT GetDate() ) Using Push SQL Cache Dependencies with Data Caching
You can use Push SQL cache dependencies when working with the Cache object. You represent a Push SQL cache dependency with an instance of the SqlCacheDependency class. For example, in the Page_Load() handler in Listing 23.48, a DataTable is added to the cache that represents the contents of the Movies database table. The DataTable is displayed in a GridView control. Listing 23.48. PushSQLDataCache.aspx
Notice that an instance of the SqlCacheDependency class is created. A SqlCommand object is passed to the constructor for the SqlCacheDependency class. If the results of the SqlCommand changes, then the DataTable will be dropped automatically from the cache. The order of the commands here is important. You need to create the SqlCacheDependency object before you execute the command. If you call the Fill() method before you create the SqlCacheDependency object, then the dependency is ignored. Warning The page in Listing 23.48 connects to a Server database named MyMovies. You should not use Push dependencies with a Local SQL Express database. The page uses a database table named Movies, which was created with the following SQL command: CREATE TABLE Movies ( Id int IDENTITY NOT NULL, Title nvarchar(100) NOT NULL, Director nvarchar(50) NOT NULL, EntryDate datetime NOT NULL DEFAULT GetDate() )
|
Категории