剖析SQL Server 2005查詢通知之基礎篇

摘要 在本系列文章中，我們將深入探討如何把.NET 2.0和SQL Server 2005的查詢通知特徵聯合起來，以便通知應用程式何時關鍵資料發生變化進而達到消除反覆查詢資料庫的目的。[@more@]

　　一. 引言

　　資料庫應用程式的典型問題之一是更新陳舊的資料。

　　設想有一個典型的顯示產品及其分類的電子商務網站。一個供應商的產品列表很可能並不經常發生變化，而其分類列表甚至更不會頻繁更改。然而，在使用者每次瀏覽該網站時，必須從資料庫中反覆查詢這些列表。這顯然是一種典型的低效資源利用，開發者和架構師都在絞盡腦汁想辦法以減少這種浪費。

　　緩衝技術正是“最小化”對這種幾乎“停滯”的資料進行重複查詢的技術之一。這種資料可以被進行一次性查詢並儲存在一個緩衝區中，而且應用程式可以從快取中重複地存取資料。偶爾情況下，才更新快取以得到新資料。但是，圍繞更新快取的時間排程方面出現了幾個問題。該多長時間操作一次呢？例如，你每隔多長時間希望你的產品分類改變一次？每隔幾個月一次？每隔兩個月重新整理一次該緩衝區如何？你知道會發生什麼嗎？就在你重新整理快取之後，分類被更新，而且在下一次重新整理前在兩個月的時間裡它將保持陳舊。

　　查詢通知，是微軟的ADO.NET和SQL Server小組協作開發的新成果。簡言之，查詢通知允許你緩衝資料並且僅在SQL Server中的資料發生變化時才發出通知。一旦接到通知，你就可以重新整理你的緩衝區或者採取你需要的任何措施。

　　在SQL Server 2005中引入的一種新特徵“Service Broker”使得查詢通知成為可能。Service Broker把佇列機制引入到資料庫管理中，它使用一組佇列與服務進行通訊，而服務反過來也知道如何往回通訊以呼叫相應的實體。其實，這些佇列和服務都是一些與表、檢視和儲存過程一樣的類物件。儘管完全可以在SQL Server內使用Service Broker，但是ADO.NET知道如何與Service Broker進行通訊以觸發這種機制並且從Service Broker中檢索回通知。

　　注意 當SQL Server中的資料發生改變時，查詢通知允許你緩衝資料並且通知你。

　　在.NET一端，存在很多種“鉤入”這種功能的方式。ADO.NET 2.0提供了System.Data.SqlClient.SqlDependency和System.Data.Sql.SqlNotificationRequest類。SqlDependency是SqlNotificationRequest的一種高階實現，並且是當使用ADO.NET 2.0時你最有可能使用的類。ASP.NET 2.0也透過System.Web.Caching.SqlCache-Dependency類(它提供了一個針對SqlDependency的包裝器)與Service Broker進行通訊，而且這是直接透過在一個ASP.NET頁面中使用指令以宣告方式提供的功能實現的。這允許ASP.NET開發者容易地實現使依賴於SQL Server中的資料中的快取無效。

　　二. .NET與Service Broker的通訊

　　上面這些技術是如何聯合到一起來解決“緩衝之謎”的呢？儘管你可以採取很多的措施以允許SQL Server把服務提供給.NET；但是，關鍵還在於，傳送到SQL Server的查詢具有一個依附到它們的標誌以便告訴SQL Server，除了返回結果集外，SQL Server還應該把該查詢（及其請求者）註冊到Service Broker。為此，你要建立一個感知該查詢的佇列和一個依附到該佇列的服務，並且知道如何返回到客戶端。如果該結果集中的任何一行在資料庫中得到更新，那麼在相關佇列中的項將觸發，並且反過來，把一條訊息傳送到它的服務，然後把一個通知傳送回初始化該請求的應用程式。

　　圖1是SQL Server Management Studio的一個快照，它顯示了在資料庫的Service Broker部分中的佇列（Queues）和服務（Services）。

圖1.該圖顯示了.NET的查詢通知所使用的Pubs資料庫中的預設佇列和服務。

　　下面是理解這一過程的一些有關重要內容：

　　· 存在一些規則以指出SQL Server接收哪些型別的查詢。

　　· 一旦SQL Server傳送回通知，佇列和服務即被刪除。這意味著，你僅能在每次請求中得到一個通知。一個典型的應用程式會重新查詢資料庫並且，在同時，請求在Service Broker中建立一種新的依賴性。

　　· 返回到應用程式的資訊也不過是“something changed”。該應用程式並不被通知改變了什麼（請參考本文中的SQLNotificationEventArgs一節瞭解更多的資訊）。

