使用SqlDependency实现程序对于数据库中表数据变化的监视
好久没有写博客了，感觉有点生疏了。
今天和大家分享一个数据库变化自动通知客户端的技巧，现在还是局限在sqlServer上的使用，在文章最后附上源码
今天的主角就是SqlDependency--实现数据库中对于某一张表的监视，好了。废话不多说了，开始搞，今天我新建了个简单的WPF项目如下：
1、首先需要对数据库的设置：
** 1、设置某个数据库代理的回滚 * ALTER DATABASE [test] SET NEW_BROKER WITH ROLLBACK IMMEDIATE; * 2、设置某个数据库的代理 * ALTER DATABASE [test] SET ENABLE_BROKER; * 3、查询某个数据库是否已经启动了代理 * SELECT name,is_broker_enabled FROM sys.databases WHERE name = 'yaozheng' * is_broker_enabled 为0表示未启动代理 1表示已启动代理
2、开始c#代码 ，初始化方法中启动监听器
/// <summary>
/// 初始化
/// </summary>
public MainWindow()
{
InitializeComponent();
// 启动侦听器来接收来自通过连接字符串指定的 SQL Server 实例的依赖项更改通知。
SqlDependency.Start(_connStr);
SelectData();
}
3、写一个简单的数据库查询SelectData，重点就是
SqlDependency dependency = new SqlDependency(command); // 事件注册，这是核心 dependency.OnChange += new OnChangeEventHandler(Dependency_OnChange);
详细如下：
/// <summary> /// 数据库查询操作 /// </summary> private static void SelectData() { using (SqlConnection connection = new SqlConnection(_connStr)) { //依赖是基于某一张表的,而且查询语句只能是简单查询语句,不能带top或*,同时必须指定所有者,即类似[dbo].[] string cmdText = "SELECT [ID],[Name],[Age] from dbo.Test_Table where [Age] = 1"; using (SqlCommand command = new SqlCommand(cmdText, connection)) { command.CommandType = CommandType.Text; connection.Open(); SqlDependency dependency = new SqlDependency(command); // 事件注册，这是核心 dependency.OnChange += new OnChangeEventHandler(Dependency_OnChange); SqlDataReader sdr = command.ExecuteReader(); Console.WriteLine(); while (sdr.Read()) { Console.WriteLine("Id:{0}\\Name:{1}\\Age:{2}", sdr["ID"].ToString(), sdr["Name"].ToString(), sdr["Age"].ToString()); } sdr.Close(); } } }
4、实现Dependency_OnChange方法，在数据库中数据发生变化时,重新获取
/// <summary> /// 具体事件 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private static void Dependency_OnChange(object sender, SqlNotificationEventArgs e) { // 只有数据发生变化时,才重新获取数据 if (e.Type == SqlNotificationType.Change) { SelectData(); } }
5、WPF还有一个资源的自动释放，关闭监听
/// <summary>
/// 注意资源的释放 关闭监听
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Window_Closed(object sender, EventArgs e)
{
SqlDependency.Stop(_connStr);
}
以上操作就可以实现数据库数据变化时自动触发 SelectData() 方法。
以下重点说下注意事项，是我在实际使用过程中踩过的坑，希望大家可以避免：
* 1、应用程序开始或者结束时，必须相应的开始或者停止对SQL Server的监控。
* 2、只有SQL语句中需要查询的字段才会被监控，没有查询的数据发生变化时，并不会触发dependency_OnChange事件。
* 3、查询语句只能是简单查询语句,不能带top，不能使用*，不能使用函数包括聚合函数，包括where子查询
* 4、不能使用外连接、自连接、不能使用临时表、不能用变量、不能用试图、不能跨表、表名前必须加类型dbo的前缀
* 5、待查询的字段的数据也不能太复杂。测试时，有个字段保存Json格式的数据。如果将这个字段也写入到SQL语句中，则不会被监控到。
* 6、OnChange只能提供一次通知，如果需要重新发起，需要重新添加事件
最后附上源码
源码：https://github.com/YaoHigh/SqlDependencyTest
