在資料庫記憶體放的資料的使用模式有許多是變動少而查詢多的。據統計，資料庫中一般有 20% 以上的資料屬於此型別，例如公司的產品、人事資料，以及用作參照的資料表（lookup table），例如提供地址輸入的可選擇的國家、城市，人事部門、職級等等。這些資料可能都是在資料輸入時，供人點選而以代號填入某個資料表欄位中，其內容自身較少更改。

在多人同時訪問的環境中，我們一般會設計快取記憶體的機制，將上述不常更改的資料臨時存放在應用程式伺服器或是使用者的機器上，避免頻繁地往返訪問資料庫。但為了要提供資料庫內他人更新後的狀況，仍必須輪詢（Poll）資料庫，或是在資料表上編寫觸發器（trigger），以及可能透過訊息佇列（Message queue）來通知等等。不管方式為何，大多需要設計者花些巧思來同步應用程式快取記憶體與資料庫中更新過的記錄。

[@more@]

SQL Server 2005 搭配 ADO.NET 2.0 的 SqlClient 後，內建由 SQL Server 透過 Service Broker 服務通知前端應用程式它有興趣的相關資料已經更改，該機制稱為 Query Notification。根據使用到的功能不同，完成通知的整體架構參與元件可能有 SQL Server 2005 Query Engine、Broker Service、系統儲存過程 sp_DispatcherProc、ADO.NET 的 SqlNotification 類（System.Data.Sql.SqlNotificationRequest）、SqlDependency 類（System.Data. SqlClient.SqlDepenency）以及 ASP.NET Cache 類（System.Web.Caching.Cache）等等。

而執行的邏輯簡單說明如下：

SqlCommand 有 Notification 屬性，用來存放 Notification 相關的設定。當 SqlCommand 執行時，會讓傳遞該執行需求的 TDS 協議附加上 Notification 的資訊。

SQL Server 收到該需求後，為這個需求註冊 Notification，並執行該需求自身的 T-SQL 語法。

SQL Server 會監控後續執行的 DML 語法，看看是否會影響到前一步返回給前端的資料集合（rowset），一旦有影響到，則送一個訊息到 Service Broker。

Service Broker 的佇列中有訊息後，可能發生的狀況如下：

Notification 在前端應用程式偵聽的佇列中放入訊息，ADO.NET 的下層自動讀取訊息並觸發事件。

在 Service Broker 內的訊息持續保留著，較高階的前端應用程式自己來處理這個訊息。

上述步驟的示意圖如圖12-8所示：

圖12-8 SQL Server 2005 提供的 Notification 機制

以往與 SQL Server 溝通時，都是傳遞 T-SQL 的批處理語法。而在 SQL Server 2005 客戶端應用程式除了依舊傳遞這些 T-SQL 語法外，還可以加上三種訊息：用來傳送通知的 Service Broker Services 的名稱、以字串表示的 notification identifier、通知的超時間隔。一旦帶有這三種訊息，SQL Server 2005 則會注意該語法批次所建立並返回的資料集合（rowset），若有一個以上的資料集合，SQL Server 也會全部都注意，當有其他語法會影響到這些資料集合時就發出通知。

在 SQL Server 2000 的 Indexed View 中其實就已經實現了監控檢視表（View）所涵蓋的資料表內相關的記錄改變，並自動更新 Indexed View 的例項資料。而 SQL Server 2005 也採用相同的引擎來監控資料集合。但並不是所有檢視表所定義的查詢語法都可以建索引，同樣地，若你傳遞到 SQL Server 的查詢語法無法建立通知，將會得到如 Invalid Query 一類的報錯資訊。

因為 SQL Server 2005 的通知機制實際是搭配該版所新增的 Services Broker，所以要發出通知的資料庫必須讓 Services Broker 啟動（enable）。Services Broker 利用 SQL Server 2005 所提供的佇列（Queue）建立非同步通知。而通知機制就是一組 Services Broker 內建好的服務（Service），也就是有標準的訊息（Message）、傳送的佇列、傳送訊息的規則（Contract）等等。你可以透過“SQL Server Management Studio”的“物件資源管理器”視窗檢視每個資料庫節點底下“Services Broker”節點內相關的設定，如圖12-9 所示：

圖12-9 SQL Server 2005 在 Service Broker已經準備好查詢通知的相關設定

