启用查询通知Enabling Query Notifications

使用查询通知的应用程序有一组通用的需求。Applications that consume query notifications have a common set of requirements. 必须正确配置数据源才能支持 SQL 查询通知,并且用户必须具有正确的客户端和服务器端权限。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.

  • 确保用于连接数据库的用户 ID 具有必要的权限。Ensure that the user ID used to connect to the database has the necessary permissions.

  • 使用 SqlCommand 对象执行有效的 SELECT 语句,包含关联的通知对象 — SqlDependencySqlNotificationRequestUse a SqlCommand object to execute a valid SELECT statement with an associated notification object—either SqlDependency or SqlNotificationRequest.

  • 提供所监视的数据更改时用于处理通知的代码。Provide code to process the notification if the data being monitored changes.

查询通知需求Query Notifications Requirements

仅对满足一系列特定要求的 SELECT 语句支持查询通知。Query notifications are supported only for SELECT statements that meet a list of specific requirements. 下表提供指向“SQL Server 联机丛书”中的 Service Broker 和“查询通知”文档的链接。The following table provides links to the Service Broker and Query Notifications documentation in SQL Server Books Online.

SQL Server 文档SQL Server documentation

启用查询通知运行示例代码Enabling Query Notifications to Run Sample Code

若要通过使用 SQL Server Management Studio 启用AdventureWorks数据库 Service Broker,请执行以下 transact-sql 语句: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;

若要此查询通知示例正确运行,必须在数据库服务器上执行下面的 Transact-SQL 语句。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

执行请求通知的命令的用户必须在服务器上具有 SUBSCRIBE QUERY NOTIFICATIONS 数据库权限。Users who execute commands requesting notification must have SUBSCRIBE QUERY NOTIFICATIONS database permission on the server.

在部分信任情况下运行的客户端代码要求 SqlClientPermissionClient-side code that runs in a partial trust situation requires the SqlClientPermission.

下面的代码创建一个 SqlClientPermission 对象,将 PermissionState 设置为 UnrestrictedThe following code creates a SqlClientPermission object, setting the PermissionState to Unrestricted. 如果尚未为调用堆栈中的所有高级调用方授予该权限,则 Demand 将在运行时强制引发 SecurityExceptionThe Demand will force a SecurityException at run time if all callers higher in the call stack have not been granted the permission.

// Code requires directives to
// System.Security.Permissions and
// System.Data.SqlClient

private bool CanRequestNotifications()
{
    SqlClientPermission permission =
        new SqlClientPermission(
        PermissionState.Unrestricted);
    try
    {
        permission.Demand();
        return true;
    }
    catch (System.Exception)
    {
        return false;
    }
}
' Code requires directives to
' System.Security.Permissions and
' System.Data.SqlClient

Private Function CanRequestNotifications() As Boolean

    Dim permission As New SqlClientPermission( _
      PermissionState.Unrestricted)

    Try
        permission.Demand()
        Return True
    Catch ex As Exception
        Return False
    End Try

End Function

选择通知对象Choosing a Notification Object

查询通知 API 提供两个用于处理通知的对象:SqlDependencySqlNotificationRequestThe query notifications API provides two objects to process notifications: SqlDependency and SqlNotificationRequest. 通常情况下,大多数非 ASP.NET 应用程序应使用 SqlDependency 对象。In general, most non-ASP.NET applications should use the SqlDependency object. ASP.NET 应用程序应使用更高级别的 SqlCacheDependency,以便包装 SqlDependency 并为管理通知和缓存对象提供框架。ASP.NET applications should use the higher-level SqlCacheDependency, which wraps SqlDependency and provides a framework for administering the notification and cache objects.

使用 SqlDependencyUsing SqlDependency

要使用 SqlDependency,必须对所使用的 SQL Server 数据库启用 Service Broker,并且用户必须具有接收通知的权限。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 对象(例如通知队列)已预定义。Service Broker objects, such as the notification queue, are predefined.

此外,SqlDependency 会自动启动一个辅助线程以在通知发布到队列中时处理这些通知;它还会分析 Service Broker 消息,将此信息作为事件自变量数据公开。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 方法初始化 Start,以建立与数据库的相关性。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. 必须在应用程序终止时为执行的每个相关连接调用 Stop 方法。The Stop method should be called at application termination for each dependency connection that was made.

使用 SqlNotificationRequestUsing SqlNotificationRequest

相对而言,SqlNotificationRequest 要求您自己实现整个侦听基础结构。In contrast, SqlNotificationRequest requires you to implement the entire listening infrastructure yourself. 此外,必须定义队列所支持的所有支持 Service Broker 对象(例如队列、服务和消息类型)。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.

请参阅See also