SqlDependency.OnChange dependency_OnChange没有开火

Question

我刚刚开始使用SignalR和安装配置，根据不同的文章和这里的多个问题。我已经跟随了每一步。我不知道为什么依赖OnChange没有启动？

[HubName("broadcastHub")]
public class BroadcastHub : Hub
{  
    [HubMethodName("sendNotifications")]
    public Task<object> SendNotifications()
    {
        DataTable dt = new DataTable();
        using (var connection = new SqlConnection(strConnectionString))
        {
            connection.Open();
            using (SqlCommand command = new SqlCommand(query, connection))
            {
                command.Notification = null;
                SqlDependency dependency = new SqlDependency(command);
                dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
                var reader = command.ExecuteReader();
                dt.Load(reader);
                connection.Close();

            }
        }
        IHubContext context = GlobalHost.ConnectionManager.GetHubContext<BroadcastHub>();
        var json = Newtonsoft.Json.JsonConvert.SerializeObject(dt);
        return (context.Clients.All.RecieveNotification(json));
    }
    private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
    {
        if (e.Type == SqlNotificationType.Change)
        {
            SendNotifications();
        }
    }
}

它第一次运行良好，我得到了预期的数据。但是，当表中有任何更改时，它不会触发dependency_OnChange

我还发现代理服务是通过以下查询启用的：

select is_broker_enabled from sys.databases where name='msdb' 
select is_broker_enabled from sys.databases where name='mydb'

两者都是enabled，值是1。

我在SendNotifications中使用的查询是：-

select Id,OXEName,OXEIP IP,ConnectionStatus Status, Case WHEN ConnectedOxeIP IS NULL OR ConnectedOxeIP = '' THEN OXEIP ELSE ConnectedOxeIP END as ConnectedOxeIP from PBXDetail

Java脚本

$(function () {
    var notifications = $.connection.broadcastHub;
    notifications.client.recieveNotification = function (response) {
    };
    $.connection.hub.start().done(function () {
        notifications.server.sendNotifications();
    }).fail(function (e) {
    });             
});

Startup.cs

[assembly: OwinStartup(typeof(myNameSpace.Startup))]
namespace myNameSpace
{
    public class Startup
    {
        public void Configuration(IAppBuilder app)
        {
            app.MapSignalR(new HubConfiguration() { EnableJSONP = true });
        }
    }
}

Global.asax

protected void Application_Start(object sender, EventArgs e)
{
   System.Data.SqlClient.SqlDependency.Start(strConnectionString);
}

protected void Application_End(object sender, EventArgs e)
{
   System.Data.SqlClient.SqlDependency.Stop(strConnectionString);
}

Answer 1

我已经想出了它，并把它作为一个答案，以便任何未来的读者谁将面临这类问题将能够解决它。

我调试了一段代码，发现我在dependency_OnChange上用dependency_OnChange获得了以下值的参数：

Info => Invalid
Type => Subscribe
Source => Statement

如果信息无效，这将使我知道我的查询有问题。然后，我改变了我的查询语法，如下所示，它运行良好。

select [Id],[OXEName],[OXEIP] as [IP],[ConnectionStatus] as [Status], Case WHEN [ConnectedOxeIP] IS NULL OR [ConnectedOxeIP] = '' THEN [OXEIP] ELSE [ConnectedOxeIP] END as [ConnectedOxeIP] from dbo.PBXDetail

下面是我找到的查询状态：

select * from table // did not work
select ID from table // did not work
select [ID] from table // did not work
select [ID] from dbo.table // Worked

完成此操作后，我发现在每一次页面刷新时，dependency_OnChange都会触发页面刷新次数。例如，如果页面刷新10次，它将触发10次。因此，我作了以下的修改：

[HubMethodName("sendNotifications")]
public Task<object> SendNotifications()
{
    DataTable dt = new DataTable();
    using (var connection = new SqlConnection(strConnectionString))
    {
        connection.Open();
        using (SqlCommand command = new SqlCommand(query, connection))
        {
            command.Notification = null;
            if (ServiceController.dependency == null)
            {
                ServiceController.dependency = new SqlDependency(command);
                ServiceController.dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
            }
            var reader = command.ExecuteReader();
            dt.Load(reader);
            connection.Close();
        }
    }
    IHubContext context = GlobalHost.ConnectionManager.GetHubContext<BroadcastHub>();
    var json = Newtonsoft.Json.JsonConvert.SerializeObject(dt);
    return (context.Clients.All.RecieveNotification(json));
}

private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
    if (e.Type == SqlNotificationType.Change)
    {
        if (ServiceController.dependency != null)
        {
            ServiceController.dependency.OnChange -= dependency_OnChange;
            ServiceController.dependency = null;
        }
        SendNotifications();
    }
}

ServiceController

 public static class ServiceController
 {
    internal static SqlCommand command = null;
    internal static SqlDependency dependency = null;
    internal static bool isCachingEnabled = false;
 }

Global.asax

protected void Application_Start(object sender, EventArgs e)
{
    if (!ServiceController.isCachingEnabled)
    {
        SqlDependency.Stop(strConnectionString);
        SqlDependency.Start(strConnectionString);
    }
}