SQLServer2005所提供的主动通知

在数据库内存放的数据的使用模式有许多是变动少而查询多的。据统计，数据库中一般有 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（http://www.netmag.com.tw） 找寻相关的信息。

针对 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=''

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/66009/viewspace-1037958/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/66009/viewspace-1037958/