SqlNotificationRequest and Detecting Notifications (ADO.NET)

Article
04/18/2008

This section shows how to use the SqlNotificationRequest from a Windows Forms application. To demonstrate how notifications work, this sample application uses 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 data into a DataSet from the SQL Server 2005 AdventureWorks sample database. A SqlDataAdapter object fills the DataSet and then binds it to a DataGridView control. A SqlNotificationRequest object for receiving notifications 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.

Note

The sample code assumes that you have enabled query notifications by executing the scripts in Enabling Query Notifications (ADO.NET).

Building the Sample Application

The sample Windows Forms application contains a single form. Follow these steps to create the form.

Create a new Windows Application in Visual Studio.
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 code file and add the following statements to the top of the file, above the class definition.

Option Explicit On
Option Strict On

Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Threading

using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Threading;

In the declaration section of the class, add the following items.

Private changeCount As Integer = 0

Private Const tableName As String = "Contacts"
Private Const statusMessage As String = _
   "{0} changes have occurred."
Private exitRequested As Boolean = False
Private waitInProgress As Boolean = False

' 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 notifications.
' The Service Name must be already defined with
' the Service Broker for the database you are querying.
Private Const ServiceName As String = _
   "Service=ContactChangeNotifications"

' The database name is needed for both the connection
' string and the SqlNotificationRequest.Options property.
Private Const DatabaseName As String = "AdventureWorks"

' Specify how long the notification request
' should wait before timing out.
' This value waits for 30 seconds.
Private NotificationTimeout As Integer = 30

protected int changeCount = 0;

protected const string tableName = "Contacts";
protected const string statusMessage = 
    "{0} changes have occurred.";
protected Boolean exitRequested = false;
protected Boolean waitInProgress = false;

// The following objects are reused
// for the lifetime of the application.
protected DataSet dataToWatch = null;
protected SqlConnection connection = null;
protected 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.
protected const string ServiceName = 
    "Service=ContactChangeNotifications";

// The database name is needed for both the connection
// string and the SqlNotificationRequest.Options property.
protected const string DatabaseName = "AdventureWorks";


// Specify how long the notification request
// should wait before timing out.
// This value waits for 30 seconds. 
protected int NotificationTimeout = 30;

Add three helper methods, GetConnectionString, GetSQL, and GetListenerSQL.

The defined connection string uses integrated security. Therefore, you must verify that the account you are using has the necessary database permissions and that the AdventureWorks sample database has notifications enabled. In addition, the database must have a custom queue defined. For more information on configuring query notifications for this sample, see Enabling Query Notifications (ADO.NET).

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 String.Format("Data Source=(local);Integrated Security=true;" & _
     "Initial Catalog={0};Pooling=False;Asynchronous Processing=true;", _
     DatabaseName)
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. This query removes
    ' all the messages. In a real application,
    ' you would want to only look for your messages.
    Return "WAITFOR (RECEIVE * FROM ContactChangeMessages);"
End Function

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 String.Format("Data Source=(local);Integrated Security=true;" +
     "Initial Catalog={0};Pooling=False;Asynchronous Processing=true;", 
     DatabaseName);        
}

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 must 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.

Private Sub Listen()
    Using command As New SqlCommand(GetListenerSQL(), connection)
        ' Make sure we don't time out before the
        ' notification request times out.
        If Not connection.State = ConnectionState.Open Then
            connection.Open()
        End If

        command.CommandTimeout = NotificationTimeout + 120

        Dim callBack As New AsyncCallback(AddressOf Me.OnReaderComplete)
        Dim asynResult As IAsyncResult = command.BeginExecuteReader(callBack, command)
        If Not asynResult.IsCompleted Then
            waitInProgress = True
        End If
    End Using
End Sub

Private Sub OnReaderComplete(ByVal asynResult As IAsyncResult)
    ' You may not interact with the form and its contents
    ' from a different thread, and this callback procedure
    ' is all but guaranteed to be running from a different thread
    ' than the form. Therefore you cannot simply call code that 
    ' updates the UI.
    ' Instead, you must call the procedure from the form's thread.
    ' This code will use recursion to switch from the thread pool
    ' to the UI thread.
    If Me.InvokeRequired Then
        Dim switchThreads As New AsyncCallback(AddressOf Me.OnReaderComplete)
        Dim args() As Object = {asynResult}
        Me.BeginInvoke(switchThreads, args)
        Exit Sub
    End If

    ' At this point, this code will run on the UI thread.
    Try
        waitInProgress = False
        Dim reader As SqlDataReader = CType( _
           asynResult.AsyncState, SqlCommand).EndExecuteReader(asynResult)
        Do While reader.Read()
            ' Empty queue of messages.
            ' Application logic could parse
            ' the queue data to determine why things.
            For i As Integer = 0 To reader.FieldCount - 1
                Debug.WriteLine(reader(i).ToString())
            Next
        Loop

        reader.Close()
        changeCount += 1
        Me.Label1.Text = String.Format(statusMessage, changeCount)

        ' The user can decide to request
        ' a new notification by
        ' checking the check box on the form.
        ' However, if the user has requested to 
        ' exit, we need to do that instead.
        If exitRequested Then
            Me.Close()
        Else
            GetData(CheckBox1.Checked)
        End If

    Catch ex As Exception
        MessageBox.Show(ex.Message, ex.Source, _
          MessageBoxButtons.OK, MessageBoxIcon.Warning)
    End Try
