Executing a Command with a Notification RequestÂ
You can execute a Transact-SQL statement and configure a SqlCommand object so that a notification is generated by Microsoft SQL Server when the results will be different if the query is executed again.
This feature is useful when you want to use custom notification queues on the server or in a disconnected scenario in which you do not want to maintain live objects. In this case, use the SqlNotificationRequest to create the notification request. After that you no longer need the SqlNotificationRequest object. Instead, you can query your queue for any notifications and respond appropriately to what you find. This can occur even if the application is shut down then subsequently restarted.
To enable notifications for a given query, bind a SqlNotificationRequest object to a SqlCommand object.
When a Transact-SQL statement is executed on the server, it keeps track of the query, and if it detects a change that also might cause the result set to change, it sends a message to the SQL Server 2005 queue set in the notification request.
How you poll the SQL Server 2005 queue and interpret the message is application specific. The application is responsible for polling the queue and reacting based on the contents of the message. For more information, see "Building Notification Services Applications" in SQL Server Books Online.
An application is designed in a manner similar to that shown in the following code.
' Assume c is an open SqlConnection.
' Create a new SqlCommand object.
Dim cmd As New SqlCommand("SELECT * FROM Authors", c)
' Create a SqlNotificationRequest object.
Dim nr As New SqlNotificationRequest()
nr.id = "myNotificationID"
nr.Service = "mySSBQueue"
' Associate the notification request with the command.
cmd.Notification = nr
' Execute the command.
cmd.ExecuteReader()
' Process the DataReader.
' Using SQL syntax, the SQL Server 2005 queue can be polled periodically to see if you have a new message.
// Assume c is an open SqlConnection.
// Create a new SqlCommand object.
SqlCommand cmd=new SqlCommand( "SELECT * FROM Authors", c );
// Create a SqlNotificationRequest object.
SqlNotificationRequest nr=new SqlNotificationRequest();
nr.id="myNotificationID";
nr.Service="mySSBQueue";
// Associate the notification request with the command.
cmd.Notification=nr;
// Execute the command.
cmd.ExecuteReader();
// Process the DataReader.
// Using SQL syntax, the SQL Server 2005 queue can be polled periodically to see if you have a new message.
When using SQL Server notification requests with SqlNotificationDependency, create your own queue name instead of using the default service name, as shown in the following SQL script.
CREATE QUEUE mySSBQueue
GO
CREATE SERVICE mySSService ON QUEUE mySSBQueue
([//s.ms.net/SQL/Notifications/PostQueryNotification/v1.0])
GO
Security
There are no new client-side security elements for SqlNotificationRequest. This is mostly a server feature, and the server has created special privileges that users must have to request a notification.