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.

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

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.

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:

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.