Enabling Query Notifications
Applications that consume query notifications have a common set of requirements. In addition, your data source must be correctly configured to support SQL query notifications and the user must have the correct client and server side permissions.
To use query notifications you must:
Use SQL Server 2005.
Enable query notifications for your database.
Ensure that the user ID used to connect to the database has the necessary permissions.
Use a SqlCommand object to execute a valid SELECT statement with an associated notification object—either SqlDependency or SqlNotificationRequest.
Provide code to process the notification when and if the data being monitored changes.
Enabling Query Notifications
For security reasons, SQL Server 2005 databases do not have Service Broker enabled by default. You must enable Service Broker for each database using the ALTER DATABASE command.
To enable Service Broker on the AdventureWorks database via SQL Server Management Studio, execute the following Transact-SQL statement:
ALTER DATABASE AdventureWorks SET ENABLE_BROKER;
For more information, see "Service Broker Programming" in SQL Server 2005 Books Online.
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 SqlClientPermission.
[Visual Basic]
Private Function CanRequestNotifications() As Boolean
Dim perm As New SqlClientPermission( _
Security.Permissions.PermissionState.Unrestricted)
Try
perm.Demand()
Return True
Catch ex As Exception
Return False
End Try
End Function
[C#]
private bool CanRequestNotifications()
{
SqlClientPermission perm =
new SqlClientPermission(
Security.Permissions.PermissionState.Unrestricted);
try
{
perm.Demand();
return true;
}
catch (System.Exception)
{
return false;
}
}
Choosing a Notification Object
The query notifications API provides two objects to process notifications: SqlDependency and SqlNotificationRequest. In general, most non-ASP.NET applications should use the SqlDependency object. ASP.NET applications should use the higher-level SqlCacheDependency, which wraps SqlDependency and provides a framework for administering the notification and cache objects. To use SqlDependency, Service Broker must be enabled for the SQL Server 2005 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 need 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.
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.