SQL 依赖项未检测到更改.我错过了什么

Question

我试图使用 sqldependency 检查任何数据库更改，然后将一些东西发送到连接到 Signalr 的客户端。问题是，它似乎没有检测到变化。我觉得少了点什么。代码如下：

[HubName("DataHub")]
public class DataHub : Hub
{
    public void pushOnChange() 
    {
        string connection = "data source=fourthD;initial catalog=dash;integrated security=True;multipleactiveresultsets=True;application name=EntityFramework"";
        SqlDependency.Start(connection);
        SqlConnection connectionString = new SqlConnection(connection);
        connectionString.Open();
        using (SqlCommand command=new SqlCommand("SELECT * FROM dbo.insight", connectionString))
        {
            command.Notification = null;
            SqlDependency dependency = new SqlDependency(command);
            dependency.OnChange += new OnChangeEventHandler(OnDependencyChange);
            command.ExecuteReader();
        }
    }

    void OnDependencyChange(object sender, SqlNotificationEventArgs e)
    {
        Dictionary<string, string> data = new Dictionary<string, string> { };
        data.Add("Id","34");
        data.Add("DeviceId", "163-117")
        data.Add("DeviceUserName", "Someone");
        var result = new[] { data };
        Clients.All.addData(result);
    }
}

Answer 1

问题是 SqlDependency 需要重新订阅。因此，您应该像这样修改 OnDependencyChange 方法：

void OnDependencyChange(object sender, SqlNotificationEventArgs e)
{
    // Resubscription
    using (SqlCommand command=new SqlCommand("SELECT * FROM dbo.insight"
            , connectionString))
    {
        command.Notification = null;
        SqlDependency dependency = new SqlDependency(command);
        dependency.OnChange += new OnChangeEventHandler(OnDependencyChange);
        command.ExecuteReader();
    }
    Dictionary<string, string> data = new Dictionary<string, string> { };
    data.Add("Id","34");
    data.Add("DeviceId", "163-117")
    data.Add("DeviceUserName", "Someone");
    var result = new[] { data };
    Clients.All.addData(result);
}

希望这有帮助。