使用SqlDependency监听SqlServer2005数据库变更通知

背景需求：对于数据中表A数据字段的变更，需要引发相应业务逻辑，插入或更新相关表或字段。在以往的方式我们多会在数据库端下文章，建立相应触发器，来完成业务逻辑操作。不过这种方式仅适用于单纯对于数据操作的需求，可是当我们要完成更复杂的业务需求是却

背景需求：对于数据中表A数据字段的变更，需要引发相应业务逻辑，插入或更新相关表或字段。在以往的方式我们多会在数据库端下文章，建立相应触发器，来完成业务逻辑操作。不过这种方式仅适用于单纯对于数据操作的需求，可是当我们要完成更复杂的业务需求是却不太容易了（虽然sql05已经支持托管代码的使用了）。可能你会想到我们可以轮询数据库相关表或视图，来发现数据的变化，可是这对于性能和即时性却是个不容易取舍的问题。不过在SqlServer2005中有了新的方案，那就是查询通知。

查询通知是在 Microsoft SQL Server 2005 中以及 ADO.NET 2.0 的 System.Data.SqlClient 命名空间中引入的。查询通知建立在 Service Broker 基础结构的基础上，使应用程序可以在数据更改时收到通知。如果应用程序提供数据库中信息的缓存（例如 Web 应用程序），需要在源数据更改时接收通知，此功能特别有用。

通过三种方式可以使用 ADO.NET 实现查询通知：

1. 低级实现由 SqlNotificationRequest 类提供，该类公开服务器端功能，使您可以对通知请求执行命令。

2. 高级实现由 SqlDependency 类提供，该类提供源应用程序与 SQL Server 之间通知功能的高级抽象，使您可以使用相关性来检测服务器中的更改。大多数情况下，这是托管客户端应用程序通过适用于 SQL Server 的 .NET Framework 数据提供程序利用 SQL Server 通知功能的最简单、最有效的方法。

3. 此外，使用 ASP.NET 2.0（或更高版本）构建的 Web 应用程序可以使用 SqlCacheDependency 帮助器类。

如果应用程序需要通过刷新显示或缓存来响应基础数据中的更改，查询通知非常有用。如果执行相同命令生成的结果集与最初检索到的结果集不同，则 Microsoft SQL Server 可允许 .NET Framework 应用程序向 SQL Server 发送命令和请求通知。服务器上生成的通知通过队列发送，供以后处理。

您可以为 SELECT 和 EXECUTE 语句设置通知。使用 EXECUTE 语句时，SQL Server 会为执行的命令而不是 EXECUTE 语句本身注册通知。该命令必须满足 SELECT 语句的要求和限制。当注册通知的命令包含多个语句时，数据库引擎会为批处理中的每个语句创建一个通知。

使用查询通知的应用程序有一组通用的要求。必须正确配置数据源才能支持 SQL 查询通知，并且用户必须具有正确的客户端和服务器端权限。

要使用查询通知，必须符合下列条件：

1.使用 SQL Server 2005 或 SQL Server 2008。

2.对数据库启用查询通知。

3.确保用于连接数据库的用户 ID 具有必要的权限。

4.使用 SqlCommand 对象执行有效的 SELECT 语句，包含关联的通知对象 — SqlDependency 或 SqlNotificationRequest。

5.提供所监视的数据更改时用于处理通知的代码。

下面就以一个例子来说明使用SqlDependency的整个流程

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Windows.Forms;

