ASP.NET uses polling to retrieve change notifications for Microsoft SQL Server 7 and 2000. This polling occurs on a background thread apart from the threads used to service requestsa request will never be slowed down due to a polling operation. When there are no changes being monitored , the polling is stopped automatically; when a new SqlCacheDependency is created, the polling begins again. ConfigurationThe poll simply asks SQL for all of the records in the AspNet_SqlCacheTablesForChangeNotification table. The number of records in this table will not exceed the number of tables in the database. This is a fast operation since there are no joins or other complex SQL operations and the table is very small. The poll time can be configured for each application, but it defaults to once every 5 seconds. Before we can create a SqlCacheDependency , we need to add some entries in the web.config file of our application (see Listing 11.2). Listing 11.2 SQL Server Cache Dependency Configuration<configuration> <connectionStrings> <add name="Northwind" connectionString="server=localhost; database=Northwind; uid=sa;pwd=00password" /> </connectionStrings> <system.web> <cache> <sqlCacheDependency enabled="true"> <add name="Northwind" connectionString="Northwind" pollTime="500" /> </cache> </system.web> </configuration> The above configuration file adds an entry for the Northwind database in the <connectionStrings/> configuration section. Although not implemented for the Technology Preview release, this configuration section will eventually support encryption to securely store connection string information. The <cache> section is a new entry for the <system.web> configuration section group . In the Technology Preview release, <cache> only supports configuration options for SQL Server change notifications though the <sql CacheDependency /> section. The <sqlCacheDependency /> section contains the elements and attributes shown in Table 11.3. Now that SQL cache invalidation is configured, we can begin using it in our ASP.NET application. Table 11.3. The <sqlCacheDependency /> Configuration Section
Invalidating Output-Cached PagesThere are two ways you can use SQL Server invalidation within pages: through directives or through the page output-cache APIs. The easiest way is to use the new sqldependency attribute of the <%@ OutputCache %> directive. The sqldependency attribute accepts two types of values:
The Syntax for SQL Server 7 and 2000To use sqldependency for SQL Server 7 or 2000, here's the syntax: <%@ OutputCache duration="9999" varybyparam="none" sqldependency="Northwind:Products" %> You can also specify multiple dependencies by separating items with a semicolon. For example, if you also want the page dependent on the Pubs Authors table, use this syntax: <%@ OutputCache duration="9999" varybyparam="none" sqldependency="Northwind:Products;Pubs:Authors" %> The Syntax for SQL Server "Yukon"To use sqldependency for SQL Server "Yukon," here's the syntax: <%@ OutputCache duration="9999" varybyparam="none" sqldependency="CommandNotification" %>
When the page is created, the page uses the value of the sqldependency attribute as parameters for the constructor of SqlCacheDependency . (We'll look at the SqlCacheDependency class shortly.) Using Response.Cache APIAn alternative to using the <%@ OutputCache %> directive is to use the Response.Cache API and create the SqlCacheDependency ourselves (see Listing 11.3). Listing 11.3 Using the Cache API for SQL Server Dependency<%@ Page Language="VB" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <script runat="server"> Public Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) ' Create the SqlCacheDependency ' Dim dp As New SqlCacheDependency("Northwind", "Products") ' Set up page output caching ' Response.Cache.SetExpires(DateTime.Now.AddSeconds(60)) Response.Cache.SetCacheability(HttpCacheability.Public) Response.Cache.SetValidUntilExpires(True) ' Make this page dependent on the SqlCacheDependency ' Response.AddCacheDependency(dp) Dim connection As New SqlConnection( _ "server=.; database=Northwind;uid=sa;pwd=00password") Dim command As New SqlCommand("SELECT * FROM Products", _ connection) connection.Open() DataGrid1.DataSource = command.ExecuteReader() DataGrid1.DataBind() connection.Close() End Sub </script> <h1>Last update: <%=DateTime.Now.ToString("r")%></h1> <hr> <asp:DataGrid runat="server" id="DataGrid1" /> Immediately when SqlCacheDependency is created, polling begins. How It WorksOn the first poll, the list of notification-enabled tables is returned from the database. This list of tables is used to construct a cache entry for each table returned. Any dependencies requested through SqlCacheDependency are then made on this hidden cache entry. Thus, multiple SqlCacheDependency instances can be made for the same table, all dependent on one entry in the cache. When the table cache entry changes, it invalidates all dependent cache items. Below is an example session (which assumes that the Northwind database and Products table are already configured for change notifications).
Let's look at SqlCacheDependency in more detail. |