**SignalR** SQLDependency Onchanged fired multiple times

Question

I am having a ASP.Net MVC Monitoring App with VS2013 and .Net Framework 4.5.1 which should automatically refresh the Real-time Chart everytime there are changes in database. Everything works fine when I have a single instance of the browser open, but when i open another tab or browser either on the same machine or on the different machine it fires the SQLDependency Event multiple times.

Here's my Repository.cs

  public class Repository
{
    private static string conString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();
    public List<GetTakeInCount> UpdateTRTakeInData()
    {
        try
        {
            var lst = new List<GetTakeInCount>();
            using (SqlConnection con = new SqlConnection(conString))
            {

                using (var cmd = new SqlCommand(@"SELECT [Date],[TotalPlan],[TakeInQty],[OrderQty],[DelayedQty] FROM [dbo].[ProductTakeInData] Where [Production] = 'TR'", con))
                {
                    cmd.Notification = null;

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

                    SqlDependency dependency = new SqlDependency(cmd);
                    dependency.OnChange -= new OnChangeEventHandler(dependency_OnChange);
                    dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);

                    var rdr = cmd.ExecuteReader();
                    while (rdr.Read())
                    {
                        GetTakeInCount rowData = new GetTakeInCount();
                        rowData.TakeInQuantity = Convert.ToDouble(rdr["TakeInQty"].ToString());
                        rowData.Date = rdr["Date"].ToString();
                        rowData.PlanQuantity = rdr["TotalPlan"].ToString();
                        rowData.AccPlanQty = Convert.ToDouble(rdr["TotalPlan"].ToString());
                        rowData.OrderQty = Convert.ToDouble(rdr["OrderQty"].ToString());
                        rowData.DelayedQty = Convert.ToDouble(rdr["DelayedQty"].ToString());
                        lst.Add(rowData);
                    }
                    rdr.Dispose();
                }
            }

            return lst;
        }
        catch (Exception e)
        {
            throw e;
        }
    }

    private void dependency_OnChange(object sender, SqlNotificationEventArgs e) //this will be called when any changes occur in db table. 
    {
        if (e.Type == SqlNotificationType.Change)
            {
            //Call SignalR  
            MyHub mh = new MyHub();
            mh.UpdateChart();
        }
        SqlDependency dependency = sender as SqlDependency;
        if (dependency != null) dependency.OnChange -= new OnChangeEventHandler(dependency_OnChange);
    }

}

Here's my Hub

[HubName("MyHub")]
    public class MyHub : Hub
    {

       public void UpdateChart()  
       {
            List<GetTakeInCount> data = new List<GetTakeInCount>();
            Repository rep = new Repository();
            data = rep.UpdateTRTakeInData();
            IHubContext context = GlobalHost.ConnectionManager.GetHubContext<MyHub>();
            context.Clients.All.updateChartData(data);
        }  

    }

Here's my js stuff:

 $(function () {
        // Declare a proxy to reference the hub.


        var chartConnection = $.connection.MyHub;
        $.connection.hub.logging = true;

        //debugger;
        // Create a function that the hub can call to broadcast messages.
        chartConnection.client.updateChartData = function () {
            updateChart();
            console.log('hub started')

        };
        // Start the connection.
        $.connection.hub.start();
        updateChart();

    });

        function updateChart()
        {                                         
    -- Update Chart Code ---
    }

Anyone can help?

Answer 1

Ryan, You can re-look into the event deregister & register part.

"SqlDependency dependency = new SqlDependency(cmd); dependency.OnChange -= new OnChangeEventHandler(dependency_OnChange); dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);"

I think it's better to have singleton object of SqlDependency and then do the deregistration & registration of that.

To do this You can create a singleton class that will give you an instance of SqlDependency and instead of doing "new SqlDependency(cmd)" you can call the that getInstance(cmd) method of singletonclass and do the deregister & Register on that.