Using SqlNotificationRequest to Subscribe to Query Notifications

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 Benefits of Programming with Service Broker.

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 AdventureWorks2008R2 ;

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 AdventureWorks2008R2 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=AdventureWorks2008R2;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.