　　· 儘管依賴性被繫結到從查詢中返回的行上；但是，它並不被查詢中的單個列加以過濾。如果你有一個查詢—它返回你的組織的基本成員姓名以及那些單個改變之一的地址（但是，其姓名並不改變），這將觸發一個改變通知。很希望，這種特殊行為在未來的版本中會有所改變。

　　· 通知被返回，透過一個專門針對這一目的建立的SqlConnection。這個連線並不加入連線池中。

　　三. 何時使用查詢通知

　　查詢通知是針對於並不經常改變的資料而設計的。最好把它應用於伺服器端的應用程式(例如ASP.NET或remoting)而不是客戶端應用程式(例如Windows表單應用程式)。記住，每一個通知請求都要在SQL Server中註冊。如果你擁有大量的都有通知請求的客戶端應用程式，那麼這可能會導致你的伺服器產生資源問題。微軟推薦，對於客戶端應用程式，你應該限制查詢通知使用為不多於十個並行使用者。

　　對於大規模應用程式來說，查詢通知可能是一種強有力的幫助，而不用簡單地新增越來越多的伺服器以滿足要求。設想，有一家大型的為成千上百萬使用者提供線上軟體更新服務的軟體公司。不是使每一個使用者的更新操作都觸發伺服器上的另一個查詢來確定需要哪些元件，而是能夠緩衝查詢結果並且可以直接從該快取中服務匹配的查詢。

　　注意：對於客戶端應用程式來說，應該限制你的查詢通知使用—不多於十個併發使用者。

　　對於較小規模的情況而言，下拉式列表框是另一種典型的資料集；此時該資料集更新的次數並不如請求的次數多。產品列表、州列表、國家列表、供應商、銷售人，甚至更多不太需要頻繁改變的資訊正是使用通知的較好候選。

　　四. 為使用查詢通知作準備

　　因為預設情況下SQL Server 2005處於高度安全的狀態，所以你需要“開啟”一些功能才能使用查詢通知。首先，你要使用的每一個資料庫都需要啟動Service Broker功能。為此，你可以在T-SQL中使用如下命令實現：

USE mydatabase
ALTER DATABASE mydb SET ENABLE_BROKER

　　另外，你需要授予一些SQL Server許可權以允許非管理員帳戶能夠參與使用查詢通知。

　　五. SqlDependency.Start和Stop

　　SqlDependency和SqlCacheDependency都要求，在任何通知請求前先呼叫靜態方法SqlDependency.Start()。這個方法負責建立一個SqlConnection以實現在資料改變時接收通知。注意，你僅需要在一個應用程式的生命週期的開始建立這些內容。例如，在一個ASP.NET應用程式中，global.asax檔案的Application_Start事件處理器就是實現這一功能的好地方。

　　注意，對包含在通知中的每一個連線都應該呼叫Start方法。因此，如果你在應用程式中存取多個資料庫，那麼你需要為每一個資料庫呼叫Start。在下列示例中，有一個針對Pubs資料庫的連線串pubsConn，它在這個應用程式的web.config檔案中定義。

　　為了切斷這個連線，你可以使用SqlDependency.Stop()，這也是一個靜態方法。

Sub Application_Start(ByVal sender as Object， _
ByVal e as EventArgs)
System.Data.SqlClient.SqlDependency.Start _
(System.Configuration.ConfigurationManager. _
Connectionstrings("pubsConn").ConnectionString)
End Sub
Sub Application_End(ByVal sender as Object，
ByVal e as EventArgs)
System.Data.SqlClient.SqlDependency.Stop _
(System.Configuration.ConfigurationManager. _
Connectionstrings("pubsConn").ConnectionString)
End Sub

　　如果你在呼叫Start和Stop的同時觀察SQL Server Profiler，那麼你會看到許多有趣的資訊。當呼叫Start時，應用程式執行一個查詢以確保支援Service Broker，然後建立一個儲存過程備以後用於清除在Service Broker基礎結構中的SqlDependency佇列和服務。最後，它執行一個SQL Server 2005 WaitFor命令，該命令負責查詢在Notification Service部分的入口。這就是如果你使用ADO.NET的低階SqlNotificationRequest物件的話所有你需要顯式完成的事情。

　　在整個的.NET 2.0的設計過程中，SqlDependency底層架構從一種推模式（來自SQL Server）改變為一種拉模式（來自.NET）。這樣做的原因是為了解決第一次設計時所導致的一些安全問題。微軟的Sushil Chordia在MSDN上發表了一篇有關於這種改進的文章，該文詳細描述了這一改進的內在機理。

　　六. 你的第一個通知

　　下面，讓我們開始使用SqlDependency來分析一下所有上面這些是如何協同工作的。
首先，我們建立一個類NotificationTest來存取你的資料。在這個類中，還要建立一個典型的函式以便從Pubs資料庫的Authors表中查詢一些資料並返回一個SqlDataReader。

