שפה
הערה
הגישה לדף זה מחייבת הרשאה. באפשרותך לנסות להיכנס או לשנות מדריכי כתובות.
הגישה לדף זה מחייבת הרשאה. באפשרותך לנסות לשנות מדריכי כתובות.
To receive a notification when query results change, configure the SqlCommand with a notification request. Use notification requests for custom server queues or when you don't want to maintain live dependency objects.
Creating the Notification Request
Create the SqlNotificationRequest object and assign it to the command's Notification property before execution. After the server registers the request, you don't need to retain the request object. Query the queue for notifications and respond as your application requires. Notifications can arrive while the application is shut down and remain available after it restarts.
When the command with the associated notification is executed, any changes to the original result set trigger sending a message to the SQL Server queue that was configured in the notification request.
How you poll the SQL Server 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.
Note
When using SQL Server notification requests with SqlDependency, create your own queue name instead of using the default service name.
Grant users the server permissions required to request notifications. The SqlNotificationRequest class doesn't add client-side security requirements.
Example
The following fragment creates the SqlNotificationRequest object and associates it with the SqlCommand. Install the Microsoft.Data.SqlClient NuGet package and import Microsoft.Data.Sql and Microsoft.Data.SqlClient. The Options value names a Service Broker service and the database that hosts it, not the queue itself. Create the service and queue as described in Enabling Query Notifications.
' Assume connection is an open SqlConnection.
' Create a new SqlCommand object.
Dim command As New SqlCommand( _
"SELECT ShipperID, CompanyName, Phone FROM dbo.Shippers", connection)
' Create a SqlNotificationRequest object.
Dim notificationRequest As New SqlNotificationRequest()
notificationRequest.UserData = "NotificationID"
notificationRequest.Options = "service=ContactChangeNotifications;local database=AdventureWorks"
' Associate the notification request with the command.
command.Notification = notificationRequest
' Execute the command.
command.ExecuteReader()
' Process the DataReader.
' You can use Transact-SQL syntax to periodically poll the
' SQL Server queue to see if you have a new message.
// 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 notificationRequest=new SqlNotificationRequest();
notificationRequest.UserData = "NotificationID";
notificationRequest.Options = "service=ContactChangeNotifications;local database=AdventureWorks";
// Associate the notification request with the command.
command.Notification=notificationRequest;
// Execute the command.
command.ExecuteReader();
// Process the DataReader.
// You can use Transact-SQL syntax to periodically poll the
// SQL Server queue to see if you have a new message.