SqlDependency OnChange数据库变更时不触发的问题求助

针对你用SignalR结合SqlDependency实现数据库变更推送但OnChange事件不触发的问题，结合你的代码和常见坑点，整理以下排查步骤：

1. 严格遵循SqlDependency查询语法要求 #

SqlDependency对SQL语句有强制规范，你的查询缺少架构名（比如dbo），这是常见触发失败原因：
原查询：

SELECT [ID], [CardID], [ReaderID], [ScanAt] FROM [LiveCards]

修改为：

SELECT [ID], [CardID], [ReaderID], [ScanAt] FROM [dbo].[LiveCards]

额外注意：必须明确列名（不能用SELECT *）、不能用聚合函数/子查询/临时表，所有对象必须带架构名。

2. 检查SQL用户权限 #

确保连接数据库的用户拥有以下权限：

-- 替换为你的SQL用户名
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [YourSqlUserName];
GRANT VIEW DEFINITION TO [YourSqlUserName];
GRANT CONTROL ON SCHEMA::dbo TO [YourSqlUserName];

3. 修正SqlDependency注册时机 #

你在Session_Start里注册订阅，会导致每个用户会话重复创建订阅，可能引发冲突或失效。改为在Application_Start中初始化一次：
修改Global.asax代码：

protected void Application_Start()
{
    AreaRegistration.RegisterAllAreas();
    FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
    RouteConfig.RegisterRoutes(RouteTable.Routes);
    BundleConfig.RegisterBundles(BundleTable.Bundles);
    // 启动SqlDependency
    SqlDependency.Start(con);
    // 全局初始化一次订阅
    MemberCardComponent MCC = new MemberCardComponent();
    MCC.RegisterMemberCard();
}

// 移除Session_Start中的注册代码
protected void Session_Start(object sender, EventArgs e)
{
    // 此处不再初始化MemberCardComponent
}

4. 完善OnChange事件处理逻辑 #

当前代码未解绑旧事件，可能导致内存泄漏或重复触发问题，同时要处理非Change类型的通知（比如错误），确保重新订阅：
修改MemberCardComponent中的OnChange方法：

private void sqlDep_OnChange(object sender, SqlNotificationEventArgs e)
{
    // 先解绑旧事件
    SqlDependency sqlDep = sender as SqlDependency;
    if (sqlDep != null)
    {
        sqlDep.OnChange -= sqlDep_OnChange;
    }

    // 无论是否是变更通知，都重新注册订阅（避免丢失监听）
    RegisterMemberCard();

    // 仅当是变更时推送消息
    if (e.Type == SqlNotificationType.Change)
    {
        var memberCardHub = GlobalHost.ConnectionManager.GetHubContext<MemberCardHub>();
        memberCardHub.Clients.All.notify("cardscanned");
    }
}

5. 验证Service Broker状态与清理通知队列 #

即使你确认启用了Service Broker，也可以再次验证并清理可能积压的会话：

1. 执行SQL验证Broker状态：

SELECT name, is_broker_enabled 
FROM sys.databases 
WHERE name = '你的数据库名';

确保is_broker_enabled值为1。

2. 清理过期的通知会话：

USE 你的数据库名;
GO
DECLARE @conv_handle uniqueidentifier;
DECLARE conv_cursor CURSOR FOR
SELECT conversation_handle 
FROM sys.conversation_endpoints 
WHERE far_service = 'SQL/Notifications/QueryNotificationService';

OPEN conv_cursor;
FETCH NEXT FROM conv_cursor INTO @conv_handle;
WHILE @@FETCH_STATUS = 0
BEGIN
    END CONVERSATION @conv_handle WITH CLEANUP;
    FETCH NEXT FROM conv_cursor INTO @conv_handle;
END

CLOSE conv_cursor;
DEALLOCATE conv_cursor;
GO

6. 检查连接字符串配置 #

确保web.config中的连接字符串：

• 包含providerName="System.Data.SqlClient"（不能用其他提供者）
• 明确指定数据库名，避免使用AttachDbFilename方式
• 不要包含MultipleActiveResultSets=False（默认是True，不影响，但确保没有手动关闭）

内容的提问来源于stack exchange，提问作者Minh Hoànggg