namespace CaptureWeb
{
public class SQLServiceBroker
{

private string connectionStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();

private string sqlStr = "";

private SqlConnection connection = null;

public delegate void UIDelegate();

private UIDelegate uidel = null;

public Form form = null;

///

///
///
///
///
public SQLServiceBroker(string TableName, List ColumnNames)
{
string columns = "";
foreach (string str in ColumnNames)
{
if (columns != "")
columns = columns + ",";
columns = columns + "[" + str + "]";
}
this.sqlStr = string.Format("select {0} From [dbo].[{1}]", columns, TableName);
}

///

///
///
///
///
///
public SQLServiceBroker(string constr, string TableName, List ColumnNames)
: this(TableName, ColumnNames)
{
this.connectionStr = ConfigurationManager.ConnectionStrings[constr].ToString();
}


///

///
///
~SQLServiceBroker()
{
StopDependency();
connection.Dispose();
}

///

///
///
///
public bool EnoughPermission()
{

SqlClientPermission perm = new SqlClientPermission(System.Security.Permissions.PermissionState.Unrestricted);
try
{
perm.Demand();
return true;
}
catch (System.Exception)
{
return false;
}
}

///

///
///
///
public void InitDependency(UIDelegate uidelegate)
{

SqlDependency.Stop(connectionStr);
SqlDependency.Start(connectionStr);
if (connection == null)
connection = new SqlConnection(connectionStr);

if (!EnoughPermission())
throw new Exception("没有权限（SqlClientPermission）！");
if (uidelegate == null)
throw new Exception("回调方法未指定（UIDelegate）！");
if (connection == null)
throw new Exception("未初始化（InitDependency）！");
this.uidel = uidelegate;

}

///

/// 传入窗体对象，以防止委托有需要访问UI层控件是引发的“从不是创建控件的线程访问它”
///
///
///
public void InitDependency(Form form1, UIDelegate uidelegate)
{
InitDependency(uidelegate);
this.form = form1;
}

///

///
///
public void StartDependency()
{
//这里很奇怪，每次都需要新的command对象
using (SqlCommand command = new SqlCommand(sqlStr, connection))
{
command.Notification = null;
SqlDependency dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
if (connection.State != ConnectionState.Open)
connection.Open();
command.ExecuteNonQuery();
command.Dispose();
}
}

///

///
///
public void StopDependency()
{
SqlDependency.Stop(connectionStr);
if (connection != null)
connection.Close();
}

///

///
///
///
///
private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
//注销监测事件
SqlDependency dependency = (SqlDependency)sender;
dependency.OnChange -= dependency_OnChange;
//放在移除事件之后又很大必要，防止ui层调用更新相同表时，进入循环出发调用
//uidel.Invoke();
//uidel();
//使用from.Invoke调用防止访问界面控件引发“从不是创建控件的线程访问它”
if (form != null)
form.Invoke(uidel);
else
uidel();
//再次启动监听
StartDependency();
}

}
}

调用方式：

SQLServiceBroker broker;
private void button1_Click(object sender, EventArgs e)
{
//需要监测的列
List columns = new List();
columns.Add("test1");
columns.Add("test2");
string table = "test";
broker = new SQLServiceBroker(table, columns);
//实例化毁掉函数
SQLServiceBroker.UIDelegate uidel = new SQLServiceBroker.UIDelegate(writeCon);
//初始化，及传入回调函数
broker.InitDependency(uidel);
//初始化，传入窗体对象对于需要委托中访问ui控件的情况
//broker.InitDependency(this, uidel);
//启动监听
broker.StartDependency();
MessageBox.Show("启动");
}

private void writeCon()
{
MessageBox.Show("changed");
}

代码比较简单，都有说明，这里有必要注意几点问题：

1.首先需要在SQL Server 2005上执行ALTER DATABASE SET ENABLE_BROKER;语句让相应的数据库启用监听服务，以便支持SqlDependency特性。

2.对于SqlCommand的cmdText有特殊要求，其中不能用*，不能用top，不能用函数，包括聚合函数，不能用子查询，包括where后的子查询，不能用外连接，自连接，不能用临时表，不能用变量，不能用视图，不能垮库，表名之前必须加类似dbo数据库所有者这样的前缀。

3.其中在使用当中发现SqlConnection对象应该是一直存在的，因此在此示例中升级为属性，如果将它声明在StartDependency方法体中，出现只能调用一次的情况，因为对于SqlDependency需要connection对象的存在。

