啟用查詢通知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.

  • 將可執行有效 SELECT 陳述式的 SqlCommand 物件,與關聯的通知物件 (SqlDependencySqlNotificationRequest) 搭配使用。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 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. 如果應用程式要求特別通知訊息或通知行為,或者應用程式為較大 Service Broker 應用程式的一部分,則此手動方法非常有用。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