|
直接上代码 ：
?????? 声明部分?:?????????
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Windows.Forms;
namespace FrontAlarm
{
??? public class SQLServiceBroker
??? {
??????? private string connectionStr = "Data Source=.;Initial Catalog=Test;User ID=cc;Password=cc;";
??????? 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);
??????? }
??????? /// <summary>
???????
??????? /// </summary>
??????? /// <param name="constr"></param>
??????? /// <param name="TableName"></param>
??????? /// <param name="ColumnNames"></param>
??????? public SQLServiceBroker(string constr,string TableName,List<string> ColumnNames)
??????????? : this(TableName,ColumnNames)
??????? {
??????????? this.connectionStr = "Data Source=.;Initial Catalog=Test;User ID=cc;Password=cc;";ConfigurationManager.ConnectionStrings[constr].ToString();
??????? }
??????? /// <summary>
??????? ///
??????? /// </summary>
??????? ~SQLServiceBroker()
??????? {
??????????? StopDependency();
??????????? connection.Dispose();
??????? }
??????? /// <summary>
??????? ///
??????? /// </summary>
??????? /// <returns></returns>
??????? public bool EnoughPermission()
??????? {
??????????? SqlClientPermission perm = new SqlClientPermission(System.Security.Permissions.PermissionState.Unrestricted);
??????????? try
??????????? {
??????????????? perm.Demand();
??????????????? return true;
??????????? }
??????????? catch (System.Exception)
??????????? {
??????????????? return false;
??????????? }
??????? }
??????? /// <summary>
??????? ///
??????? /// </summary>
??????? /// <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）！");
??????????? if (connection == null)
??????????????? throw new Exception("未初始化（InitDependency）！");
??????????? this.uidel = uidelegate;
??????? }
??????? /// <summary>
??????? /// 传入窗体对象，以防止委托有需要访问UI层控件是引发的“从不是创建控件的线程访问它”
??????? /// </summary>
??????? /// <param name="form1"></param>
??????? /// <param name="uidelegate"></param>
??????? public void InitDependency(Form form1,UIDelegate uidelegate)
??????? {
??????????? InitDependency(uidelegate);
??????????? this.form = form1;
??????? }
??????? /// <summary>
??????? ///
??????? /// </summary>
??????? 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();
??????????? }
??????? }
??????? /// <summary>
??????? ///
??????? /// </summary>
??????? public void StopDependency()
??????? {
??????????? SqlDependency.Stop(connectionStr);
??????????? if (connection != null)
??????????????? connection.Close();
??????? }
??????? /// <summary>
??????? ///
??????? /// </summary>
??????? /// <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 DependBrok()
??????? {
??????????? //需要监测的列
??????????? 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("数据有变化o(∩_∩)o 哈哈");
??????? }
??????? 关于SqlDependency类，很多人无法测试成功，因为它的限制很多，功能可能有待加强，稍微不注意就会让上面的代码陷入死循环。特别要注意的就是command的sql语句问题:
select id,name from dbo.test where id <>4 order by id? desc
很遗憾，他只能支持上面这样的简单语句
列明必须写，不能用*,不能用top，不能用函数，包括聚合函数，不能用子查询，包括where后的子查询，不能用外连接，自连接，不能用临时表，不能用变量，不能用视图，不能垮库，而且表名之前必须加类似dbo这样的前缀....具体有多少限制，基本上除了上述的格式或者比上述更简单的格式，其他的都不可以。
?
注意
如果在数据库中执行 ‘ALTER DATABASE?dbTest SET?ENABLE_BROKER?’ 出现死循环 执行下列语句就可以了
ALTER DATABASE?dbTest SET NEW_BROKER WITH ROLLBACK IMMEDIATE
ALTER DATABASE?dbTest SET ENABLE_BROKER
如果有没成功的 可以给我留言 我解决了这个? 希望可以帮到需要的同胞
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
