对于sqldependency类的使用（通过它来监听数据库的变化）

来源：互联网发布：相册制作软件下载编辑：程序博客网时间：2024/05/22 11:16

System.Data.SqlClient.SqlDependency类为我们提供了一个关于sql2005的很好的功能，就是sql2000时代很多人梦寐以求的查询自动通知。虽然这个东西限制有很多很多，但还是有很实用价值的。

　　我们先看一个演示例子：

　　例子中先创建一个sql环境，里面插入了一些数据，然后客户端查询，查询结果出来后再去更改数据库，增加一些记录，同时查客户端的表格是否变化。

1、创建sql测试环境

　　use master      go     create database dbTest      go      use　 dbTest     go     create table test(   id int identity(1,1),   name varchar(10)   )    insert into test(name)   select 'aa'    insert into test(name)   select 'bb'    insert into test(name)   select 'cc'    insert into test(name)   select 'dd'     insert into test(name)   select 'ee'    go    alter database dbTest set enable_broker

2、在windows下用一个form和datagridview来测试

using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Windows.Forms;using System.Data.SqlClient;using System.Configuration;namespace Lock_link_Barrier{    public partial class Dependency : Form    {        public Dependency()        {            InitializeComponent();        }        //变量        string connString = ConfigurationManager.ConnectionStrings["conString"].ToString();        SqlConnection conn = null;        SqlCommand command = null;        private void DependencyForm_Load(object sender, EventArgs e)        {            conn = new SqlConnection(connString);            command = conn.CreateCommand();            command.CommandText = "select Id,Car,Carport from [dbo].[Link] ";            SqlDependency.Start(connString);//启动            GetData();//获取数据        }        private void GetData()        {            command.Notification = null;//清除            SqlDependency dependency = new SqlDependency(command);//设置通知            dependency.OnChange += new OnChangeEventHandler(sqlDependency_OnChange);//通知事件            using (SqlDataAdapter adapter = new SqlDataAdapter(command)) //查询数据            {                System.Data.DataSet ds = new DataSet();                adapter.Fill(ds, "test");                ExcelData.DataSource = ds.Tables["test"];            }        }        void sqlDependency_OnChange(object sender, SqlNotificationEventArgs e)        {            //因为是子线程，需要用invoke方法更新ui            if (this.InvokeRequired)            {                this.Invoke(new OnChangeEventHandler(sqlDependency_OnChange), new object[] { sender, e });            }            else            {                SqlDependency dependency = (SqlDependency)sender;                dependency.OnChange -= sqlDependency_OnChange;                //通知之后，当前dependency失效，需要重新getdata并且设置通知                GetData();            }        }        //插入数据，查看监控        private void button1_Click(object sender, EventArgs e)        {            if (textBox1.Text != "" && textBox2.Text != "" && textBox3.Text!= "")            {                int a = Convert.ToInt16(textBox1.Text);                SqlConnection con = new SqlConnection(connString);                con.Open();                SqlCommand cmd = new SqlCommand("select count(*) from Link where Id="+ a,con);                if (Convert.ToInt32(cmd.ExecuteScalar()) > 0)                {                    MessageBox.Show("该Id已存在，请重新输入");                }                else                {                    string sql1 = "insert into Link (Id,Carport,Car) values (" + textBox1.Text + ",'" + textBox2.Text + "','" + textBox3.Text + "')";                    SqlCommand cm = new SqlCommand(sql1, con);                    if (cm.ExecuteNonQuery() == 1)                    {                        MessageBox.Show("插入成功");                    }                    else                    {                        MessageBox.Show("插入失败");                    }                }            }            else            {                MessageBox.Show("请输入数据");                return;            }                    }        private void DependencyForm_FormClosed(object sender, FormClosedEventArgs e)        {            //清理现场            SqlDependency.Stop(connString);            conn.Close();            conn.Dispose();        }    }}

3、启程序，表格中会有3条记录，然后切换到数据库中，继续插入一些数据

　　insert into test(name)select 'ff' insert into test(name)select 'gg'

4、切换客户端，看看表格是否改变

　　关于SqlDependency类，很多人无法测试成功，因为它的限制很多，功能可能有待加强，稍微不注意就会让上面的代码陷入死循环。特别要注意的就是command的sql语句问题:

　　select id,name from dbo.test where id<>4 order by id desc

　　很遗憾，他只能支持上面这样的简单语句

　　列明必须写，不能用*,不能用top，不能用函数，包括聚合函数，不能用子查询，包括where后的子查询，不能用外连接，自连接，不能用临时表，不能用变量，不能用视图，不能垮库，而且表名之前必须加类似dbo这样的前缀....具体有多少限制，基本上除了上述的格式或者比上述更简单的格式，其他的都不可以。表名之前必须加类似dbo数据库所有者这样的前缀）例如：select * from table1，select column1 from table1，select count(*) from table1 都是错误的sql查询语句，select column1 from dbo.table1 则是正确的语句。

对于sqldependency的使用分为三个大步骤：

1.启动到服务器的 SqlDependency 连接。在SQL Server 2005上执行ALTER DATABASE <DatabaseName> SET ENABLE_BROKER;语句让相应的数据库启用监听服务，以便支持SqlDependency特性。这条语句最好在数据库未执行任何事务的情况下执行。

2、调用SqlDependency.Start(String strConnectionString)方法，在应用程序端启用依赖监听器。如果是在Web程序中使用，建议可以将该语句放在Application_Start事件中执行。该方法的参数为一个数据库的连接字符串，该数据库必须已经执行过步骤一的操作。对于同一连接字符串，若已经执行过该语句，再次执行不会发生任何异常，但返回值会为False.监听是基于数据库的，而依赖才可以基于表或者查询。

3.创建 SqlConnection 和 SqlCommand 对象以连接到服务器并定义 Transact-SQL 语句。

4.创建一个新的 SqlDependency 对象，或使用现有的对象，并将其绑定到 SqlCommand 对象。
这会在内部创建一个 SqlNotificationRequest 对象并根据需要将其绑定到命令对象。
此通知请求包含一个内部标识符，可唯一地标识此 SqlDependency 对象。
如果客户端侦听器尚未处于活动状态，它也将启动客户端侦听器。

5、该步骤分别有两种不同的做法。该阶段必须注意步骤。
订阅一个针对 SqlDependency 对象的 OnChange 事件的事件处理程序。
使用 SqlCommand 对象的任一 Execute 方法执行该命令。
因为该命令绑定到通知对象，所以服务器认识到它必须生成一个通知，
并且队列信息将指向相关性队列。

方法A：建立连接对象，再创建一个SqlCommand实例，创建SqlCacheDependency实例，
在这步之后再调用Command对象来获取数据（这个顺序很重要）。
之后调用Cache的Insert语句建立一个依赖于一个具体查询数据集的Cache项。
SqlConnection conn = new SqlConnection(strConnection);

SqlCommand command = new SqlCommand(strCommandText, conn);

SqlCacheDependency dependency = new SqlCacheDependency(command);

// 注册方法到委托，该委托是

CacheItemRemovedCallback onRemove = new CacheItemRemovedCallback(RemovedCallback);

// 新增或修改一条缓存记录

Cache.Insert(strCacheKey,objAppCache,dependency,absoluteExpiration,slidingExpiration,CacheItemPriority.Default, onRemove);

方法B：建立连接对象，再创建一个SqlCommand实例，最后创建SqlDependency实例。定义SqlDependency的委托OnChange，当数据发生改变时做出相应的处理（比如清除Cache）。

SqlConnection conn = new SqlConnection(strConnection);
SqlCommand command = new SqlCommand(strCommandText, conn);

SqlCacheDependency dependency = new SqlCacheDependency(command);

dependency.OnChange += new OnChangeEventHandler(Dependency_OnChange);

6.停止到服务器的 SqlDependency 连接。

如果任何用户随后更改了基础数据，Microsoft SQL Server 会检测到有针对此更改的
挂起通知并发布通知，此通知经过处理后通过调用 SqlDependency.Start 而创建的基
础 SqlConnection 转发到客户端。客户端侦听程序接收无效消息。然后客户端侦听器
定位关联的 SqlDependency 对象并触发 OnChange 事件。

问题：未启用当前数据库的 SQL Server Service Broker，因此查询通知不受支持。如果希望使用通知，请为此数据库启用 Service Broker。
解决（步骤）：1、在服务器上的数据库内新建查询，
ALTER DATABASE DatabaseName SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE Databasename SET ENABLE_BROKER;
【“Databasename”为数据库名】
2、执行

0 0