如何利用C实时监听SQL数据库变更并快速响应？

使用SqlDependency和查询通知

原理
SqlDependency是.NET提供的类库，基于SQL Server的查询通知机制（Query Notification），当数据库的查询结果发生变化时,系统会通过事件通知应用程序。

实现步骤

1. 启用Service Broker
   确保SQL Server实例的Service Broker已启用：

   ALTER DATABASE YourDatabase SET ENABLE_BROKER;

2. 配置数据库权限
   用户需要具备SUBSCRIBE QUERY NOTIFICATIONS权限：

   GRANT SUBSCRIBE QUERY NOTIFICATIONS TO YourUser;

3. C#代码实现

   using System.Data.SqlClient;
   using System.Web.Caching;
   public class DatabaseListener
   {
       private SqlDependency _dependency;
       private string _connectionString = "Server=.;Database=YourDB;Integrated Security=True;";
       public void StartListening()
       {
           SqlDependency.Start(_connectionString); // 初始化监听
           using (var connection = new SqlConnection(_connectionString))
           {
               connection.Open();
               var command = new SqlCommand(
                   "SELECT [Id], [Name] FROM dbo.YourTable", 
                   connection
               );
               _dependency = new SqlDependency(command);
               _dependency.OnChange += OnDependencyChange; // 注册事件
               command.ExecuteNonQuery();
           }
       }
       private void OnDependencyChange(object sender, SqlNotificationEventArgs e)
       {
           if (e.Type == SqlNotificationType.Change)
           {
               // 处理变更逻辑
               Console.WriteLine("数据发生变化！");
               StartListening(); // 重启监听
           }
       }
       public void StopListening()
       {
           SqlDependency.Stop(_connectionString);
       }
   }

注意事项

• 查询必须满足特定条件（如不能使用、必须指定表所有者dbo）。
• 长时间运行的应用需处理连接池和内存泄漏问题。
• 仅支持SQL Server 2005及以上版本。

轮询数据库（Polling）

适用场景
若数据库版本较低或需要更灵活的查询控制,可通过定时轮询实现监听。

实现示例

using System.Timers;
public class PollingService
{
    private Timer _timer;
    private string _lastHash;
    public void StartPolling()
    {
        _timer = new Timer(5000); // 5秒轮询一次
        _timer.Elapsed += CheckDataChange;
        _timer.Start();
    }
    private void CheckDataChange(object sender, ElapsedEventArgs e)
    {
        var currentHash = GetDataHash();
        if (currentHash != _lastHash)
        {
            Console.WriteLine("数据已更新");
            _lastHash = currentHash;
        }
    }
    private string GetDataHash()
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            var command = new SqlCommand(
                "SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM dbo.YourTable", 
                connection
            );
            connection.Open();
            return command.ExecuteScalar().ToString();
        }
    }
}

优化建议

• 添加索引加快CHECKSUM_AGG计算速度。
• 根据业务需求调整轮询间隔。

SQL Server变更跟踪（Change Tracking）

优势
内置于SQL Server，可跟踪行级变更，并提供变更类型（新增、修改、删除）。

配置与使用

1. 启用变更跟踪

   ALTER DATABASE YourDatabase SET CHANGE_TRACKING = ON;
   ALTER TABLE dbo.YourTable ENABLE CHANGE_TRACKING;

2. C#中获取变更

   var command = new SqlCommand(@"
       SELECT 
           CT.Id, 
           CT.SYS_CHANGE_OPERATION, 
           CT.SYS_CHANGE_VERSION
       FROM CHANGETABLE(CHANGES dbo.YourTable, 0) AS CT",
       connection
   );
   // 解析结果并记录最后同步版本号

触发器 + 外部服务

架构设计

1. 创建数据库触发器,将变更记录到专用表。
2. 使用独立服务（如Windows Service）监听专用表并推送通知。

触发器示例

CREATE TRIGGER [dbo].[YourTable_ChangeTrigger]
ON [dbo].[YourTable]
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    INSERT INTO ChangeLog(TableName, ChangeType, ChangeTime)
    VALUES ('YourTable', 'INSERT/UPDATE/DELETE', GETDATE());
END

性能与安全建议

1. 资源占用

   • SqlDependency在数据频繁变更时可能产生高负载。
   • 轮询方式需平衡实时性和性能。

2. 安全实践

   • 最小权限原则：监听服务仅需SELECT和SUBSCRIBE QUERY NOTIFICATIONS权限。
   • 加密敏感数据字段。

方案对比

引用说明

• Microsoft Docs | SqlDependency Class: 链接
• SQL Server变更跟踪: 链接