Using SqlNotificationRequest to Subscribe to Query Notifications
Subscribing to a query notification using SqlNotificationRequest requires that you prepare the underlying Service Broker objects before your application can request the notification. Once you request the subscription, your application monitors the queue for a notification message and reacts appropriately when the message arrives.
SQL Server delivers query notifications using Service Broker. A query notification message has the message type name
http://schemas.microsoft.com/SQL/Notifications/QueryNotification. Service Broker validates messages of this type as VALID_XML WITH SCHEMA COLLECTION. For subscriptions created with SqlNotificationRequest, the application is responsible for monitoring the queue and processing the notification messages. Therefore, using SqlNotificationRequest requires that you implement an external application. This topic discusses the specific steps required to subscribe to a query notification using SqlNotificationRequest. For more information on creating an application to process query notification messages, see Introduction to Service Broker Programming.
A SqlNotificationRequest must specify a service to receive the notification messages. To create a service, you must create a queue for the service to use, and then create the service. You must also create a route to the service in the local database.
The Database Engine uses the contract
http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification to send notification messages, so the service that you create must accept conversations that follow this contract. The example below creates a service named WebCacheNotifications that uses the queue WebCacheMessages and then creates a route to the WebCacheNotifications service in the local database.
USE AdventureWorks ; CREATE QUEUE WebSiteCacheMessages ; CREATE SERVICE WebCacheNotifications ON QUEUE WebSiteCacheMessages ([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]) ; CREATE ROUTE WebCacheMessagesRoute WITH SERVICE_NAME = 'WebCacheNotifications', ADDRESS = 'LOCAL' ;
http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification specifies that messages of type
http://schemas.microsoft.com/SQL/Notifications/QueryNotification may be sent by the initiator of the conversation.
The service name in the SqlNotificationRequest object is the name of the Service Broker service. The notification is created as a Service Broker message.
The notification request must also contain a message string for the request. When the Database Engine creates a notification for this request, the notification message contains this message string. The notification message is an XML document. This document contains a Message element that holds the message string included in the notification request. The application uses the message string to identify the query that corresponds to the notification.
Notification subscriptions are managed using a combination of the query and the message. If the application requests another notification with the same message and the same query, the Database Engine updates the notification subscription rather than creating a new subscription. The message can be any string. However, notice that the Database Engine determines whether two messages are the same. Therefore, the options set for the database strings that do not compare equivalent in your program may be equivalent in the database. For example, the Database Engine considers strings that differ only in the number of trailing spaces to be the same.
The sample below shows a simple program that creates a notification subscription using SqlNotificationRequest:
Option Strict On Imports System Imports System.Data Imports System.Data.Sql Imports System.Data.SqlClient Namespace Microsoft.Samples.SqlServer Module NotificationSampleMain Public Sub Main() Try ' Connect to the AdventureWorks database in the default instance ' on this server, using integrated security. If you change this ' connection string, be sure to change the service string below. Using connection As SqlConnection = _ new SqlConnection("database=AdventureWorks;server=.;" + _ "Integrated Security=SSPI") connection.Open() ' Define the service to receive the notifications. Update this ' information if you change the connection string. Dim service As String = _ "WebCacheNotifications" Dim query As String = _ "SELECT prod.Name, prod.Class, " + _ " prod.ProductNumber " + _ "FROM Production.Product as prod " + _ "WHERE prod.Color = 'Black' " Dim command As SqlCommand = connection.CreateCommand() command.CommandText = query command.Notification = _ new SqlNotificationRequest(Guid.NewGuid().ToString(), _ service, _ Int32.MaxValue) Dim reader As SqlDataReader = command.ExecuteReader() ' Normally, an application would process the results here. MsgBox("Registered the notification.") ' Notice that the connection dispose method also ' disposes the commands and readers created from the ' connection. End Using ' Using connection ' For sample purposes, simply display all exceptions and exit. Catch e As SqlException MsgBox("SqlException: " + e.Message + vbCrLf _ + e.StackTrace ) Catch e As Exception MsgBox("Exception: " + e.Message + vbCrLf _ + e.StackTrace ) End Try End Sub ' Main End Module 'NotificationSampleMain End Namespace ' Microsoft.Samples.SqlServer
After this code runs, SQL Server contains a query notification subscription. The subscription produces a notification when there is a change in any of the data specified in the following query:
SELECT prod.Name, prod.Class, prod.ProductNumber FROM Products.Product as prod WHERE prod.Color = 'Black'
Service Broker delivers the notification messages to the service WebCacheNotifications. Because this service uses the queue WebCacheMessages, notification messages appear in that queue. To process the notification messages, the application monitors the queue WebCacheMessages.