Export (0) Print
Expand All

Executing a SqlCommand with a SqlNotificationRequest 

A SqlCommand can be configured to generate a notification when data changes after it has been fetched from the server and the result set would be different if the query were executed again. This is useful for scenarios where you want to use custom notification queues on the server or when you do not want to maintain live objects.

Creating the Notification Request

You can use a SqlNotificationRequest object to create the notification request by binding it to a SqlCommand object. Once the request is created, you no longer need the SqlNotificationRequest object. You can query the queue for any notifications and respond appropriately. Notifications can occur even if the application is shut down and subsequently restarted.

When the command with the associated notification is executed, any changes to the original result set trigger sending a message to the SQL Server 2005 queue that was configured in the notification request.

How you poll the SQL Server 2005 queue and interpret the message is specific to your application. The application is responsible for polling the queue and reacting based on the contents of the message.

NoteNote

When using SQL Server notification requests with SqlDependency, create your own queue name instead of using the default service name.

There are no new client-side security elements for SqlNotificationRequest. This is primarily a server feature, and the server has created special privileges that users must have to request a notification. For more information, see Enabling Query Notifications and "Building Notification Services Applications" in SQL Server Books Online.

Example

The following code fragment demonstrates the design pattern for creating the application.

// Assume connection is an open SqlConnection.
// Create a new SqlCommand object.
SqlCommand command=new SqlCommand(
 "SELECT ShipperID, CompanyName, Phone FROM dbo.Shippers", connection);

// Create a SqlNotificationRequest object.
SqlNotificationRequest notficationRequest=new SqlNotificationRequest();
notficationRequest.id="NotificationID";
notficationRequest.Service="mySSBQueue";

// Associate the notification request with the command.
command.Notification=notficationRequest;
// Execute the command.
command.ExecuteReader();
// Process the DataReader.
// You can use T-SQL syntax to periodically poll the 
// SQL Server queue to see if you have a new message.

See Also

Community Additions

ADD
Show:
© 2014 Microsoft