.NET Core 2.0中SQLNotifications使用问题及数据库变更通知咨询

.NET Core 2.0是否支持SQL Notifications？ #

很遗憾，.NET Core 2.0并不支持传统的SQL Server查询通知（也就是SqlDependency/SqlCommand.Notification相关功能）。这个特性是在.NET Core 3.0版本才被正式引入到Microsoft.Data.SqlClient库中的，所以你在2.0环境下尝试访问SqlCommand.Notification会出现“不包含定义”的错误——因为这个属性在当时的Core版本里根本不存在。

结合SignalR实现数据库变更通知的方案 #

方案1：不升级.NET Core 2.0的替代方案 #

因为2.0不支持SqlDependency，我们可以用以下几种方式来实现需求：

• 轮询数据库（最简单的临时方案）
  在后台服务（比如用IHostedService）中定时查询数据库，对比上次查询的结果或记录的最后变更时间，如果发现有更新，就通过SignalR推送给客户端。
  示例思路：

  // 后台服务中的轮询逻辑
  private async Task ExecuteAsync(CancellationToken stoppingToken)
  {
      while (!stoppingToken.IsCancellationRequested)
      {
          var latestChanges = await _dbContext.Records.Where(r => r.LastUpdated > _lastCheckTime).ToListAsync();
          if (latestChanges.Any())
          {
              await _hubContext.Clients.All.SendAsync("RecordUpdated", latestChanges);
              _lastCheckTime = DateTime.UtcNow;
          }
          await Task.Delay(TimeSpan.FromSeconds(5), stoppingToken); // 5秒轮询一次
      }
  }
  

  缺点：实时性差，频繁轮询会增加数据库压力。

• 数据库触发器+变更日志表+后台监听

  1. 在数据库中创建一个变更日志表，记录被修改的表、变更类型（增/删/改）、变更时间、主键ID等信息。
  2. 为需要监控的业务表创建触发器，当表发生增删改操作时，自动向变更日志表插入一条记录。
  3. 后台用IHostedService监听变更日志表（可以用短轮询，或者用更高效的方式比如查询最新日志ID），一旦发现新的日志，就通过SignalR推送通知给客户端。
     这种方案比单纯轮询业务表更高效，因为只需要查询小体积的日志表。

方案2：升级到.NET Core 3.0+后使用SqlDependency+SignalR #

如果可以升级项目到.NET Core 3.0及以上版本，就能使用官方的SqlDependency来实现实时的数据库变更通知，配合SignalR推送会更高效：

1. 启用SQL Server的Service Broker
   首先需要在目标数据库上启用Service Broker，执行SQL命令：

   ALTER DATABASE YourDatabaseName SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
   

2. 配置SqlDependency并监听查询
   在后台服务中初始化SqlDependency，并注册查询通知：

   private async Task InitializeSqlDependency()
   {
       // 初始化SqlDependency
       SqlDependency.Start(_connectionString);
       
       using var connection = new SqlConnection(_connectionString);
       await connection.OpenAsync();
       
       using var command = new SqlCommand("SELECT Id, Name, LastUpdated FROM dbo.YourTable", connection);
       // 必须指定NotificationAutoEnlist = true
       command.Notification = null;
       var dependency = new SqlDependency(command);
       dependency.OnChange += OnDependencyChange;
       
       // 执行查询来激活通知
       await command.ExecuteReaderAsync();
   }
   
   private async void OnDependencyChange(object sender, SqlNotificationEventArgs e)
   {
       if (e.Type == SqlNotificationType.Change)
       {
           // 通过SignalR推送变更通知
           await _hubContext.Clients.All.SendAsync("RecordUpdated");
           
           // 重新注册监听，因为SqlDependency触发一次后会自动取消
           await InitializeSqlDependency();
       }
   }
   

3. SignalR Hub实现
   创建一个简单的SignalR Hub来处理客户端连接：

   public class NotificationHub : Hub
   {
       // 可以在这里添加客户端连接管理逻辑，比如分组通知
   }
   

4. Startup中配置SignalR
   在Startup.cs中注册SignalR服务并映射Hub：

   public void ConfigureServices(IServiceCollection services)
   {
       services.AddSignalR();
       // 其他服务注册...
   }
   
   public void Configure(IApplicationBuilder app, IHostingEnvironment env)
   {
       // 中间件配置...
       app.UseSignalR(routes =>
       {
           routes.MapHub<NotificationHub>("/notificationHub");
       });
   }
   

注意事项：

• 使用SqlDependency时，查询语句必须符合特定要求（比如不能用SELECT *，必须指定列名，不能用聚合函数等），否则通知不会触发。
• SqlDependency触发一次后会自动失效，所以需要在OnChange事件中重新注册监听。
• 确保数据库用户有足够的权限来使用Service Broker和查询通知。

内容的提问来源于stack exchange，提问作者Diana Cardenas