使用SqlDependency监听MSSQL数据库表变化通知

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

首先要对数据库进行配置，配置命令如下：

1.启用Service Broker并查看是否启用成功，is_broker_enabled为1则表明启用成功：

ALTER DATABASE SqlDepTest SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE SqlDepTest SET ENABLE_BROKER;
SELECT is_broker_enabled FROM sys.databases WHERE name = ‘SqlDepTest’

2.开启帐号订阅权限，由于sa帐号不支持权限设置，所以需要建立单独的帐号。

use master

GRANT CREATE PROCEDURE TO sqldep
GRANT CREATE QUEUE TO sqldep
GRANT CREATE SERVICE TO sqldep

use master
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO sqldep
exec sp_helprotect NULL, sqldep

C#示例代码如下：

private const string SQL_CONNECTIONSETTINGS = "";  
  
static void Main(string[] args)  
{  
    //传入连接字符串,启动基于数据库的监听  
    SqlDependency.Start(SQL_CONNECTIONSETTINGS);  
    HandleMessage();  
  
    Console.Read();  
}  
  
/// <summary>  
/// 触发处理消息机制  
/// </summary>  
private static void HandleMessage()  
{  
    using (SqlConnection connection = new SqlConnection(SQL_CONNECTIONSETTINGS))  
    {  
        //依赖是基于某一张表的,而且查询语句只能是简单查询语句,不能带top或*,同时必须指定所有者,即类似[dbo].[] ，需要监控什么列就把列写在这里
        using (SqlCommand command = new SqlCommand("SELECT [MessageID],[Messages],[CreateTime] FROM [dbo].[Messages]", connection))  
        {  
            int messageID = 0;  
            command.CommandType = CommandType.Text;  
            connection.Open();  
            command.Notification = null;  
            SqlDependency dependency = new SqlDependency(command);  
            dependency.OnChange += new OnChangeEventHandler(Dependency_OnChange);  
  
            SqlDataReader sdr = command.ExecuteReader();  
            while (sdr.Read())  
            {  
                int.TryParse(sdr["MessageID"].ToString(), out messageID);  
                ProcessMessage(messageID);  
                Console.WriteLine("MessageID:{0}\tMessages:{1}\tCreateTime:\t{2}", sdr["MessageID"].ToString(), sdr["Messages"].ToString(), sdr["CreateTime"].ToString());  
                CompleteProcessMessage(messageID, 2);  
            }  
            sdr.Close();  
        }  
    }  
}  
  
/// <summary>  
/// SQL消息触发事件  
/// </summary>  
/// <param name="sender"></param>  
/// <param name="e"></param>  
private static void Dependency_OnChange(object sender, SqlNotificationEventArgs e)  
{  
    SqlDependency dependency = sender as SqlDependency;  
    dependency.OnChange -= Dependency_OnChange;  
    HandleMessage();  
}  
  
/// <summary>  
/// 处理消息  
/// </summary>  
/// <param name="messageID">消息编号</param>  
private static void ProcessMessage(int messageID)  
{  
    using (SqlConnection connection = new SqlConnection(SQL_CONNECTIONSETTINGS))  
    {  
        string sql = string.Format(@"INSERT INTO [dbo].[MessagesComplete]  
                       SELECT [MessageID],[Messages],1,[CreateTime],GETDATE()  
                       FROM [dbo].[Messages] WHERE [MessageID] = {0}  
                       DELETE FROM [dbo].[Messages] WHERE [MessageID] = {0}", messageID);  
        using (SqlCommand command = new SqlCommand(sql, connection))  
        {  
            command.CommandType = CommandType.Text;  
            connection.Open();  
            command.ExecuteNonQuery();  
            command.Dispose();  
        }  
    }  
}  
/// <summary>  
/// 消息处理完成，更新消息处理状态  
/// </summary>  
/// <param name="messageID">消息编号</param>  
/// <param name="status">状态：1.处理中，2.处理完成，-1.处理失败</param>  
private static void CompleteProcessMessage(int messageID, int status)  
{  
    using (SqlConnection connection = new SqlConnection(SQL_CONNECTIONSETTINGS))  
    {  
        string sql = string.Format("UPDATE [dbo].[MessagesComplete] SET [Status] = {1} WHERE [MessageID] = {0}", messageID, status);  
        using (SqlCommand command = new SqlCommand(sql, connection))  
        {  
            command.CommandType = CommandType.Text;  
            connection.Open();  
            command.ExecuteNonQuery();  
            command.Dispose();  
        }  
    }  
}  

转载于:https://blog.csdn.net/weixin_33842328/article/details/93830854
转载于:https://www.cnblogs.com/lip-blog/p/7244544.html