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 https://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 https://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
([https://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]) ;
CREATE ROUTE
WebCacheMessagesRoute
WITH SERVICE_NAME = 'WebCacheNotifications',
ADDRESS = 'LOCAL' ;
The contract https://schemas.microsoft.com/SQL/Notifications/PostQueryNotification specifies that messages of type https://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:
[Visual Basic]
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.
See Also
Concepts
Getting Notifications
Using SqlDependency to Subscribe to Query Notifications
Other Resources
Introduction to Service Broker Programming
Service Broker Routing