SqlDependency multiple instances of Start() with SignalR

Question

I have an MVC 4 site that is using SqlDependency to detect changes to a database and then notify the subscribed client(s) of the change via SignalR. All of the mechanisms are in place, and the application is aware of the change occurring, but here's where it gets interesting...I see a growing number of client notifications based on the number of either browser refreshes occurred between Application_Start() and Application_End(), or the number of connected clients.

Perhaps my understanding is off on these technologies, but I thought that the SignalR turned into a Singleton which causes all traffic to occur over a single "pipe" between the client and server, regardless of the number of client(s) connected.

Of course, that doesn't explain why a refresh seems to instantiate an entirely new SqlDependency.

I saw this answer that showed an idea of shutting down the SqlDependency (simulating the Application_End()) but all that did was add execution time to the page and didn't resolve the issue.

I'm stumped and would really appreciate some suggestions to get this working as expected.

Here's the code I'm working with...

In my Global.asax.cs file:

using System.Configuration;
using System.Data.SqlClient;
using System.Web.Mvc;
using System.Web.Optimization;
using System.Web.Routing;

namespace SmartAppV1
{
    public class MvcApplication : System.Web.HttpApplication
    {
        protected void Application_Start()
        {
            AreaRegistration.RegisterAllAreas();
            FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
            RouteConfig.RegisterRoutes(RouteTable.Routes);
            BundleConfig.RegisterBundles(BundleTable.Bundles);

            // SignalR Wireup
            SqlDependency.Start(ConfigurationManager.ConnectionStrings["SmartAppSignalR"].ConnectionString);
        }

        protected void Application_End()
        {
            // Shut down SignalR Dependencies
            SqlDependency.Stop(ConfigurationManager.ConnectionStrings["SmartAppSignalR"].ConnectionString);
        }
    }
}

My SignalR Hub:

using System.Collections.Generic;
using System.Configuration;
using Microsoft.AspNet.SignalR;
using Microsoft.AspNet.SignalR.Hubs;
using SmartAppData.Entities;
using SmartAppV1.Models;

namespace SmartAppV1
{
    [HubName("smartAppHub")]
    public class SmartAppHub : Hub
    {
        private readonly string _connection = ConfigurationManager.ConnectionStrings["SmartAppSignalR"].ConnectionString;

        public void MonitorGrid4DataChanges()
        {
            var setGrid4 = new SmartAppSignalR
            {
                ConnectionString = _connection,
                Query =
                    @"SELECT [ID], [OrdHeaderId], [LoadId], [NewStatus] FROM [dbo].[CTLoadStatusChangeLog] WHERE [NewStatus] = 'Delivered' ORDER BY [ID] DESC"
            };
            setGrid4.DispatchBoardStatusChange();
        }
    }
}

My SignalR class:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using Microsoft.AspNet.SignalR;
using SmartAppData;
using SmartAppData.Entities;
using SmartAppData.Services;

namespace SmartAppV1.Models
{
    public class SmartAppSignalR
    {
        public string ConnectionString { get; set; }
        public string Query { get; set; }

        public IEnumerable<DeliveredGridItem> ReadGrid4Data()
        {
            var service = new LoadService();
            var result = service.GetLoadsByBookedByIdByTmsStatus(110, LoadTmsStatus.Delivered.ToString()).ToList();
            var deliveredList = new List<DeliveredGridItem>();
            foreach (var obj in result)
            {
                var deliveredItem = new DeliveredGridItem(obj.LoadId) { LoadTmsStatus = obj.DataValue_LoadTmsStatus };
                deliveredList.Add(deliveredItem);
            }

            return deliveredList;
        }

        public void DispatchBoardStatusChange()
        {
            using (var conn = new SqlConnection(ConnectionString))
            {
                using (var cmd = new SqlCommand(Query, conn))
                {
                    cmd.Notification = null;
                    var dependency = new SqlDependency(cmd);
                    // make sure the OnChange doesn't exist
                    // trying to remove redundant calls
                    dependency.OnChange -= dispatchBoard_OnChange;
                    dependency.OnChange += dispatchBoard_OnChange;
                    if (conn.State == ConnectionState.Closed)
                        conn.Open();
                    var reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                    }
                }
            }
        }