由於我們是透過 SQL Server 2005 的範例資料庫 AdventureWorks 來測試，因此若要讓應用程式可以收到通知，須先啟動該資料庫的 Service Broker Services，同時要允許登入的賬號訂閱查詢通知。而需要執行的 T-SQL 設定如程式程式碼列表12-8所示：

程式程式碼列表12-8 設定 AdventureWorks 資料庫啟動 Service Broker，並允許賬號 Sandy 訂閱查詢通知

--啟動 Service Broker，停止則是設定 DISABLE_BROKER

ALTER DATABASE AdventureWorks SET ENABLE_BROKER

--並無法在 sp_dboption 看出某個 DB 是否啟動了 BROKER

--EXEC sp_dboption AdventureWorks

--需要檢視sys.databases 的 is_broker_enabled 欄位才知道是否啟動

SELECT * FROM sys.databases

--允許某個賬號訂閱查詢

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO Sandy

我們分別提供 Windows Forms 以及 Web Forms 兩種應用程式範例。首先是 Windows Forms，執行畫面如圖12-10所示：

圖12-10 修改資料後，透過 SQL Server 的通知來更新已經查詢的現有資料

在範例中透過限制查詢產品的編號範圍來避免 SQL Server 關注太多的資料，從而避免造成頻繁通知多個前端應用程式。你可以透過範例程式的主選單重複開啟兩個如圖12-10 所示的程式介面，以此模擬不同人同時訪問資料，但彼此設定不同產品編號範圍的查詢，而其中有部分範圍重迭。然後分別呼叫不同的記錄來修改，測試是否當重迭的記錄被其中一個視窗改變後，兩個視窗都會接到來自 SQL Server 的通知。反之，若更新的是某個視窗自己特有的資料範圍，則只有該視窗會接到通知。範例程式程式碼主要的部分如程式程式碼列表12-9所示：

程式程式碼列表12- 9 透過 SqlDependency 物件設定 SqlCommand 物件的 Notification 屬性，以註冊通知的相關設定

Dim conn As New SqlConnection（ _

ConfigurationSettings.ConnectionStrings（"AWConnectionString"）.ConnectionString）

Delegate Sub PopulateList（）

Private Sub notificationForm_Load（ByVal sender As System.Object, ByVal e As System.EventArgs） _

Handles MyBase.Load

SqlDependency.Start（ADONET20.My.Settings.AdventureWorksConnection）

'取得初始的資料

ListProducts（）

End Sub

Sub OnDependencyChanged（ByVal sender As Object, ByVal e As SqlNotificationEventArgs）

'SqlDependency 物件的 OnChanged 事件觸發時

'要執行的商業邏輯

Dim dR As DialogResult

dR = MessageBox.Show（"資料已經修改了. 要更新資料嗎?", e.Info.ToString, _

MessageBoxButtons.YesNo, MessageBoxIcon.Question）

If dR = Windows.Forms.DialogResult.Yes Then

'交給 Form 的主執行緒更新資料

Me.Invoke（New PopulateList（AddressOf ListProducts））

End If

End Sub

Public Sub ListProducts（）

'重新裝載資料

'SqlDependency 設定後，僅會註冊一次的事件通知

Dim dep As New SqlDependency（）

'設定 SqlDependency 物件的 OnChanged 事件發生時，要呼叫哪個 event handler

AddHandler dep.OnChanged, AddressOf OnDependencyChanged

'限制查詢的範圍，避免太大的範圍導致多人都影響到這個範圍內的資料，而讓 SQL Server

'頻頻觸發通知

Using cmd As New SqlCommand（ _

"SELECT ProductID, Name, ListPrice FROM Production.Product " & _

"WHERE ProductID BETWEEN @Start AND @End", conn）

With cmd

.Parameters.Add（New SqlParameter（"@Start", Data.SqlDbType.Int））

.Parameters.Add（New SqlParameter（"@End", Data.SqlDbType.Int））

.Parameters（0）.Value = txtStart.Text

.Parameters（1）.Value = txtEnd.Text

End With

'自動幫我們設定 SqlCommand 的 Notification 屬性所需的 SqlNotificationRequest 物件

'可以透過 Debug 來觀察 SqlCommand 物件執行前後的關係

dep.AddCommandDependency（cmd）

