In the April CTP of .NET 2.0, I chanced upon some changes to SqlNotificationRequest, using my favorite tool, .NET Reflector and my one my favorite investigative techniques, called “follow the error message”.
It turns out that two properties in SqlNotificationRequest the id and Service properties are about to be replaced. They still work but are marked “do not use, to be removed”. They'll but replaced by the UserData and Options properties. UserData appears to be a straight replacement for id, probably mandated by the .NET naming police. The naming police are possibly the same folks who replaced SqlContext.GetPipe and GetWindowsIdentity methods with the Pipe and WindowsIdentity properties. Making things consistent is nice, but its hard on folks who write things like demos, labs, slides, books, and articles.
SqlNotificationRequest.Options is more interesting. While the Service property only let you specify the Service Broker Service name, options gives you…you guessed it, more options. You can also specify which database the broker service lives in or even the Broker indentifier.
Suppose you wanted to listen for query notifications on a query on a table in the pubs database, using a service named “MyService” that also lives in the pubs database in your local SQL Server instance. Using the soon-obsolete Service and Id properties it would look like this:
SqlNotificationRequest not = new SqlNotificationRequest();
not.Id = Guid.NewGuid().ToString();
not.Service = "MyService";
not.Timeout = 0;
// now hook it up to the right SqlCommand
Using the new syntax would look like this:
SqlNotificationRequest not = new SqlNotificationRequest();
not.UserData = Guid.NewGuid().ToString();
not.Options = "service=MyService;local database=pubs";
not.Timeout = 0;
// now hook it up to the right SqlCommand
You can even use the Service Broker identifier GUID (look it up by “select name, service_broker_guid from sys.databases”) in the Options like this:
//NB: Service Broker service names are case sensistive!
//not.Options = "service=MyService;local database=pubs";
not.Options = "service=MyService;broker instance=CE086F11-C691-47F1-A8B6-1B7BD59EA6AE";
This property gives you the option of pointing at a service in a different database in the same instance, or even a different instance, subject to sercurity, of course. Happy query notifying. I gotta go fix a paper. And a book chapter. And a slide. And a lab. And… geez.