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

资料来源：网络整理时间：2023/2/14 0:35:03 共计：3650 浏览

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();
                    }
                }
            }

版权说明：
本网站凡注明“广州京杭原创”的皆为本站原创文章，如需转载请注明出处！
本网转载皆注明出处，遵循行业规范，如发现作品内容版权或其它问题的，请与我们联系处理！
欢迎扫描右侧微信二维码与我们联系。

·上一条：C#曲线分析平台的制作（六，Sqldependency+Signalr+windows 服务） | ·下一条：SqlDependency .NET库可以自动检测更新