SQL Server 2005 Query Notifications Tell .NET 2.0 Apps When Critical Data Changes (Cont.)

Query Notification and ASPNET’s Cache

In addition to the ADO.NET-based query notification, there is an entirely alternate set of tools built into ASP.NET 2.0 that build database cache invalidation into your Web site. The effect is, in some ways, similar to what you achieve with the middle tier caching samples above. This functionality is something that was done previously through a great deal of trickery using database polling, as detailed in Jeff Prosise’s April 2003 Wicked Code column. (See the sidebar for a link to his article.)

System.Web.SqlCacheDependency is a class that you can use in code, similar to SqlDependency, but then add into the Response.Cache to enable cache invalidation based on database updates. An even simpler way to achieve this is through the <OutputCache> directive in the page’s HTML. There are a few differences between these two methods.

• Using SqlCacheDependency attaches the dependency to a specific SqlCommand.
• Using <OutputCache> instructs the page to request a notification for any valid command that is called by the page.
• You have more fine-grained control over the cache with the SqlCacheDependency.
• <OutputCache> enables you to additionally create cache invalidation when using SQL 7.0 and SQL 2000. I will explain this in more detail below.

Listing 6 is an example of using SqlCacheDependency to request notification on a specific command. Creating the object is similar to creating the SqlDependency object in ADO.NET. Then you tie the notification into the page’s cache with Cache.AddDependency, setting the familiar caching properties. In this case, the cache is valid until it expires, which is either within 30 seconds or when a notification comes in, whichever happens first. When the page cache is invalidated, the next request for this page creates the page from scratch, forcing the data access to happen again.

Remember that you don’t see SqlDependency.Start here because it is in the Global.asax.

Invalidating the cache with the <OutputCache> in the page’s markup is even simpler. By using the new SqlDependency parameter of <OutputCache> and setting its value to CommandNotification, you are plugged right in to query notifications. Here is an example with only the minimal parameters being used.

Any valid queries called from this page have a notification request registered in the Service Broker. The data access does not necessarily have to be done in the page’s client-side or server-side code code-behind). You can call into another class or even another assembly for the data access and it will still use query notification requested by the page.

The SqlDependency parameter can also take other values, but only the CommandNotification value, used with SQL Server 2005, gives you the Service Broker Query Notification. ASP.NET 2.0 is also wired up to give cache invalidation from SQL 7 and SQL 2000. One way to do this is by using the value database-name:table-name (for example, pubs:authors) as the SqlDependency value. The effect is that a polling type of query is performed. There are more pieces necessary to make this work, but as it is outside of the scope of this article, I will point you to the “Implementing SQL Server 7/2000 Dependency Caching” explanation and example in the Visual Web Developer Guide on the ASP.NET Web site. (See the sidebar for the link to this.)

Query Notification in Transactions

Query Notifications do work inside of transactions. However, you will only get one notification per transaction regardless of the number of changes made to the database within that transaction.

SQL Server Express

Query Notifications can be used with SSE but the SSE database must be a named instance. Named instance is a setup option.

Additionally, the database that you want to use must be an attached database.

Conclusion

Although Query Notification is definitely one of the big new features in .NET 2.0, it still plays underdog to other exciting features such as generics or the UI magic in ASP.NET. Whether you use it to prevent the incessant re-querying for a drop down list of the 50 U.S. states or to manage the Web-based updates of millions of client computers, it can help you reduce overhead on your resources. In its simplest use-through the ASP.NET OutputCache directive-or by building a complex caching mechanism in your Web application’s middle tier or Web service, Query Notification can be a powerful ally in creating extensible, responsive applications.

Julia Lerman

Links

MSDN Data Access blog: http://blogs.msdn.com/dataaccess

Sushil Chordia’s blog: http://blogs.msdn.com/sushilc

ASP.NET’s Visual Web Developer Guided Tour for “Implementing SQL Server 7/2000 Dependency Caching:” http://www.asp.net/guidedtour2/s23.aspx