查询通知-实时监控SqlServer表变化

查询通知是基于Service Broker基础结构的，使应用在数据改变时接收到通知（应用程序可以请求SqlServer在查询结果发生改变时发出通知）。在ADO.NET中有三种方式：

• SqlNotificationRequest：是一种底层实现，暴露了服务端功能，可以在通知请求中执行一个命令；

• SqlDependency：一种高层抽象，用于检测服务端的改变；在大多数情况下也是最简单有效的方式；

• SqlCacheDependency：是ASP.NET2及以后提供的一个辅助类；

 

---

通知语句

每个查询通知订阅生成一个通知事件。通知发生后，SQL Server 将删除通知订阅。因此，再次需要通知的应用程序必须对检索已更改数据的命令请求新通知。

可以使用Select或Execet设定通知语句：

• Select语句要为基本的查询语句：

  • 显示指定提取的列（不能是*等），且不能是text、ntext、image类型的大型对象；

  • 表必须是基表，不能是引用试图、派生表、临时表、或表变量，表名必须有限定（即[dbo].[MyTable]）；

  • 语句中不能有复杂操作：如PIVOT、UNPIVOT、UNION、INTERSECT、EXCEPT等；

  • 语句中不能包含：DISTINCT、COMPUTE、COMPUTE BY或INTO；

  • 不能有聚合函数：AVG、COUNT(*)、MAX、MIN、STDEV、STDEVP、VAR 或 VARP；

• Execute：将执行的命令而非语句本身注册一个通知；命令有Select类似的要求与限制；

• 批处理或存储过程的订阅请求，会针对批处理或存储过程内执行的每个语句发出单独的订阅请求；

• 提交重复的活动订阅，则会导致使用新的指定超时值续订现有订阅；

 

创建通知时必须对数据库具有Create Service/Queue/Procedure权限才能使用SqlDependency，同时必须授予用户SUBSCRIBE QUERY NOTIFICATIONS权限：

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO userName

 

---

SqlDependency示例

以数据库XTest，表Tx_Flag(Changed int, Info Varchar(100)为例进行说明。

 

启用监听服务：

ALTER DATABASE XTest SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE XTest SET ENABLE_BROKER;

NEW_BROKER用于激活 Service Broker 消息传递，同时为数据库创建新的 Service Broker 标识符。所有当前已有消息订阅都会返回一个错误。

查询是否启用了Broker

SELECT is_broker_enabled FROM sys.databases WHERE name = 'XTest' 

为普通用户授予订阅权限：

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO MyName

 

开始监听服务：

string _strConnection = "Data Source = 192.168.1.100;Initial Catalog = XTest;User Id = MyName;Password = MyPsw;"

private static void QueryNotice(string strCon_)

{

    using (var con = new SqlConnection(strCon_))

    {

        using (var cmd = new SqlCommand("Select Changed From dbo.Tx_Flag", con))

        {

            con.Open();

            cmd.CommandType=CommandType.Text;

          //var dep = new SqlDependency(cmd, null, 5*60); 

            var dep = new SqlDependency(cmd); 

            dep.OnChange += new OnChangeEventHandler(dependency_OnChange);            

            cmd.ExecuteNonQuery(); //必须要执行一下command

        }

    }

}

private static void dependency_OnChange(object sender, SqlNotificationEventArgs e)

{

    Console.WriteLine(" {0}：{1} {2} {3}", DateTime.Now, e.Source, e.Info, e.Type); //Source数据库通知为Data，超时为Timeout；

    

    QueryNotice(_strConnection ); //这里要再次调用

}

 

static void Main(string[] args){

            SqlDependency.Start(_strConnection )

            QueryNotice(_strConnection )

            Console.Readline()

            SqlDependency.Stop(_strConnection )

}

 

当在Tx_Flag中插入数据、删除数据或更新Changed列时，都会收到通知。