Query Notifications in ADO.NET 2.0

Query Notifications are a new feature part of ADO.NET 2.0. I gotta admit that the rationale behind it is really valuable - getting notifications back from the database server, when a given dataset of interest is changed. I've implemented the same functionality with MSSQL 2000 requiring periodical server polls, but this is really not very performant solution. With ADO.NET 2.0 and its Service Broker, you can have quick and elegant solution.

While the idea behind QN is really easy to understand, the documentation and examples are still kinda scarce. Not only this, but there turned out to be very small details, which may give you couple of hours in debugging in order to detect them.

To start with, here is a list of articles about Query Notifications in ADO.NET 2.0:

1. Query Notifications in ADO.NET 2.0 [link]
2. Using Query Notifications [link]
3. Enabling Query Notifications [link]
4. Using a Dependency to Detect Changes in the Server [link]

All of these will give you some very good understanding of what QN is all about. However, I found some problems with the examples in them, so don't be confused if you try them and they are not working - it is not you :)

Speaking of problems, one of the subtle things you should have in mind is that the query assigned to the SqlCommand object must be fully qualified (that is the table name should include the table owner, you should enumerate all interested columns in the SELECT clause rather than putting a simple * for ALL columns). Actually if you take a look at the following article:

5. Using SqlDependency for data change events [link]

you will see a bunch of things which the author has experienced (the same as me) while dealing with QN.

Another problem that I got was with my OnChangeEventHandler event handler. I initially received OnChange notifications although there were no DML operations on the monitored dataset. The info that I got from the SqlNotificationEventArgs parameter for this event was the following:

e.Source = Statement
e. Type = Subscribe
e.Info = Invalid

The reason for this was that my SELECT statement was using * for all the column names instead of fully qualifying the columns put in interest. I bet that if this is a problem, then the description of it should be somehow more descriptive and showing more symptoms of what actually caused the specific problem.

Another problem that I got was with the QN registration for the specific database you want to enable it. For this, you should check out the following article:

6. Using Query Notifications in .NET 2.0 to handle ad-hoc data refreshes [link]

SqlDependency is one way to use Query notifications in your application. What is worth noting about it, is that one SqlDependency is good only for a single notification. That said, after one notification has been generated through your SqlDepedency, you should re-create it if you want further changes to be captured. Another cool thing with SqlDependency, is that it can easily applied to ASP.NET cache object (check out article 1 for more information).