4.在使用委托传入调用方法是，如果方法有访问界面UI控件的情况，需要传入窗体对象，以form.Invoke(uidel);的方式调用，否则会引发“从不是创建控件的线程访问它”异常。

5.对于回调函数中需要更新正在监听的表时防止循环调用造成死循环，请在调用委托之前先移除onchange事件dependency.OnChange -= dependency_OnChange;

整个项目Demo：http://cid-9601b7b7f2063d42.skydrive.live.com/self.aspx/Code/SQLServiceBroker.rar

http://support.microsoft.com/kb/555893/

CodeProject：http://www.codeproject.com/KB/database/chatter.aspx

MSDN： http://msdn.microsoft.com/zh-cn/library/t9x04ed2.aspx

出现的问题：sql Service Broker 未开启

开启方法：http://pan.baidu.com/share/link?shareid=1850603785&uk=3675091944

未启用当前数据库的 SQL Server Service Broker，因此查询通知不受支持。如果希望使用通知，请为此数据库启用 Service Broker。



解决办法：
在Microsoft SQL Server Management Studio Express新建查询中执行如下语句
alter database DotShoppingCart set enable_broker

若命令执行成功的话，验证一下，执行下面SQL语句
select IS_BROKER_ENABLED from master.sys.databases where name='DotShoppingCart'
若出现下图所示，则配置成功


注：DotShoppingCart是一个数据库












2

扩展阅读
Service Broker 端点
SQL Server 使用 Service Broker“端点”使 Service Broker 与 SQL Server 实例外部进行通信。 端点就是一个 SQL Server 对象，代表 SQL Server 进行网络通信的功能。每个端点支持一种特定的通信类型。例如，HTTP 端点使 SQL Server 可以处理特定的 SOAP 请求。Service Broker 端点将 SQL Server 配置为通过网络发送和接收 Service Broker 消息。
Service Broker 端点为传输安全模式和消息转发提供选项。Service Broker 端点侦听特定的 TCP 端口号。
默认情况下，SQL Server 的实例不包含 Service Broker 端点。因此，默认情况下 Service Broker 不通过网络发送或接收消息。必须创建 Service Broker 端点才能向 SQL Server 实例外部发送消息或从该实例外部接收消息。有关创建 Service Broker 端点的详细信息，请参阅 CREATE ENDPOINT (Transact-SQL)。一个实例可以只包含一个 Service Broker 端点。
安全说明
创建 Service Broker 端点后，SQL Server 将接受在该端点中指定的端口上的 TCP/IP 连接。Service Broker 传输安全模式要求授权后才能连接该端口。如果运行 SQL Server 的计算机启用了防火墙，则该计算机上的防火墙配置必须允许该端点中所指定端口的传入和传出连接。有关 Service Broker 传输安全模式的详细信息，请参阅 Service Broker 传输安全性。

Service Broker 传输安全性
Service Broker 传输安全性使数据库管理员可以对与数据库的网络连接进行限制并且可以加密网络上的消息。Service Broker 端点支持基于证书的身份验证和 Windows 身份验证。
传输安全性应用于两个实例间的网络连接。传输安全性控制哪些实例可以通信并提供两个实例间的加密。
传输安全性作为一个整体应用于实例。传输安全性不保护各个消息的内容，也不控制对实例中各个服务的访问。Service Broker 对话安全性在各消息离开发送实例至到达目标实例之间，对消息进行加密。 实例使用的身份验证类型取决于每个实例的 Service Broker 端点的 AUTHENTICATION 选项。当端点指定多个身份验证方法时，具体使用哪个身份验证方法取决于为发起连接的实例指定这些方法时的












3
顺序。协商期间，每个实例都报告其所支持的所有身份验证类型和算法。发起方按照接受方指定的顺序尝试两个端点都支持的身份验证方法。这意味着，对于长时间运行的会话，消息可能通过多个连接来交换，并且连接的身份验证可能随着发起会话的实例不同而不同。
Service Broker 端点支持两种类型的加密。与身份验证一样，具体用于连接的加密方法取决于为发起连接的实例指定这些方法时的顺序。

