前言

在MS SQL Server 2005 新增了查詢通知，這項功能可以提供應用程式在查詢結果變更時要求 SQL Server 主動提供通知。查詢通知可讓程式設計設計師將應用程式在程式先前讀取的資料有異動的時候才查詢資料庫。應用程式主動發出查詢，應用程式會經由查詢資料庫取得現有資料，並且更新通知訂閱。應用程式可以利用查詢通知來減少查詢資料庫的次數。不用撰寫polling的方式用來定期重新執行查詢以維護現行結果的程式碼，程式設計人員可以設計在查詢結果可能過期時就自動被通知的應用程式。有了查詢通知，應用程式可以發出含有查詢及通知要求的命令。應用程式可以快取查詢的結果，或查詢結果所產生的動態內容。當應用程式收到查詢通知時，應用程式會清除快取的內容。如果應用程式需要更新的查詢結果時，應用程式會重新發出查詢及通知要求。Database Engine 會使用「通知訂閱」來追蹤查詢通知的要求。當命令含有通知要求時，資料庫會將要求註冊為通知訂閱，然後再執行此命令。Database Engine 會使用 Service Broker 來傳遞通知訊息。因此，在應用程式要求訂閱的資料庫中，Service Broker 必須處於作用中。查詢通知功能並不會使用 Notification Services。所以查詢通知與事件通知無關。

Service Broker的查詢通知

在MS SQL Server 2005裡的Service Broker才開始有查詢通知功能。Service Broker把Message Queue加入到DataBase中，Service Broker使用一組MessageQueue與Service進行通訊，而Service同時也知道如何與DATABASE通訊，可以呼叫相對應的實體DATABASE。其實，這些MessageQueue和Service都是跟表、視圖和Stored Procedure一樣的類對象(Class-Object)。儘管可以在SQL Server內使用Service Broker，但是ADO.NET知道如何與Service Broker進行通訊以觸發這種機制並且從Service Broker中檢索回通知。在ADO.NET 2.0提供了三種類別可以使用來運用，以下我們列出這三種類別以供參考:

一、SqlNotificationRequest

SqlNotificationRequest 類別所提供的是底層實作，它可讓您使用通知要求執行命令。

二、SqlDependency

SqlDependency是繼承SqlNotificationRequest的一種子類別。可以讓您使用相依性偵測伺服器中的變更。在多數情況下，對於使用 .NET Framework Data Provider for SQL Server 的 Managed 用戶端應用程式而言，這是利用 SQL Server 通知功能最簡單且最有效的方式。

三、SqlCacheDependency

ASP.NET 2.0也通過System.Web.Caching.SqlCacheDependency類別(它提供了一個針對SqlDependency的封裝好的object)與Service Broker進行溝通，而且這是直接通過在一個ASP.NET頁面中使用<%OutputCache>指令以宣告方式提供的功能實現的。這允許ASP.NET程式設計師容易實現依賴在SQL Server中的數據的緩存無效。

查詢通知可用於需要重新整理顯示或快取，以回應基礎資料變更的應用程式中。Microsoft SQL Server 可讓 .NET Framework 應用程式傳送命令至 SQL Server，並要求當執行相同命令而產生與最初擷取的不同結果集時，就產生通知。伺服器中產生的通知會透過這些要稍後處理的佇列進行傳送。而我的實作是使用第二種方式以提供專案方便開發。

實作MonitorDB

實作的環境是MS SQL 2008，開發工具是VS2008，實作步驟如下：

第一步:

由於是要使用查詢通知功能，必須先啟用ServiceBroker，可以利用SQL Management Studio介面啟用Service Broker如下圖:

但有時上線中的資料庫經常會被鎖定以導致無法正確執行，可以改用下列的指令先佔住連線，然後再啟用Service Broker功能:



第二步：



第二步主要是確認應用程式是否具有從Local端要求通知的使用權限。

第三步:



