Enabling Query Notifications

To use query notifications, configure your database to support them and grant the database principal the required 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 SqlCommand to execute a valid SELECT statement with an associated SqlDependency or SqlNotificationRequest object.
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 the SQL Server docs.

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

Grant the database principal SUBSCRIBE QUERY NOTIFICATIONS and the Service Broker permissions required by your queue configuration. For example, replace the placeholder with an existing database user:

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [<database_user>];

Modern .NET doesn't support code access security. Don't use SqlClientPermission.Demand to authorize query notifications; configure database permissions instead.

Choosing a Notification Object

Query notification support provides two objects to process notifications: SqlDependency and SqlNotificationRequest. Use SqlDependency to receive change events and invalidate your application's cache. Use SqlNotificationRequest when you need to implement your own notification listener.

Important

Don't use System.Data.SqlClient for new development. Use Microsoft.Data.SqlClient instead. The ASP.NET Framework SqlCacheDependency class requires System.Data.SqlClient and doesn't integrate with Microsoft.Data.SqlClient. For new Microsoft.Data.SqlClient applications, handle SqlDependency.OnChange and update the cache through your application's cache API.

Using SqlDependency

To use 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.

In addition, SqlDependency automatically launches a worker thread to process notifications as they are posted to the queue; it also parses the Service Broker message, exposing the information as event argument data. SqlDependency must be initialized by calling the Start method to establish a dependency to the database. This 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, SqlNotificationRequest requires you to implement the entire listening infrastructure yourself. In addition, all the supporting Service Broker objects such as the queue, service, and message types 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.