I’m ashamed that this is STILL on my to-do list as I have probably written and presented more about Query Notification than most people. But I see via Roger Jennings blog that Ryan Dunn has the key of the implementation posted on his blog (by way of a hot tip from Mike Pizzo on the DP* team :-)).

To activate Query Notification, you need to get a query “registered” in SQL Server’s Service Broker then listen for a notification.

ADO.NET 2.0’s SqlDependency class does all the dirty work then all you need to do is create a SqlDependency object and attach it to a SqlCommand before you execute it. When the command gets to the server, the server sees the dependency and sets up the watch and the notification in Service Broker. When SQL Server sees something change that would impact the results of your query, it fires back a notification. SqlDependency has an event handler to catch that notification as well as other properties to interact with it.

SqlNotification is a more low level approach for complex scenarios where you want to have more control over the process. Here you need to create your own listener.

ASP.NET also uses Query Notification with the SqlCacheDependency object as well as dynamically engaging it in the Page directive.

The obvious (if you have played with this stuff) problem here is that with LINQ to SQL, we are not executing the command ourselves. So how do we register a query with service broker and how do we listen for it?

Rather than attaching the SqlDependency to a SqlCommand, you can expliclty wire up some instructions to have the SqlDependency taken along when a call is made to SqlServer. Pretty cool, although I’m already wondering about how I might want to control that. Certainly you don’t want to attempt to register EVERY SINGLE query with Service Broker. Many will fail anyway and when that happens you get an immediate notice about the failure. There are a limited scope of scenarios in which QueryNotification is a benefit (oft-called and infrequently changing data e.g. “states in the u.s.” “categories of items we sell”) and plenty of rules about what is and is not a valid query for notification. With a blanket approach, you could be creating a performance nightmare.

The key is the CallContext class, which is buried in System.Runtime.Remoting.Messaging. I’ve never heard of it before and would never ever have figured this out on my own! Here’s more info on CallContext.

Here’s the critical line of code from Ryan’s post which has a more complete example of using SqlDependency in it’s simplest form, which is always the right place to start.

  System.Runtime.Remoting.Messaging.CallContext.SetData(“MS.SqlDependencyCookie”, dependency.Id)

So now my todo list is getting edited. I will have to see how to control this puppy and also what happens when you use the asp.net page directive.

(*DP=Data Programmability)

  Sign up for my newsletter so you don't miss my conference & Pluralsight course announcements!