SqlDependency.OnChange dependency_OnChange did not fire

Question

I have just started usingSignalR and setup configurations according to different articles and multiple questions here at SO. I have followed every step. I am unable to figure out why dependency OnChange is not firing ?

[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();
        }
    }
}

It works fine for the first time and I get expected data. But when any change is made in Table it does not fire dependency_OnChange

I have also identified that Broker Service is enabled by using the following queries:-

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

Both are enabled and value is 1.

Query which I am using in SendNotifications is:-

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

Java Script

$(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

I have figured it out and posting it as an answer so that any future reader who will be facing this type of issue would be able to figure it out.

I debugged piece of code and found that I was getting following Parameters with Values in SqlNotificationEventArgs at dependency_OnChange and those were:

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

If info is invalid this lead me to know that there was a problem with my query. Then I changed my query syntax like following and it worked fine.

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

Following are the query statuses which I found:

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

After doing this I have found that at every page refresh dependency_OnChange was firing as many times the page was refresh. For instance if page is refreshed 10 times it will fire 10 times. So I have made the following changes:

[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);
    }
}