End Sub

private void Listen()
{
    using (SqlCommand command =
        new SqlCommand(GetListenerSQL(), connection))
    {
     // Make sure we don't time out before the
    // notification request times out.
       if (connection.State != ConnectionState.Open)
        {
            connection.Open();
        }

        command.CommandTimeout = NotificationTimeout + 120;

        AsyncCallback callBack = new AsyncCallback(
            this.OnReaderComplete);
        IAsyncResult asynResult = command.BeginExecuteReader(
            callBack, command);
        if (asynResult.IsCompleted == true)
        {
            waitInProgress = true;
        }
    }
}

private void OnReaderComplete(IAsyncResult asynResult)
{
    // You may not interact with the form and its contents
    // from a different thread, and this callback procedure
    // is all but guaranteed to be running from a different thread
    // than the form. Therefore you cannot simply call code that 
    // updates the UI.
    // Instead, you must call the procedure from the form's thread.
    // This code will use recursion to switch from the thread pool
    // to the UI thread.
    if (this.InvokeRequired == true)
    {
        AsyncCallback switchThreads = new AsyncCallback(this.OnReaderComplete);
        object[] args = { asynResult };
        this.BeginInvoke(switchThreads, args);
        return;
        }
  // At this point, this code will run on the UI thread.
      try
        {
            waitInProgress = false;
            SqlDataReader reader = ((SqlCommand)asynResult.AsyncState)
                .EndExecuteReader(asynResult);
            while (reader.Read())
            // Empty queue of messages.
            // Application logic could partse
            // the queue data to determine why things.
            {
                for (int i = 0; i <= reader.FieldCount - 1; i++)
                    Debug.WriteLine(reader[i].ToString());
            }

            reader.Close();
            changeCount += 1;
            this.label1.Text = String.Format(statusMessage, changeCount);

            // The user can decide to request
            // a new notification by
            // checking the CheckBox on the form.
            // However, if the user has requested to
            // exit, we need to do that instead.
            if (exitRequested == true)
            {
                this.Close();
            }
            else
            {
                GetData(checkBox1.Checked);
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, ex.Source, 
                MessageBoxButtons.OK, MessageBoxIcon.Warning);
        }
}

Next put the following code into the Click event handler for the form's button.

Private Sub Button1_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles Button1.Click

    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)
End Sub

private void button1_Click(object sender, EventArgs e)
{
    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);
}

This code initializes the SqlConnection and SqlCommand objects necessary to execute a command to register a notification request.

Add the GetData method below, which creates the SqlNotificationRequest object, fills the grid with data, and starts a background listener if the user requests it.

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 request As New SqlNotificationRequest()
        request.UserData = New Guid().ToString()

        request.Options = String.Format( _
           "Service={0};local database={1}", ServiceName, _
           DatabaseName)

        ' If a timeout occurs, a notifcation
        ' will occur indicating that is the 
        ' reason for the notification.
        request.Timeout = NotificationTimeout
        command.Notification = request
    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.
        Me.Listen()
    End If
End Sub

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 request =
            new SqlNotificationRequest();
        request.UserData = new Guid().ToString();
        request.Options = String.Format(
            "Service={0};local database={1}", 
            ServiceName, DatabaseName);

        // If a time-out occurs, a notification
        // will indicating that is the 
        // reason for the notification.
        request.Timeout = NotificationTimeout;
        command.Notification = request;
    }

    using (SqlDataAdapter adapter =
        new SqlDataAdapter(command))
    {
        adapter.Fill(dataToWatch, tableName);

        dataGridView1.DataSource = dataToWatch;
        dataGridView1.DataMember = tableName;
    }

    if (Register)
    {
        // Start the background listener.
        this.Listen();
    }
}

Create a FormClosed event handler and add the following code to it:

Private Sub Form1_FormClosed(ByVal sender As System.Object, _
  ByVal e As System.Windows.Forms.FormClosedEventArgs) _
  Handles MyBase.FormClosed

    If connection IsNot Nothing Then
        connection.Close()
    End If
End Sub

private void Form1_FormClosed(object sender, 
    FormClosedEventArgs e)
{
    if (connection != null)
    {
        connection.Close();
    }
}

Testing the Sample Application

The application loads the DataGridView control with data from the AdventureWorks database when the Get Data button is clicked. If the Re-register check box is checked, the application continues to register for change notifications after each notification. If the Re-register check box is not checked, the application unregisters itself after one notification and does not receive any further notifications until the Re-register check box is checked and the DataGridView is refreshed by clicking the Get Data button.

Run the application, check the Re-register check box, and click the Get Data button. The DataGridView 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 the following Transact-SQL command:
```
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 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 check box, update the database again, and observe that the form indicates this change.

Note

Subsequent changes to the database do not cause further notifications and therefore the application form indicators remain unchanged.

SqlNotificationRequest and Detecting Notifications (ADO.NET)

About the Sample

Building the Sample Application

Testing the Sample Application

See Also

Concepts

Additional resources