Asp.Net Core 3.1 Razor Pages SignalR中SqlDependency OnChange多次触发问题

问题根源 #

• 每次调用GetAllThisNewCaseInternalComments方法都会生成一个全新的SqlDependency监听，N个客户端发起查询就会注册N个独立监听，数据表变更时所有监听同时触发，导致重复广播通知。
• 前端每次打开Popup执行OpenInternalCommentsPipe时，都会重复绑定refreshInternalComments事件回调，即使只收到一次通知，也会触发多次loadData调用。
• SqlDependency.Start不需要每次查询都执行，全局启动一次即可，重复调用无意义还会增加额外开销。
• 当前SQL语句直接拼接newCaseId存在SQL注入风险，需要优化为参数化查询。

解决方案 #

1. 修改服务注册 #

将Repository注册为全局单例，确保全局只维护一套监听逻辑：

services.AddSingleton<INewCaseInternalCommentsRepository, NewCaseInternalCommentsRepository>();

2. 全局启动SqlDependency #

在Program.cs/Startup.cs的应用启动逻辑中添加SqlDependency启动代码，移除Repository中每次查询的启动调用：

// 应用启动时执行一次即可
var connectionString = builder.Configuration.GetConnectionString("SigRConnection");
SqlDependency.Start(connectionString);

3. 优化Repository代码，添加监听去重逻辑 #

public class NewCaseInternalCommentsRepository : INewCaseInternalCommentsRepository
{
    private readonly IHubContext<MySigServer> _context;
    private readonly string _connectionString;
    // 存储已注册监听的案件ID，避免重复注册
    private readonly HashSet<string> _registeredCaseIds = new HashSet<string>();
    private readonly object _lockObj = new object();

    public NewCaseInternalCommentsRepository(IConfiguration configuration,
                                IHubContext<MySigServer> context)
    {
        _connectionString = configuration.GetConnectionString("SigRConnection");
        _context = context;
    }

    public List<NewCaseInternalComments> GetAllThisNewCaseInternalComments(string newCaseId)
    {
        var comments = new List<NewCaseInternalComments>();

        using (SqlConnection conn = new SqlConnection(_connectionString))
        {
            conn.Open();
            // 修改为参数化查询，避免SQL注入
            string commandText = "SELECT id, NewCaseId, TimeStamp, UserId, UserName, UserComment FROM dbo.NewCaseInternalComments WHERE NewCaseId = @NewCaseId";
            SqlCommand cmd = new SqlCommand(commandText, conn);
            cmd.Parameters.AddWithValue("@NewCaseId", newCaseId);

            // 加锁防止并发重复注册
            lock (_lockObj)
            {
                if (!_registeredCaseIds.Contains(newCaseId))
                {
                    SqlDependency dependency = new SqlDependency(cmd);
                    dependency.OnChange += (sender, e) => DbChangeNotification(sender, e, newCaseId);
                    _registeredCaseIds.Add(newCaseId);
                }
            }

            var reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                var newCaseInternalComments = new NewCaseInternalComments
                {
                    id = Convert.ToInt32(reader["id"]),
                    TimeStamp = DateTime.Parse(reader["TimeStamp"].ToString()),
                    UserId = reader["UserId"].ToString(),
                    UserName = reader["UserName"].ToString(),
                    UserComment = reader["UserComment"].ToString()
                };
                comments.Add(newCaseInternalComments);
            }
        }
        return comments;
    }


    private void DbChangeNotification(object sender, SqlNotificationEventArgs e, string newCaseId)
    {
        // 只处理数据变更的有效通知，忽略其他无关通知
        if (e.Type == SqlNotificationType.Change)
        {
            _context.Clients.All.SendAsync("refreshInternalComments");
        }

        // SqlDependency触发一次后自动失效，需要重新注册
        lock (_lockObj)
        {
            _registeredCaseIds.Remove(newCaseId);
            if(sender is SqlDependency dependency)
            {
                dependency.OnChange -= (s, ev) => DbChangeNotification(s, ev, newCaseId);
            }
        }
    }
}

同时修改Repository接口，将newCaseId改为方法参数避免并发赋值冲突：

public interface INewCaseInternalCommentsRepository
{
    List<NewCaseInternalComments> GetAllThisNewCaseInternalComments(string newCaseId);
}

对应修改页面控制器代码：

public IActionResult OnPostRetreiveInternalComments([FromBody] NewCaseInternalComments obj)
{
    return new JsonResult(_repository.GetAllThisNewCaseInternalComments(obj.NewCaseId.ToString()));
}

4. 优化前端代码，避免重复绑定事件 #

// 全局变量标记是否已经绑定过监听
let isRefreshEventBinded = false;
function OpenInternalCommentsPipe(ncID) {
    if (connection.connectionState != "Connected") {
        connection.start();
    }
    // 只绑定一次监听
    if (!isRefreshEventBinded) {
        connection.on("refreshInternalComments", function () {
            loadData();
        });
        isRefreshEventBinded = true;
    }
    loadData();
    function loadData() {
        console.log("Hello! Something was changed!!!!");
        console.log(ncID);
        $.ajax({
            url: '/Identity/Account/Manage/ManagePPS?handler=RetreiveInternalComments',
            type: "POST",
            beforeSend: function (xhr) {
                xhr.setRequestHeader("XSRF-TOKEN",
                    $('input:hidden[name="__RequestVerificationToken"]').val());
            },
            data: JSON.stringify({
                NewCaseId: ncID
            }),
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            success: function (data, response) {
                PaintInternalCommentsFromTunnel(data);                
            },
            error: (jqXHR, error) => {
                console.log("Something went wrong!");
                console.log(jqXHR)
                console.log(error)
            }
        });
    }
}

内容的提问来源于stack exchange，提问作者Kyle Gortner