SqlDependency OnChange event firing multiple times

Question

I'm have a comment system on a project where you can view different pages and they have their own comments. I'm trying to use signalR with SqlDependency to automatically pull new comments on the page. My problem is that if multiple people have a connection open with SqlDependency the number of "onchange" events called from the sqlnotification start getting multiplied. Instead of the onchange even getting called once it will be called multiple times for each user. (Even if they are not viewing the same comments page) I've pretty much exhausted all searching here with the most common response being that I need to unsubscribe the event when it's called like this:

    private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
    {
        SqlDependency dependency = (SqlDependency)sender;
        dependency.OnChange -= new OnChangeEventHandler(dependency_OnChange);

        if (e.Info.ToString().ToLower().Trim() == "insert")
        {
            MyHub.Show();
        }
    }

This didn't seem to change anything for me so I'm lost on what the best way to prevent this would be.

GetData method:

    [WebMethod]
    public IEnumerable<DUpdates> GetData(string dnum, string date)
    {
        if (Common.IsValidInt32(dnum)) //just a function that check for valid int
        {
            using (var connection =
                new SqlConnection(ConfigurationManager.ConnectionStrings["SConnectionString"].ConnectionString))
            {

                connection.Open();

            using (SqlCommand command = new SqlCommand(@"SELECT [Comment] FROM dbo.DUpdates WHERE (DID = " + dnum + ") AND (Deleted = 0) AND CreateDate > @Date ORDER BY CreateDate DESC", connection))
            {
                command.Parameters.Add("@Date", SqlDbType.DateTime);
                command.Parameters["@Date"].Value = date;

                command.Notification = null;

                SqlDependency dependency = new SqlDependency(command);

                dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);


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

                using (var reader = command.ExecuteReader())
                {
                    return reader.Cast<IDataRecord>().Select(x => new DUpdates()
                    {
                        comment = x.GetString(0)
                    }).ToList();
                }
            }
        }
    }

JS stuff:

    $(function() {
        var job = $.connection.myHub;

        job.client.displayStatus = function () {
            getData();
        };

        $.connection.hub.start();
        getData();
    });

    function getData() {
        var params = (new URL(document.location)).searchParams;
        var id = params.get("id");
        var dt = $("#accessdate").val();

        $.ajax({
            url: 'dupdatesservice.asmx/GetData',
            data: JSON.stringify({dnum: id, date: dt}),
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            type: "POST",
            success: function (data) {
                if (data.d.length > 0) {
                    $("#testdiv").prepend(data.d[0].comment);
                }
            }
        });
    }

Can provide other code if needed.

Answer 1

The issue here is that I was creating a new SqlDependency for each user that was on a page (or same user with multiple windows). So if 2 windows were open for a page, then it would check for notifications twice and send the response twice if there was something new. Because of the ajax request, now all of those SqlDependencies were doubled so I'd get 4 responses the next time, then 8 and so on.

What I decided to do instead was to essentially change my pages to private chats using signalr and just ditched the SqlDependency stuff. So now if a user goes to one page, they are connected with anyone else on the page and anytime someone submits a "comment" it also gets sent to other people viewing the page.