使用SqlDependency监听SqlServer2005数据库变更通知
|
背景需求：对于数据中表A数据字段的变更，需要引发相应业务逻辑，插入或更新相关表或字段。在以往的方式我们多会在数据库端下文章，建立相应触发器，来完成业务逻辑操作。不过这种方式仅适用于单纯对于数据操作的需求，可是当我们要完成更复杂的业务需求是却不太容易了（虽然sql05已经支持托管代码的使用了）。可能你会想到我们可以轮询数据库相关表或视图，来发现数据的变化，可是这对于性能和即时性却是个不容易取舍的问题。不过在SqlServer2005中有了新的方案，那就是查询通知。 ??????? 查询通知是在 Microsoft SQL Server 2005 中以及 ADO.NET 2.0 的 System.Data.SqlClient 命名空间中引入的。查询通知建立在 Service Broker 基础结构的基础上，使应用程序可以在数据更改时收到通知。如果应用程序提供数据库中信息的缓存（例如 Web 应用程序），需要在源数据更改时接收通知，此功能特别有用。 通过三种方式可以使用 ADO.NET 实现查询通知：
??????? 如果应用程序需要通过刷新显示或缓存来响应基础数据中的更改，查询通知非常有用。如果执行相同命令生成的结果集与最初检索到的结果集不同，则 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; ??????? /// <summary> ??????? ///? ??????? /// </summary> ??????? /// <param name="TableName"></param> ??????? /// <param name="ColumnNames"></param> ??????? public SQLServiceBroker(string TableName,List<string> 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); ??????? } ??????? /// <param name="constr"></param> ??????? public SQLServiceBroker(string constr,string TableName,arial;font-size:14px;line-height:21px;">??????????? : this(TableName,ColumnNames) ??????????? this.connectionStr = ConfigurationManager.ConnectionStrings[constr].ToString(); ??????? ~SQLServiceBroker() ??????????? StopDependency(); ??????????? connection.Dispose(); ??????? /// <returns></returns> ??????? public bool EnoughPermission() ??????????? SqlClientPermission perm = new SqlClientPermission(System.Security.Permissions.PermissionState.Unrestricted); ??????????? try ??????????????? perm.Demand(); ??????????????? return true; ??????????? catch (System.Exception) ??????????????? return false; ??????? /// <param name="uidelegate"></param> ??????? 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）！"); ??????????????? throw new Exception("未初始化（InitDependency）！"); ??????????? this.uidel = uidelegate; ??????? /// 传入窗体对象，以防止委托有需要访问UI层控件是引发的“从不是创建控件的线程访问它” ??????? /// <param name="form1"></param> ??????? 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() ??????????? if (connection != null) ??????????????? connection.Close(); ??????? /// <param name="sender"></param> ??????? /// <param name="e"></param> ??????? 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<string> columns = new List<string>(); ??? 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 <DatabaseName> 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 from:http://hi.baidu.com/freezesoul/item/699faa36936f3bf72684f405 常见错误： 未启用当前数据库的 SQL Server Service Broker，因此查询通知不受支持。如果希望使用通知，请为此数据库启用 Service Broker 昨晚遇到的这个问题，也知道Notifications service依赖底层的Service broker的。?在一个国外论坛里面找到了解决办法： ALTER DATABASE DatabaseName SET NEW_BROKER WITH ROLLBACK IMMEDIATE; 执行以上2条语句，未做任何等待，就提示命令执行完成。 SELECT is_broker_enabled FROM sys.databases WHERE name = 'DBNAME' 查看is_broker-enabled为1 NEW_BROKER选项，SQL SERVER联机丛书上的解释： 每个数据库都包含一个 Service Broker 标识符。sys.databases?目录视图的?service_broker_guid?列显示该实例中每个数据库的 Service Broker 标识符。Service Broker 路由使用 Service Broker 标识符来保证一个会话的所有消息都传递到同一个数据库。因此，Service Broker 标识符在同一网络上的所有实例中应是唯一的。否则，消息可能被误传。 SQL Server 为每个新数据库生成新的 Service Broker 标识符。由于标识符是新的，因此 SQL Server 可以安全地激活新数据库中的 Service Broker 消息传递。网络上的其他数据库应该不会有与之相同的 Service Broker 标识符。 NEW_BROKER。此选项用于激活 Service Broker 消息传递，同时为数据库创建新的 Service Broker 标识符。由于该数据库中所有的现有会话都未使用新的实例标识符，因此，此选项将结束这些会话并返回一个错误。 ROLLBACK IMMEDIATE将立即回滚未完成的事务 from:http://www.cnblogs.com/wanghk/archive/2012/05/12/2497170.html （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |