SqlDependency OnChange event not firing for SignalR

I know there are multiple questions on SO with a near identical subject, but unfortunately, after following countless guides and reading several answers, I'm at a loss to answer why this is occurring as a new user to SignalR / SqlDependencies.

I have an ASP.Net WebForms app that uses SignalR to push a realtime figure to the page. The code executes on initial load, and fires the event, but after that, I cannot get the event to fire when the dependency detects a change using the OnChange event.

I can see the Queue and SPs are created on the server just fine, but I can't ever see the queue receives any notifications when data is either added/removed or updated in the table. I think it may be something to do with the OnChange resubscription, but I'm not entirely sure.

What could be causing the event to not fire after initial load or the notification not be received from SQL?

I've posted all code below:

Hub Code

Namespace SignalR.Models
    <HubName("notificationHub")>
    Public Class NotificationHub
        Inherits Hub

        Private notifCount As Integer

        <HubMethodName("sendNotifications")>
        Public Sub SendNotifications()
            Using db As SqlConnection = New SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings("aspCreate_fetch2").ConnectionString)
                Dim query As String = " SELECT IIF(COUNT(l.[id]) > 99, 99, COUNT(l.[id]))
                                      FROM pla.[lv_test] as l
                                     WHERE 1=1
                                          AND l.[lv_int_id] = 419"
                Using sp As SqlCommand = New SqlCommand(query, db)
                    Dim dependency As SqlDependency = New SqlDependency(sp)
                    AddHandler dependency.OnChange, New OnChangeEventHandler(AddressOf dependency_OnChange)

                    sp.Notification = Nothing
                    Dim dt As DataTable = New DataTable()

                    db.Open()

                    If db.State = ConnectionState.Closed Then db.Open()
                    Dim reader = sp.ExecuteReader()
                    dt.Load(reader)

                    If dt.Rows.Count > 0 Then
                        notifCount = Int32.Parse(dt.Rows(0)(0).ToString())
                    End If

                    Dim context = GlobalHost.ConnectionManager.GetHubContext(Of NotificationHub)()
                    context.Clients.All.ReceiveNotification(notifCount)
                End Using
            End Using
        End Sub
        Private Sub dependency_OnChange(sender As Object, e As SqlNotificationEventArgs)
            If e.Type = SqlNotificationType.Change Then
                SendNotifications()
            End If
        End Sub
    End Class
End Namespace

Global ASAX

    Sub Application_Start(sender As Object, e As EventArgs)
        Dim sConn = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("redacted1").ConnectionString

        ' Fires when the application is started
        RouteConfig.RegisterRoutes(RouteTable.Routes)
        BundleConfig.RegisterBundles(BundleTable.Bundles)
        SqlDependency.[Stop](sConn)
        SqlDependency.Start(sConn)
    End Sub

    Private Sub Application_End(ByVal sender As Object, ByVal e As EventArgs)
        Dim sConn = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("redacted1").ConnectionString
        SqlDependency.[Stop](sConn)
    End Sub

JavaScript

$(function () {
    var nf = $.connection.notificationHub;

    nf.client.receiveNotification = function (notifCount) {
        console.log("connection started");
        $("#notif-badge").text(notifCount);
    }

    $.connection.hub.start().done(function () {
        nf.server.sendNotifications();
    }).fail(function (e) {
        alert(e);
    });
});

I am no VB or Javascript expert but I believe that your subscription to OnChange is removed immediately after exiting SendNotifications().

In your code, you have the following dependencies:

SqlDependency -> SqlCommand -> SqlConnection

and you're getting attached to SqlDependency object. However, because your SqlConnection is disposed at the end of the method, your subscription is just gone.

Declare your SqlConnection as a private property and keep the connection open. Besides, move the event subscription to a separate initialization method or the constructor to do it only once.

EDIT :

Here is more or less what I have in mind (in C# , sorry ^^ ):

public class DemoSqlSubscriber : Hub
{  
    readonly string connectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("aspCreate_fetch2").ConnectionString;  
    private SqlDependency dependency;
        
    public void StartListening()  
    {  
        SqlDependency.Start(connectionString);  
        SqlConnection connection = new SqlConnection(connectionString);  
        connection.Open();  

        SqlCommand command=new SqlCommand();  
        command.CommandText= "SELECT [notification_count] FROM pla.[notif_count]";  
        command.Connection = connection;  
        command.CommandType = CommandType.Text;  

        dependency = new SqlDependency(command);  
        dependency.OnChange += new OnChangeEventHandler(OnCountChanged);  
    }  

    private void OnCountChanged(object s,SqlNotificationEventArgs e)  
    {  
        if(e.Type == SqlNotificationType.Change)
        {
            // Publish 
            IHubContext<NotificationHub> context = GlobalHost.ConnectionManager.GetHubContext<NotificationHub>();
            context.Clients.All.ReceiveNotification(notifCount);
        }
    }  

    public void StopListening()  
    {  
        SqlDependency.Stop(connectionString);  
    }  
} 

Could you try to structure your Hub accordingly in VB. NET and let us know ?