productListBox.Items.Clear（）

conn.Open（）

Dim reader As SqlDataReader = cmd.ExecuteReader（）

While reader.Read（）

productListBox.Items.Add（reader（"ProductID"） & " - " & _

reader（"Name"）.ToString & ": " & reader（"ListPrice"）.ToString）

End While

End Using

conn.Close（）

End Sub

在上述範例程式程式碼中，首先是在 Global.asax 檔案內的 Application_Start 事件加上透過 SqlDependency 類的靜態方法 Start 啟動接聽，Start 方法需要傳遞資料庫連線字串。它會完成如下的操作：

開啟一條新的不經過 connection pool 的連線到 SQL Server 2005，由於 SQL Server 2000 之前的版本並不支援這些機制，所以透過 SqlDependency 類連線到 SQL Server 2000 之前的版本不會有作用。

在伺服器上建立一個新的佇列，並賦予唯一名稱。

在該佇列上建立一個唯一名稱的服務。

在伺服器上建立一個新的儲存過程，在客戶端不再聽佇列時，清除掉上述臨時建置的各種物件。

偵聽佇列所收到的更改通知。

需要強調的是不管呼叫 Start 方法幾次，每個程式（Process）只會開一條連線到 SQL Server 去聽資料的變化。也由於是正常開啟連線到 SQL Server，所以若客戶端應用程式和伺服器端之間有防火牆，也不會影響這個通知機制。否則，若防火牆阻止對 SQL Server 建立連線，則原本就訪問不到 SQL Server 的資料，也就不必談還需要在資料變化後通知了。但也由於需要建立額外的連線到 SQL Server，所以這種機制比較適用於應用程式伺服器（如 IIS、COM+）透過快取訪問 SQL Server 的架構，而不是有個幾百臺前端應用程式同時訪問 SQL Server 的 client/server 架構，否則固定幾百條連線連在 SQL Server 上，將會因為過多連線而拖垮 SQL Server。

可在範例程式中透過SqlCommand 例項中的T-SQL 更改記錄，但並沒有自動更新 ListBox 內各條記錄的資料，而是在收到 SQL Server 記錄改變的通知後，透過事件觸發指到OnDependencyChanged 函式呼叫主執行緒重新執行 ListProducts 方法，從相關資料表讀出更新後的記錄來重設 ListBox 的內容。當然，你也可以利用另外的應用程式來更新 Prodution.Product 資料表，如 SQL Server Management Studio，就能看到如圖12-10 被通知的畫面。

網頁的設計本來就非常重視快取（cache）的機制，因此在 ASP.NET 1.0/1.1 時就已經對快取記憶體多有著墨。而在 ASP.NET 2.0 中除了網頁架構外，更是大幅增強伺服器控制快取記憶體的能力。對於 SQL Server 2000/7.0 以往的版本也提供了透過對資料表加上額外的觸發器（Trigger）和資料表來記錄變化，讓目標資料有變化時可以透過輪詢（Poll）該輔助的資料表來觸發快取失效的過程。但關於 ASP.NET 2.0 的討論超過了本書的範圍，你可以到微軟網站或是 .NET Magazine（）找尋相關的資訊。

針對 SQL Server 2005，在 ASP.NET 2.0 內則可以直接透過 SqlCacheDependency 類與前述的主動通知機制協作，當 DB 內的資料有變化時，讓快取記憶體的資料立刻失效，重新回到 SQL Server 取得最新的資料後更新快取內容。而 SqlCacheDependenc類就是包裝前述的 SqlDependenc 類，讓 ASP.NET 的快取記憶體失效，除了檔案變化外，也能夠透過資料庫內容的變化自動觸發更新。

我們提供的程式範例畫面如圖12-11 所示：

圖12-11 透過 ASP.NET 2.0 提供的 SqlCacheDependency 類在
SQL 資料庫內相關紀錄更新時，自動讓快取失效

整個網頁的程式程式碼範例如列表12-10：

程式程式碼列表12-10 ASP.NET 的 Cache 可以因為存放在資料庫內的資料改變而自動失效

Private Sub myBind（）

'不管是透過按鈕回來，還是以 GridView 的分頁事件回來，都可以透過此方法更新資料

If Me.Cache（"Product"） Is Nothing Then

UpdateCache（）

End If

