如何用SqlDependency与C#检测数据库变更？现有代码异常排查

核心问题分析 #

1. 启动立即触发通知：SqlDependency.Start()的调用顺序错误（放在了创建SqlDependency对象之后），导致初始订阅流程触发了一次无效通知；同时默认订阅逻辑会对初始查询状态做检测，也可能引发无变更的通知。
2. 程序直接退出：Main方法执行完毕后控制台程序立即终止，进程退出后无法继续监听数据库变更。
3. 单次订阅失效：SqlDependency是一次性对象，触发一次OnChange事件后就会失效，必须重新注册才能持续监听后续变更。

修正步骤 #

• 先调用SqlDependency.Start()初始化监听服务，再创建SqlCommand和SqlDependency对象
• 阻塞主线程，保持程序运行（比如用Console.ReadLine()）
• 在OnChange事件中重新注册SqlDependency，实现持续监听
• 过滤无效通知（如订阅失效、错误等场景）

修正后的完整代码 #

using System;
using System.Data;
using System.Data.SqlClient;

namespace SqlDependencyExample
{
    class Program
    {
        static string connectionString = "xxx";
        
        static void Main(string[] args)
        {
            // 先启动SqlDependency监听服务
            SqlDependency.Start(connectionString);
            
            // 首次注册数据库变更监听
            RegisterDependencyListener();
            
            Console.WriteLine("监听已启动，按任意键退出...");
            // 阻塞主线程，防止程序退出
            Console.ReadLine();
            
            // 程序退出前停止监听服务
            SqlDependency.Stop(connectionString);
        }
        
        private static void RegisterDependencyListener()
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                
                // 注意：查询语句必须符合SqlDependency限制：显式列名、指定表Schema、不能用SELECT *
                using (SqlCommand command = new SqlCommand(
                    "SELECT [ID], [Name] FROM [dbo].[Users]", connection))
                {
                    SqlDependency dependency = new SqlDependency(command);
                    dependency.OnChange += OnDatabaseChanged;
                    
                    // 执行查询触发订阅逻辑
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        Console.WriteLine("\n当前数据：");
                        while (reader.Read())
                        {
                            Console.WriteLine("ID: {0}, Name: {1}", reader.GetInt32(0), reader.GetString(1));
                        }
                    }
                }
            }
        }
        
        private static void OnDatabaseChanged(object sender, SqlNotificationEventArgs e)
        {
            // 过滤有效变更通知（仅处理新增行的情况，可根据需求调整）
            if (e.Type == SqlNotificationType.Change && e.Info == SqlNotificationInfo.Insert)
            {
                Console.WriteLine("\n检测到数据库新增行！");
                // 在这里执行你的业务方法
            }
            else if (e.Type == SqlNotificationType.Subscribe)
            {
                Console.WriteLine($"\n订阅失败：{e.Info}");
            }
            
            // 重新注册监听，实现持续检测
            RegisterDependencyListener();
        }
    }
}

额外注意事项 #

• 确保数据库ServiceBroker已激活（你已完成此步骤）
• 查询语句必须严格符合SqlDependency的限制：不能使用SELECT *、必须指定表的完整Schema（如dbo.Users）、不能包含聚合函数、TOP子句等
• 连接字符串对应的数据库用户需要拥有SUBSCRIBE QUERY NOTIFICATIONS权限

内容的提问来源于stack exchange，提问作者Robin Schu