SQL Server 中的查詢通知Query Notifications in SQL Server

查詢通知是根據 Service Broker 基礎結構而建置,可讓應用程式在資料變更時收到通知。Built upon the Service Broker infrastructure, query notifications allow applications to be notified when data has changed. 此功能對於從資料庫中提供資訊快取的應用程式 (如 Web 應用程式),及需要在來源資料變更時收到通知的應用程式來說非常有用。This feature is particularly useful for applications that provide a cache of information from a database, such as a Web application, and need to be notified when the source data is changed.

有三種方式可以使用 ADO.NET 來實作查詢通知:There are three ways you can implement query notifications using ADO.NET:

  1. 低階實作會由公開伺服器端功能的 SqlNotificationRequest 類別所提供,可讓您利用通知要求來執行命令。The low-level implementation is provided by the SqlNotificationRequest class that exposes server-side functionality, enabling you to execute a command with a notification request.

  2. 高階實作會由 SqlDependency 類別所提供,此類別可提供在來源應用程式與 SQL Server 之間通知功能的高階抽象概念,讓您可以使用相依性來偵測伺服器中的變更。The high-level implementation is provided by the SqlDependency class, which is a class that provides a high-level abstraction of notification functionality between the source application and SQL Server, enabling you to use a dependency to detect changes in the server. 在多數情況下,對於使用 .NET Framework Data Provider for SQL Server 的 Managed 用戶端應用程式而言,這是利用 SQL Server 通知功能最簡單且最有效的方式。In most cases, this is the simplest and most effective way to leverage SQL Server notifications capability by managed client applications using the .NET Framework Data Provider for SQL Server.

  3. 此外,使用 ASP.NET 2.0 或更新版本建置的 Web 應用程式可以使用 SqlCacheDependency Helper 類別。In addition, Web applications built using ASP.NET 2.0 or later can use the SqlCacheDependency helper classes.

查詢通知對於需要重新整理顯示或快取,以回應底層資料變更的應用程式很有用。Query notifications are used for applications that need to refresh displays or caches in response to changes in underlying data. Microsoft SQL Server 可讓 .NET Framework 應用程式傳送命令至 SQL Server,並要求當執行相同命令而產生與最初擷取的不同結果集時,就產生通知。Microsoft SQL Server allows .NET Framework applications to send a command to SQL Server and request notification if executing the same command would produce result sets different from those initially retrieved. 在伺服器所產生的通知會透過佇列傳送,以供稍後處理。Notifications generated at the server are sent through queues to be processed later.

您可以為 SELECT 和 EXECUTE 陳述式設定通知。You can set up notifications for SELECT and EXECUTE statements. 使用 EXECUTE 陳述式時,SQL Server 會為執行的命令註冊通知,而非為 EXECUTE 陳述式本身。When using an EXECUTE statement, SQL Server registers a notification for the command executed rather than the EXECUTE statement itself. 此命令必須符合 SELECT 陳述式的需求和限制。The command must meet the requirements and limitations for a SELECT statement. 當註冊通知的命令包含多個陳述式時,資料庫引擎會為批次中的每個陳述式建立一個通知。When a command that registers a notification contains more than one statement, the Database Engine creates a notification for each statement in the batch.

如果您要開發的應用程式在資料變更時需要可靠的次秒通知,請參閱規劃有效率的查詢通知策略,以及規劃通知文章中查詢通知的替代專案。If you are developing an application where you need reliable sub-second notifications when data changes, review the sections Planning an Efficient Query Notifications Strategy and Alternatives to Query Notifications in the Planning for Notifications article. 如需查詢通知和 SQL Server Service Broker 的詳細資訊,請參閱 SQL Server 檔中的下列文章連結。For more information about Query Notifications and SQL Server Service Broker, see the following links to articles in the SQL Server documentation.

SQL Server 文件SQL Server documentation

本節內容In This Section

啟用查詢通知Enabling Query Notifications
討論如何使用查詢通知,包括其啟用及使用需求。Discusses how to use query notifications, including the requirements for enabling and using them.

ASP.NET 應用程式中的 SqlDependencySqlDependency in an ASP.NET Application
示範如何使用 ASP.NET 應用程式的查詢通知。Demonstrates how to use query notifications from an ASP.NET application.

使用 SqlDependency 偵測變更Detecting Changes with SqlDependency
示範如何偵測查詢結果何時將會與原始接收的結果不同。Demonstrates how to detect when query results will be different from those originally received.

使用 SqlNotificationRequest 的 SqlCommand 執行SqlCommand Execution with a SqlNotificationRequest
示範如何設定 SqlCommand 物件來處理查詢通知。Demonstrates configuring a SqlCommand object to work with a query notification.

參考Reference

SqlNotificationRequest
描述 SqlNotificationRequest 類別與其所有成員。Describes the SqlNotificationRequest class and all of its members.

SqlDependency
描述 SqlDependency 類別與其所有成員。Describes the SqlDependency class and all of its members.

SqlCacheDependency
描述 SqlCacheDependency 類別與其所有成員。Describes the SqlCacheDependency class and all of its members.

另請參閱See also