Using Query Notifications
Description: Query notifications is a new feature in Microsoft® SQL Server™ 2005 that provides client side events
when the result of a query has changed due to an insert, update, or delete operation. This enables WinForm and WebForm based applications
to maintain accurate caches of data, and thus dramatically reduces server load and increases application performance. The
ReviewWatcher sample application (typically located in
drive:\Program Files\Microsoft SQL Server\100\Samples\AdventureWorks\Storefront\ReviewWatcher) demonstrates this capability by issuing a query
for all product reviews with a higher ID than the current ID that the application has processed. A dependency is associated
with the query before it is executed. Part of registering the dependency includes providing an event handler to process the events generated
by the dependency. When a Storefront user adds a review for a product, a row is inserted in the ProductReview table. This triggers the query
notification, which then produces an event on the client. When the event is received a new query (with associated dependency) is issued by the sample application,
which also retrieves the new relevant data. This data is then used to update the user interface.
Implementation Notes: The ReviewWatcher sample invokes the WatchForReviews method at startup, and after each
event is fired. This method creates a command that accesses the ProductReview table, locating rows with ReviewIDs that are larger than those processed so
far. Next, an instance of SqlDependency is created. This instance is associated with the SqlCommand
by passing the command as the first argument to the dependency's constructor. The SqlCommand needs to be a query which fits fairly strict guidelines. Next,
the appropriate event handler (Dependency_OnChanged) is added to the OnChanged event for the dependency. Finally, the command is executed.
It is possible to get an event even though the data in the database may not have changed.
It is important to design your event handler to correctly cope with that situation.