Export (0) Print
Expand All
1 out of 1 rated this helpful - Rate this topic

SqlNotificationRequest and Detecting Notifications (ADO.NET)

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.

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.

  1. Create a new Windows Application in Visual Studio.

  2. Select the default form in the Forms Designer. In the Property grid, change the Text property to Contacts.

  3. Add a Label control to the form. Dock the label control to the bottom of the form.

  4. Add a Button control to the form. Change its Text property to "Get Contacts".

  5. Add a CheckBox control to the form. Change its Text property to "Re-register".

  6. Add a DataGridView to the form.

  7. 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;
    
  8. In the declaration section of the class, add the following items.

    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;
    
    
    
  9. 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 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);";
    }
    
    
    
  10. 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 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);
            }
    }
    
    
    
  11. Next put the following code into the Click event handler for the form's button.

    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.

  12. 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 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();
        }
    }
    
    
    
  13. Create a FormClosed event handler and add the following code to it:

    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.

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

  2. 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
    
  3. 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.

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

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

Show:
© 2014 Microsoft. All rights reserved.