Skip to content

Latest commit

 

History

History
85 lines (57 loc) · 5.41 KB

enable-query-notifications.md

File metadata and controls

85 lines (57 loc) · 5.41 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic dev_langs
Enabling query notifications
Learn how to use query notifications, including the requirements for enabling and using them.
David-Engel
davidengel
v-kaywon
04/20/2021
sql
connectivity
conceptual
csharp

Enabling query notifications

[!INCLUDEDriver_ADONET_Download]

Applications that consume query notifications have a common set of requirements. Your data source must be correctly configured to support SQL query notifications and the user must have the correct client-side and server-side permissions.

To use query notifications, you must:

  • Enable query notifications for your database.
  • Ensure that the user ID used to connect to the database has the necessary permissions.
  • Use a xref:Microsoft.Data.SqlClient.SqlCommand object to execute a valid SELECT statement with an associated notification object—either xref:Microsoft.Data.SqlClient.SqlDependency or xref:Microsoft.Data.Sql.SqlNotificationRequest.
  • Provide code to process the notification if the data being monitored changes.

Query notifications requirements

Query notifications are supported only for SELECT statements that meet a list of specific requirements. The following table provides links to the Service Broker and Query Notifications documentation in SQL Server Books Online.

SQL Server documentation

Enabling query notifications to run sample code

To enable Service Broker on the AdventureWorks database by using SQL Server Management Studio, execute the following Transact-SQL statement:

ALTER DATABASE AdventureWorks SET ENABLE_BROKER;

For the query notification samples to run correctly, the following Transact-SQL statements must be executed on the database server.

CREATE QUEUE ContactChangeMessages;

CREATE SERVICE ContactChangeNotifications
  ON QUEUE ContactChangeMessages
([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);

Query notifications permissions

Users who execute commands requesting notification must have SUBSCRIBE QUERY NOTIFICATIONS database permission on the server.

Client-side code that runs in a partial trust situation requires the xref:Microsoft.Data.SqlClient.SqlClientPermission.

The following code creates a xref:Microsoft.Data.SqlClient.SqlClientPermission object, setting the xref:System.Security.Permissions.PermissionState to xref:System.Security.Permissions.PermissionState.Unrestricted. The xref:System.Security.CodeAccessPermission.Demand%2A will force a xref:System.Security.SecurityException at run time if all callers higher in the call stack haven't been granted the permission.

[!code-csharpDataWorks SqlClientPermission_Demand#1]

Choosing a notification object

The query notifications API provides two objects to process notifications: xref:Microsoft.Data.SqlClient.SqlDependency and xref:Microsoft.Data.Sql.SqlNotificationRequest.

Using SqlDependency

To use xref:Microsoft.Data.SqlClient.SqlDependency, Service Broker must be enabled for the SQL Server database being used, and users must have permissions to receive notifications. Service Broker objects, such as the notification queue, are predefined.

Also, xref:Microsoft.Data.SqlClient.SqlDependency automatically launches a worker thread to process notifications as they're posted to the queue. It also parses the Service Broker message, exposing the information as event argument data. xref:Microsoft.Data.SqlClient.SqlDependency must be initialized by calling the Start method to establish a dependency to the database. Start is a static method that needs to be called only once during application initialization for each database connection required. The Stop method should be called at application termination for each dependency connection that was made.

Using SqlNotificationRequest

In contrast, xref:Microsoft.Data.Sql.SqlNotificationRequest requires you to implement the entire listening infrastructure yourself. Also, all the supporting Service Broker objects such as the queue, service, and message types that are supported by the queue must be defined. This manual approach is useful if your application requires special notification messages or notification behaviors, or if your application is part of a larger Service Broker application.

Next steps