        private void dispatchBoard_OnChange(object sender, SqlNotificationEventArgs e)
        {
            if (e.Type != SqlNotificationType.Change) return;

            // dump the original OnChange event handler...we're going to re-register it
            var sqlDependency = (SqlDependency) sender;
            sqlDependency.OnChange -= dispatchBoard_OnChange;

            var retVal = new StatusChangedObject();

            using (var conn = new SqlConnection(ConnectionString))
            {
                using (var cmd = new SqlCommand(Query, conn))
                {
                    if (conn.State == ConnectionState.Closed)
                        conn.Open();
                    var reader = cmd.ExecuteReader();

                    if (reader.Read())
                    {
                        retVal.Id = Convert.ToInt32(reader["ID"]);
                        retVal.OrdHeaderId = Convert.ToInt32(reader["OrdHeaderId"]);
                        retVal.LoadId = Convert.ToInt32(reader["LoadId"]);
                        retVal.NewStatus = reader["NewStatus"].ToString();

                        var clients = GlobalHost.ConnectionManager.GetHubContext<SmartAppHub>().Clients;
                        clients.All.gridUpdate(retVal);
                    }
                }
            }

            // Re-register the SqlDependency
            DispatchBoardStatusChange();
        }
    }

    public class StatusChangedObject
    {
        public int Id { get; set; }
        public int OrdHeaderId { get; set; }
        public int LoadId { get; set; }
        public string NewStatus { get; set; }
    }
}

And, finally my .js code for the SignalR: Note: I'm using Telerik grids so I have to have the SignalR wireup outside of my $(document).ready() which is where I've seen every other tutorial/example of wiring up SignalR.

// SignalR plumbing
var hub, hubStart;
hub = $.connection.smartAppHub;
hubStart = $.connection.hub.start().done(function () {
    if ($.connection.hub.state === $.signalR.connectionState.connected) {
        console.log('SignalR is connected.');
        console.log('Call the server method to monitor changes to Grid4');
        hub.server.monitorGrid4DataChanges();
    };
});

hub.client.grid4Read = function () {
    console.log('Called grid4Read()');
};

hub.client.iDidSomething = function (response) {
    console.log('I was told to do something\r\n' + response);
};

hub.client.gridUpdate = function (response) {
    console.log("Entered hub.client.gridUpdate");
    // Will show alerts when something moves.
    // Plan on adjusting this via the 'Settings' menu.
    var showNotification = true;

    // Go get the order/load from the database
    var ordHeaderId = response['OrdHeaderId'];
    var loadId = response['LoadId'];
    var newStatus = response['NewStatus'];

    if (showNotification) {
        //setToastrOptions();
        //toastr["info"]("Grid update to loadId: " + loadId);
    }

    console.log('Client-side fromSqlDependency:\r\nOrdHeaderId: ' + ordHeaderId + '\r\nLoadId: ' + loadId + '\r\nNewStatus: ' + newStatus);
};

Answer 1

Ok, so I think there are a couple of things going on here, you are adding and removing the events for handling the sqldependency change too often. You should be using guard logic to see if the event handler even exists, removing a handler that does not exist has odd repercussions sometimes. As for the multiple starts etc, everytime you reload, or navigate away from a client page it disconnects, and reconnects if loading. a signalr hub is not a singleton instance unless you code it that way, its not done that way by default. Here is a simple version of what you want to do.

try
    {
        using (
            var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
        {
            connection.Open();
            using (SqlCommand command = new SqlCommand(@"SELECT [Id]
                                                                    ,[FName]
                                                                    ,[LName]
                                                                    ,[DOB]
                                                                    ,[Notes]
                                                                    ,[PendingReview] 
                                                   FROM [dbo].[Users]",
                connection))
            {
                // Make sure the command object does not already have
                // a notification object associated with it.
                command.Notification = null;

                SqlDependency dependency = new SqlDependency(command);

                dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);

                if (connection.State == ConnectionState.Closed)
                    connection.Open();

                command.ExecuteReader();
            }
        }
    }
    catch (Exception e)
    {
        throw;
    }
}

private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{   
    SqlDependency dependency = sender as SqlDependency;
    if (dependency != null) dependency.OnChange -= dependency_OnChange;
    //Recall your SQLDependency setup method here.
    SetupDependency();
    JobHub.Show();
}

if you want to create a singleton type of hub you need to set it up something like this where you have a static hub and a hub tracker class, this is in the tracker class. Check the tutorial link for more details:

 private readonly static Lazy<StockTicker> _instance = new Lazy<StockTicker>(() => new StockTicker(GlobalHost.ConnectionManager.GetHubContext<StockTickerHub>().Clients));

 private readonly ConcurrentDictionary<string, Stock> _stocks = new ConcurrentDictionary<string, Stock>();

Have a look at this tutorial for using a hub as a singleton: http://www.asp.net/signalr/overview/getting-started/tutorial-server-broadcast-with-signalr