使用SqlDependency类及利用SQL2008的Service Broker来检测数据改动的实现

来源：互联网发布：数据库查询结果转json 编辑：程序博客网时间：2024/05/20 14:16

sqlDependency提供了这样一种能力:当被监测的数据库中的数据发生变化时,SqlDependency会自动触发OnChange事件来通知应用程序,从而达到让系统自动更新数据(或缓存)的目的.

1.创建一个测试数据库map，新建一个表Users，添加一些数据

查询刚刚创建的数据库是否支持启用Broker功能：（注：默认情况下新创建的数据库会启用Broker功能)

在SQL中，新建查询- 输入以下代码：Select DATABASEpRoPERTYEX(''map'',''IsBrokerEnabled'') -执行(map为数据库名称)

-- 1表示已经启用 0表示没有启用

如果结果为0表示此数据库没有启用Broker功能，执行下面的代码来启用

ALTER DATABASE map SET NEW_BROKER WITH ROLLBACK IMMEDIATE;  ALTER DATABASE map SET ENABLE_BROKER;

.2、控制台程序主要代码

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Configuration;  using System.Data;  using System.Data.SqlClient;namespace ConsoleApplication1{    class Program    {        private static string _connStr;         static void Main(string[] args)        {                     _connStr = System.Configuration.ConfigurationManager.ConnectionStrings["testconect"].ToString();             SqlDependency.Start(_connStr);//传入连接字符串,启动基于数据库的监听             UpdateGrid();              Console.Read();          }                private static void UpdateGrid()          {             using (SqlConnection connection = new SqlConnection(_connStr))              {                  //依赖是基于某一张表的,而且查询语句只能是简单查询语句,不能带top或*,同时必须指定所有者,即类似[dbo].[]                  using (SqlCommand command = new SqlCommand("select U_Name,U_power,U_Tel From [dbo].[Users]", 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}\tUserId:{1}\tMessage:{2}", sdr["U_Name"].ToString(), sdr["U_power"].ToString(),sdr["U_Tel"].ToString());                     }                     sdr.Close();                  }              }          }            private static void dependency_OnChange(object sender, SqlNotificationEventArgs e)          {              UpdateGrid();          }      }}

3.测试运行

运行该控制台程序后,会输出Users表的所有数据,这时不要关闭控制台程序,直接在Sql2008的管理器里对Users表的数据做些修改(比如新增/删除等),再看一下控制台,会发现自动重新输出了新的数据.

若出现：使用SqlDependency时提示当前数据库未启用Service broker，参考第一步

ALTER DATABASE 数据库名称SET NEW_BROKER WITHROLLBACK IMMEDIATE;
ALTER DATABASE 数据库名称SET ENABLE_BROKER;

注意：

使用 SqlDependency 订阅查询通知必须向SQL Server Service Broker提供制定规则的查询语句，一般来讲，必须是简单的sql查询语句（不能用*，不能用top，不能用函数，包括聚合函数，不能用子查询，包括where后的子查询，不能用外连接，自连接，不能用临时表，不能用变量，不能用视图，不能垮库，表名之前必须加类似dbo数据库所有者这样的前缀）例如：select * from table1，select column1 from table1，select count(*) from table1 都是错误的sql查询语句，select column1 from dbo.table1 则是正确的语句。

0 0