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

概述直接上代码 ：        声明部分 :          using System; using System.Collections.Generic; using System.Text; using System.Data.SqlClient; using System.Data; using System.Configuration; using System.Windows.Forms

直接上代码 ：

       声明部分 :         

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

如果有没成功的 可以给我留言 我解决了这个  希望可以帮到需要的同胞

总结

以上是内存溢出为你收集整理的使用SqlDependency监听SqlServer2005数据库变更通知全部内容，希望文章能够帮你解决使用SqlDependency监听SqlServer2005数据库变更通知所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。