解析SQL Server 2005中的Service Broker
来源：68design.net 2007年08月29日 09:08 网友评论：0条 点击：
740
SQL Server 2005中的新内容Service Broker，可用来建立以异步消息为基础的应用。Service Broker应用是一个或者多个程序的集合，能够完成一套相关的任务。为了更加深入的了解其涵义，让我们来看看组成应用的各个对象。 消息器
消息是Service Broker应用中信息传递的基本单元。在Service Broker内部，消息是按发送顺序进行接收,并且保证每个消息只会发送和接收一次。而且消息保证不会丢失。有时，某个消息已被发送，但是没有马上收到。当发生这种情况时，Service Broker会保存消息并尝试再次发送。消息带有确认信息以确保经他们传递的信息就是他们所等待接收饿。可以传递的消息最大可达2G。 会话
当消息在Service Broker应用中传递使使用会话（或者对话）方法。会话一般针对特别任务生成，当任务完成以后就会被删除。会话才是Service Broker最主要的信息交换结构，而不是消息。会话发生在两个服务端点之间：发起会话的服务(发起方)，以及接受会话需求的服务（目的方）。 队列
在Service Broker应用中，消息以队列方式保存等待接受处理。在内部，Service Broker队列是一种特殊的表格，能够通过指明队列名称的SELECT语句进行查看，不能在队列中执行INSERT, UPDATE, 或者DELETE语句。保存在












4
队列中的消息即使重新启动服务器也不会丢失。 服务
服务程序是读取并处理队列中的消息的程序。这种服务可以是特定的存储程序，或者连接数据库的不同程序。每个服务必须与队列相关。正如先前所提到的，会话发生在服务之间。 会话组
会话组用来接连消息的处理过程并使之相互关联。每个会话是会话组中的一份子。主要的概念是有些消息与其他消息相关，会话组将这些相关的会话按照要求的顺序结合在一起。事实上，所进行的处理具有对会话组里的全部消息的高级连续访问权限，直到处理完成。
Service Broker 应用还有很多其他相关的部分。以上提到的各个部分在Service Broker起主要作用。您对它们越熟悉，您就会更熟练的掌握Service Broker应用的编写。现在，我们来看如何使用Service Broker应用来实现业务交易。
业务处理
业务流程中的任务很少按照同步进行。这些流程经常由彼此独立的任务组成，但是很可能同时发生，可能重叠，可能需要流程中别的步骤的支持。这种情况经常出现在生产产品的过程中，特别是客户订制的生产过程，比如汽车生产。 当有人预订一辆定制的汽车，汽车各个部件的生产过程并不彼此依赖。例如，这些部件可以同时生产。但是在最后阶段，当进行组装时你会遇到下面的问题： ·取决于前一步骤的步骤。
·如果出现错误会对整个项目的成功起绝对性影响的步骤。 ·需要购买者补充信息的步骤。
除了这种情况以外，如果潜在购买者取消了订单，那么进行补偿的程序也要符合逻辑。您可能对有类似特征的业务流程比较熟悉。
当数据库执行这样的流程时，经常按一系列数据库交易进行处理，每个交易都有单独的基本任务。当其中一个数据库交易被接受或退回时，这一系列相关的业务交易通常都无法以此方法完成。这些交易必须被设计成失败时，通过逻辑判断退回业务交易。整个业务程序都很难进行，因为这些独立的交易实际上是于彼此相关的，他们都包含同样的总体目标。这是Service Broker这样的队列结构的实际价值所在。












5
在Service Broker应用中，目前的处理方法是可能的，也经常是人们所需要的。您能够以这种方法建立应用模式，使模式更符合业务流程。在我们定制的汽车行业的例子中，您能够以这样的方式设计应用，使得跟踪底盘的模块和跟踪引擎的模块能够同时出现。更好的，对这两个独立的零件的处理通过对话组可以相互联系。