Using SqlNotificationRequest and Detecting Notifications
The following scenario shows how to use the SqlNotificationRequest from a Windows Forms application. To demonstrate how notifications work, this sample application will use a worker thread to monitor a Service Broker queue for message notifications.
About the Sample
The sample application is a Windows Forms application that loads a set of data into a DataSet from the SQL Server 2005 sample database AdventureWorks. The DataSet is filled using a SqlDataAdapter object and then is bound to a DataGridView control. In order to receive notifications, a SqlNotificationRequest object is created and bound to a SqlCommand object used by the SqlDataAdapter. In order to get notifications, the application creates a worker thread that connects to the predefined Service Broker queue and watches for messages. If a notification comes in, the message is pulled off the queue and the UI thread is notified.
Database Setup
For the sample to run correctly, the following Transact-SQL statements must be run on the database server.
USE AdventureWorks ;
GO
CREATE QUEUE ContactChangeMessages ;
CREATE SERVICE ContactChangeNotifications
ON QUEUE ContactChangeMessages
([https://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]) ;
CREATE ROUTE
ContactChangeRoute
WITH SERVICE_NAME = 'ContactChangeNotifications',
ADDRESS = 'LOCAL' ;
GO
For more information about setting up Service Broker queues, see "Service Broker Programming Basics", "CREATE QUEUE (Transact-SQL)" and "CREATE SERVICE (Transact-SQL)" in SQL Server 2005 Books Online.
Building the Sample Application
The application will be a Windows Forms application with a single form.
To build the sample application
Create a new Windows Application project called "Data Listener".
If you are using Visual Basic, add references to System.Data.dll and System.Xml.dll.
Select the default form in the Forms Designer. In the Property grid, change the Text property to Contacts.
Add a Label control to the form. Dock the label control to the bottom of the form.
Add a Button control to the form. Change its Text property to Get Contacts.
Add a Checkbox control to the form. Change its Text property to Re-register.
Add a DataGridView to the form.
Open the form's class module and add the following code to the top of the file, above the class definition:
[Visual Basic]
Option Strict On
Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Threading
[C#]
using System.Data.Sql;
using System.Data.SqlClient;
using System.Threading;
- In the declaration section of the class, add the following items:
[Visual Basic]
Private changeCount As Integer = 0
Private Const tableName As String = "Contacts"
Private Const statusMessage As String = "{0} changes have occurred."
' The following objects are reused
' for the lifetime of the application.
Private dataToWatch As DataSet = Nothing
Private connection As SqlConnection = Nothing
Private command As SqlCommand = Nothing
' The Service Name is required to correctly
' register for notification.
' The Service Name must be already defined with
' the Service Broker for the database you are querying.
Private Const ServiceName As String = "ContactChangeNotifications"
' Specify how long the notification request
' should wait before timing out.
' This value waits for 10 minutes.
Private NotificationTimeout As Integer = 600
[C#]
private int changeCount = 0;
private const string tableName = "Contacts";
private const string statusMessage = "{0} changes have occurred.";
// The following objects are reused
// for the lifetime of the application.
private DataSet dataToWatch = null;
private SqlConnection connection = null;
private SqlCommand command = null;
// The Service Name is required to correctly
// register for notification.
// The Service Name must be already defined with
// Service Broker for the database you are querying.
private const string ServiceName = "ContactChangeNotifications";
// Spercify how long the notification request
// should wait before timing out.
// This value waits for 10 minutes.
private int NotificationTimeout = 600;
- Add three helper methods,
GetConnectionString,GetSQL, and GetListenerSQL. The defined connection string uses integrated security. You will need to verify that the account you are using has the necessary database permissions and that the sample database, AdventureWorks has notifications enabled. For more information, see Special Considerations When Using Query Notifications. In addition, the database must have a custom queue defined. See "Database Setup" above for more information.
[Visual Basic]
Private Function GetConnectionString() As String
' To avoid storing the connection string in your code,
' you can retrive it from a configuration file.
' In general client applications don't need to incur
' overhead of connection pooling.
Return "Data Source=localhost;Integrated Security=SSPI;" & _
"Initial Catalog=AdventureWorks;Pooling=False;"
End Function
Private Function GetSQL() As String
Return "SELECT ContactID, FirstName, LastName, " & _
"EmailAddress, EmailPromotion " & _
"FROM Person.Contact " & _
"WHERE EmailPromotion IS NOT NULL;"
End Function
Private Function GetListenerSQL() As String
' Note that ContactChangeMessages is the name
' of the Service Broker queue that must
' be already defined.
Return "WAITFOR ( RECEIVE * FROM ContactChangeMessages);"
End Function
[C#]
private string GetConnectionString()
{
// To avoid storing the connection string in your code,
// you can retrive it from a configuration file.
// In general, client applications don't need to incur the
// overhead of connection pooling.
return "Data Source=localhost;Integrated Security=SSPI;" +
"Initial Catalog=AdventureWorks;Pooling=False;";
}
private string GetSQL()
{
return "SELECT ContactID, FirstName, LastName, " +
"EmailAddress, EmailPromotion " +
"FROM Person.Contact " +
"WHERE EmailPromotion IS NOT NULL;";
}
private string GetListenerSQL()
{
// Note that ContactChangeMessages is the name
// of the Service Broker queue that must
// be already defined.
return "WAITFOR ( RECEIVE * FROM ContactChangeMessages);";
}
- In order to receive notifications when data on the server changes, the application needs start a secondary thread that watches the Service Broker queue for new messages. Once a message arrives, the worker thread must receive the message and switch to the UI thread. Add the following code to the form's module:
[Visual Basic]
Private Sub StartListener()
' A seperate listener thread is needed to
' monitor the queue for notifications.
Dim listener As New Thread(AddressOf Listen)
listener.Name = "Query Notification Watcher"
listener.Start()
End Sub
Private Sub Listen()
Using connection As SqlConnection = _
New SqlConnection(GetConnectionString())
Using command As New SqlCommand(GetListenerSQL(), connection)
connection.Open()
' Make sure we don't time out before the
' notification request times out.
command.CommandTimeout = NotificationTimeout + 15
Dim reader As SqlDataReader = command.ExecuteReader()
Do While reader.Read()
' Empty queue of messages.
' Application logic could parse
' the queue data and
' change its notification logic.
Loop
Dim args() As Object = {Me, EventArgs.Empty}
Dim notify As New EventHandler( _
AddressOf OnNotificationComplete)
' Notify the UI thread that a notification
' has occurred.
Me.BeginInvoke(notify, args)
End Using
End Using
End Sub
Private Sub OnNotificationComplete( _
ByVal sender As Object, ByVal e As EventArgs)
changeCount += 1
Me.Label1.Text = String.Format(statusMessage, changeCount)
' The user can decide to register
' and request a new notification by
' checking the check box on the form.
GetData(CheckBox1.Checked)
End Sub
[C#]
private void StartListener()
{
// A seperate listener thread is needed to
// monitor the queue for notifications.
Thread listener = new Thread(Listen);
listener.Name = "Query Notification Watcher";
listener.Start();
}
private void Listen()
{
using (SqlConnection connection =
new SqlConnection(GetConnectionString()))
{
using (SqlCommand command =
new SqlCommand(GetListenerSQL(), connection))
{
connection.Open();
// Make sure we don't time out before the
// notification request times out.
command.CommandTimeout = NotificationTimeout + 15;
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
// Empty queue of messages.
// Application logic could parse
// the queue data and
// change its notification logic.
}
object[] args = { this, EventArgs.Empty };
EventHandler notify =
new EventHandler(OnNotificationComplete);
// Notify the UI thread that a notification
// has occurred.
this.BeginInvoke(notify, args);
}
}
}
private void OnNotificationComplete(object sender, EventArgs e)
{
++changeCount;
label1.Text = String.Format(statusMessage, changeCount);
// The user can decide to register
// and request a new notification by
// checking the CheckBox on the form.
GetData(checkBox1.Checked);
}
- Next put the following code into the
Clickevent handler for the form's button. This code will initialize the SqlConnection and SqlCommand objects necessary to execute a command to register a notification request.
[Visual Basic]
changeCount = 0
Me.Label1.Text = String.Format(statusMessage, changeCount)
If connection Is Nothing Then
connection = New SqlConnection(GetConnectionString())
End If
If command Is Nothing Then
' GetSQL is a local procedure SQL string.
' You might want to use a stored procedure
' in your application.
command = New SqlCommand(GetSQL(), connection)
End If
If dataToWatch Is Nothing Then
dataToWatch = New DataSet()
End If
GetData(CheckBox1.Checked)
[C#]
changeCount = 0;
label1.Text = String.Format(statusMessage, changeCount);
if (connection == null)
{
connection = new SqlConnection(GetConnectionString());
}
if (command == null)
{
// GetSQL is a local procedure SQL string.
// You might want to use a stored procedure
// in your application.
command = new SqlCommand(GetSQL(), connection);
}
if (dataToWatch == null)
{
dataToWatch = new DataSet();
}
GetData(checkBox1.Checked);
- The
GetDatamethod below creates the SqlNotificationRequest object, fills the grid with data, and starts a background listener if the user requests it.
[Visual Basic]
Private Sub GetData(ByVal Register As Boolean)
' Empty the dataset so that there is only
' one batch worth of data displayed.
dataToWatch.Clear()
' Make sure the command object does not already have
' a notification object associated with it.
command.Notification = Nothing
If Register Then
' Create and bind the SqlNotificationRequest object
' to the command object.
Dim snr As New SqlNotificationRequest()
snr.UserData = New Guid().ToString()
snr.Options = ServiceName
' If the a timeout occurs, a notifcation
' will occur indicating that is the
' reason for the notification.
snr.Timeout = NotificationTimeout
command.Notification = snr
End If
Using adapter As New SqlDataAdapter(command)
adapter.Fill(dataToWatch, tableName)
Me.DataGridView1.DataSource = dataToWatch
Me.DataGridView1.DataMember = tableName
End Using
If Register Then
' Start the background listener.
StartListener()
End If
End Sub
[C#]
private void GetData(bool Register)
{
// Empty the dataset so that there is only
// one batch of data displayed.
dataToWatch.Clear();
// Make sure the command object does not already have
// a notification object associated with it.
command.Notification = null;
if (Register)
{
// Create and bind the SqlNotificationRequest object
// to the command object.
SqlNotificationRequest snr =
new SqlNotificationRequest();
snr.UserData = new Guid().ToString();
snr.Options = ServiceName;
// If a time-out occurs, a notification
// will indicate that is the
// reason for the notification.
snr.Timeout = NotificationTimeout;
command.Notification = snr;
}
using (SqlDataAdapter adapter =
new SqlDataAdapter(command))
{
adapter.Fill(dataToWatch, tableName);
dataGridView1.DataSource = dataToWatch;
dataGridView1.DataMember = tableName;
}
if (Register)
{
// Start the background listener.
StartListener();
}
}
- Finally, create a
FormClosedevent handler and add the following code to it:
[Visual Basic]
If connection IsNot Nothing Then
connection.Close()
End If
[C#]
if (connection != null)
{
connection.Close();
}
The application loads the DataGridView control with data from the AdventureWorks database when the Get Data button is clicked. If the Re-register checkbox is checked, then the application continues to register for change notifications after each notification. If the Re-register checkbox is not checked, the application unregisters itself after one notification and does not receive any further notifications until the Re-register checkbox is checked and the DataGridView is refreshed by clicking the Get Data button.
Run the application, check the Re-register checkbox, and click the Get Data button. The datagrid control should be filled in with data from the AdventureWorks database.
To test the application, update one of the columns retrieved by the application using Transact-SQL commands such as the following:
UPDATE Person.Contact SET LastName = 'Smith' WHERE ContactID = 1
To reset the data, run the following command:
UPDATE Person.Contact SET LastName = 'Achong' WHERE ContactID = 1
Each time the data is modified, the corresponding change is reflected in the application form's DataGridView control and the status indicator Label control at the bottom of the form, which indicates the number of changes to the database since the DataGridView was last loaded.
Uncheck the Re-register checkbox, update the database again, and observe that the form indicates this change. However subsequent changes to the database do not cause further notifications and therefore the application form indicators remain unchanged.