在應用程式上面啟用SqlDendency監聽器。 這個Method傳入的參數是一個DataBase的連接字串，要連接到DataBase必須要做這個步驟。 對於同一連接字串，如果已經執行過該語句，再次執行不會發生任何異常，但Return值會是為False. 如果是在Web的應用程式中使用，建議可以將該statement放在Application_Start事件中執行。 監聽是OnBase在資料庫上，進而denpendency才可以依附在資料表或是查詢上面。當執行完上面的程式片段，MS SQL應該在相對應的DataBase中的”佇列”以及”服務”也會出現相對應註冊連接的佇列以及服務如下圖:



第四步:



這個步驟要特別說明的原因在於，當要去DATABASE查詢資料的語法請記得不要用”Select *”，因為這樣會帶來不可預期的結果，經過這個範例測試會造成不斷的去跟DATABASE要資料讀取回來，所以最好當你要寫查詢資料的語法時，請把你需要的欄位一一的詳細列出。

第五步:



這個步驟是最主要的部份，一開始利用先前建立好的一些方法當成參數傳入像SqlConnection以及SqlCommand的物件中，最主要的地方是在建立SqlDependency物件，這個範例透過這個物件跟MS SQL指定我所需要的資料表或查詢資料結果訂閱查詢通知。接著在SqlDependency物件的OnChange的事件進行綁定，當收到MS SQL的查詢通知的回饋時，我們就可以進行接下來的動作。

第六步:



在第六步中分成兩個區塊，第一個小區塊主要是宣告委託delegate函數的範本用來等下觸發事件可以使用，裡面主要是定義委託函數範本要乎要的函數介面這個範例主要是傳入DataGridView物件以及ListBox物件方便Form視窗資料的呈現。

第二個區塊主要是讓SqlDependency物件發生改變時可以把OnChange事件指定綁定到此範例想要處理的後續動作，用dependency_OnChangeDelegate函數包裝起來，dependency_OnChangeDelegate函數此範例是想要可以把DataBase訂閱查詢通知中帶來的查詢資料結果或是資料表可以展現到Form視窗上，所以使用dataGridView1.Invoke來呼叫相關委託函數介面的Method達到目的。

現在先執行範例程式把監控到DataBase中範例要監控的Table先把資料都列出如下第一張圖，可以看見監控到資料最後一個欄位為每一筆資料異動的時間，而Form中最右邊為我們每次去讀取到DataBase收到通知的異動紀錄。之後我們對DataBase中的Table的電話欄位做資料異動，可以觀察到異動時間以及資料的最後一個欄位的異動時間與原本第一張圖有所差異，這樣代表我們已經收到MS SQL的查詢通知。



結語

優點: 特別適合更新不頻繁但是讀取頻繁的資料表,會提高應用程式性能。cache越多,SQL伺服器負擔就越小,這會減少了IO讀操作以及網路傳輸佔用。

缺點:Web伺服器會稍微增加cache和記憶體增加的負擔,並且在資料庫相應資料表發生更改後伺服器會清除該表相關的所有cache。

雖然查詢通知很方便，可以收到MS SQL的通知，可是查詢通知是針對於並不經常改變的資料而設計，最好把查詢通知應用於伺服器端的應用程式(例如ASP.NET或remoting)而不是用戶端應用程式(例如Windows表單應用程式)。每一個通知請求都要在SQL Server中註冊。如果你擁有大量都有通知查詢的用戶端應用程式,那麼這樣的結果可能會導致伺服器產生資源不足問題。由於這樣的原因,微軟建議對於用戶端應用程式,要使用查詢通知應該限制使用查詢通知的最大並行使用者數目不超過十個。

參考資料

http://technet.microsoft.com/zh-tw/library/ms166043(SQL.90).aspx
http://msdn.microsoft.com/zh-tw/library/cc668761.aspx
http://www.cnblogs.com/hsapphire/archive/2010/01/22/1653981.html
http://blog.miniasp.com/post/2008/07/Cache-LINQ-to-SQL-result-by-using-SqlCacheDependency.aspx