Imports System.Data.SqlClient
Public Class NotificationTest
Public Function DepTest() As SqlDataReader
Dim conn As New SqlConnection(connstring)
conn.Open()
Dim cmd As New SqlCommand(
"SELECT * FROM authors("， conn)")
Dim rdr As SqlDataReader
rdr = cmd.ExecuteReader()
Return rdr
End Function
End Class

　　現在，讓我們修改程式碼來加入這種依賴性。首先，宣告一個名為SqlDependency的物件。為了使之用於該類中的其它函式中，我把它定義為一個類變數。

　　然後，你需要改變這個查詢。查詢通知要求你顯式地列舉在你的查詢中的列，以及總是使用一種“兩部分”的表名。注意一下在修改後的程式碼示例中的新的查詢文字。

　　然後，例項化新的SqlDependency並且把它依附到命令中。

　　就是這些。當執行命令時，依賴性隨著它直到資料庫。在它處理查詢的同時，SQL Server能夠看到這一依賴性並且把它傳送到Service Broker以註冊它。

Imports System.Data.SqlClient
Public Class NotificationTest
Dim dep As SqlDependency
Public Function DepTest() As SqlDataReader
Dim conn As New SqlConnection(connstring)
conn.Open()
Dim cmd As New SqlCommand( _
"SELECT au_id， au_lname，au_fname " & _
"FROM dbo.authors"， conn)
dep = New SqlDependency(cmd)
Dim rdr As SqlDataReader
rdr = cmd.ExecuteReader()
Return rdr
End Function
End Class

　　現在，你已經註冊了依賴性，但是當通知返回到應用程式時你還根本沒有捕獲它。不過，SqlDependency類提供了兩種方式來了解一個通知。一種方式是透過OnChange事件，你可以透過建立一個代理來捕獲它；另一種方式是透過屬性HasChanges，你可以在你的應用程式邏輯中對之進行測試。在下列程式碼中，我在OnDepChange事件中新增了程式碼以便在後面的某個時候測試通知。

Imports System.Data.SqlClient
Public Class NotificationTest
Dim dep As SqlDependency
Public Function DepTest() As SqlDataReader
Dim conn As New SqlConnection(connstring)
conn.Open()
Dim cmd As New SqlCommand( _
"SELECT au_id，au_lname，au_fname FROM " + _
"dbo.authors"， conn)
dep = New SqlDependency(cmd)
AddHandler dep.OnChange， AddressOf OnDepChange
Dim rdr As SqlDataReader
rdr = cmd.ExecuteReader()
Return rdr
End Function
'處理器方法
Public Sub OnDepChange(ByVal sender As Object， _
ByVal e As SqlNotificationEventArgs)
Dim DepInfo As String = e.Info.ToString
'做一些事情以響應通知
End Sub
Public ReadOnly Property HasChanges() As Boolean
Get
Return dep.HasChanges
End Get
End Property
End Class

　　現在，我們來看一下其工作原理。首先，把一個斷點放到OnDepChange事件的End Sub程式碼行。然後，從你喜歡的網頁、表單程式或控制檯程式中呼叫DepTest函式來進行測試。在返回SqlDataReader後，在Visual Studio 2005的Server Explorer或在SQL Server Management Studio中開啟Authors表並且編輯某一個欄位內容。例如，一旦鎖定這一改變，那麼，當你把游標移動到表中的一個新行時，斷點應該被啟用。

　　七. SQLNotificationEventArgs

　　當你看到通知的確從資料庫中傳來時，你可以分析一下相應變數的值，它是一個SqlNotificationEventArgs物件。SqlDependency總是隨著OnChange事件返回這個物件，而且它是很有用的。其中，SqlNotificationInfo是一個具有18種可能值的列舉型別。其中，一些值對應情況正常，而另一些顯示出了問題。這些列舉中有Update，Insert和Delete—告訴你在資料中發生了什麼型別的變化。還有其它一些值即使在事件發生時也不會被髮送。例如，重新啟動伺服器將激發所有的通知；而列舉值Drop或Truncate告訴你已經對依賴的表實現了某種操作。

　　另外，還存在一些依賴性甚至還不能被註冊的情形，例如如果你試圖對一個UPDATE查詢設定一個依賴性將返回Invalid。而返回值Query顯示你的查詢語法並不符合通知的嚴格規則。上面列舉表中的最後兩個列舉值，還有其它幾個與不能註冊查詢相關的列舉值在執行該命令時被立即返回。

　　透過查詢MSDN庫中的有關SqlNotificationInfo列舉文件，你可以得到這些列舉的完全列表。

　　當我一些場合上談論查詢通知時，人們總是問我：“通知是否會告訴你發生了什麼事情？”。回答是“不會”。

　　總之，SQLNotificationEventArgs能夠向你給出一個通知中最為詳細的資訊，而這些資訊在除錯排錯時是非常有用的。