GridView1.DataSource = CType（Me.Cache（"Product"）, DataSet）.Tables（0）

GridView1.DataBind（）

End Sub

Private Sub UpdateCache（）

Dim cnn As New SqlConnection（"Data Source=.;Initial Catalog=AdventureWorks;” & _

“Persist Security Info=True;User ID=sa;Password=password"）

Dim adp As New SqlDataAdapter（ _

"SELECT ProductID, Name, ListPrice FROM Production.Product", cnn）

'透過新的 SqlCacheDependency 讓 SQL Server 內的資料改變後，Cache 自動過期失效

Dim dep As New SqlCacheDependency（adp.SelectCommand）

Dim ds As New DataSet

adp.Fill（ds）

'加入對 SqlCacheDependency 的 Cache 機制

Me.Cache.Add（"Product", ds, dep, Caching.Cache.NoAbsoluteExpiration, _

Caching.Cache.NoSlidingExpiration, CacheItemPriority.Default, _

New CacheItemRemovedCallback（AddressOf DataDiff））

'若 Cache 中已經有相同鍵值的物件，則直接覆蓋該物件，若用 Add ，

'若已經存在該物件，就會觸發錯誤

Me.Cache.Insert（"LastUpdate", DateTime.Now）

End Sub

Private Sub DataDiff（ByVal key As String, ByVal value As Object, _

ByVal reason As CacheItemRemovedReason）

'Cache 失效後自動呼叫的 delegation 函式

UpdateCache（）

End Sub

Protected Sub Button1_Click（ByVal sender As Object, ByVal e As System.EventArgs） _

Handles Button1.Click

If Not Me.Cache（"Product"） Is Nothing Then

SqlDependency.Stop（"Data Source=.;Initial Catalog=AdventureWorks;User ID=sa;Password=password"）

SqlDependency.Start（"Data Source=.;Initial Catalog=AdventureWorks;User ID=sa;Password=password"）

Me.Cache.Remove（"Product"）

End If

End Sub

Function GetUpdateTime（） As String

'GridView 透過 Caption='' 屬性所做的資料繫結

'就直接呼叫這個函式，因為 GridView 會透過前端的 Script 只做內容的

'Post Back，而不是全網頁重新整理，所以若透過其他的物件如 Label 來

'顯示最後更新的時間，則在使用者透過選擇不同分頁回來更新資料時，

'資料自身會更新，但在網頁其他地方以 Label 控制項顯示的更新時間不會變化

'因此直接一起在 GridView 內顯示

Dim strRet As String = ""

If Me.Cache（"LastUpdate"） Is Nothing Then

strRet = "沒有快取記憶體資料"

Else

strRet = "伺服器端快取記憶體（Cache）最後更新時間：" & _

CType（Me.Cache（"LastUpdate"）, DateTime）.ToString（）

End If

Return strRet

End Function

Protected Sub GridView1_PageIndexChanging（ByVal sender As Object, _

ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs） _

Handles GridView1.PageIndexChanging

GridView1.PageIndex = e.NewPageIndex

myBind（）

End Sub

而在 Global.asax 的檔案內，我們加了以下兩段程式程式碼：

Sub Application_Start（ByVal sender As Object, ByVal e As EventArgs）

SqlDependency.Start（"Data Source=.;Initial Catalog=AdventureWorks;Persist Security Info=True;User ID=sa;Password=password"）

End Sub

Sub Application_End（ByVal sender As Object, ByVal e As EventArgs）

SqlDependency.Stop（"Data Source=.;Initial Catalog=AdventureWorks;Persist Security Info=True;User ID=sa;Password=password"）

End Sub

由於 ASP.NET 2.0 提供控制項，在更新畫面時可以僅重畫該控制項的區域性區域，而不需要重繪整個瀏覽頁面，這比較有效率且執行查詢的感覺較佳。而我們若要同時顯示資料自身的變化以及因通知而更新快取的時間，為了避免使用者僅觸發 GridView 控制項自身的事件（如換頁）而更新資料，但更新時間若以網頁中其他的控制項來顯示，則不會同時更新，因此我們將最後更新時間設計在 GridView 的 Caption 屬性上，透過資料繫結（DataBinding）的設定呼叫我們編寫的函式來取得並顯示快取最後更新的時間。

Caption=''

SQLServer2005所提供的主動通知

相關文章