Why SignalR change function hitting many many times?

Question

I try to develop an asp.net mvc application and also trying to use signalr. The problem is that i have two tables that control user notificitaions in project. I have a Notification table and also NotificationUser table which is many to many table of notification and user tables. I am trying achive that if a user create a notification to another user in system I try to show a pop-up that confirm user with a simple message like 'Hey!New notification received'. The problem is javascript change function of signalr hitting so many times.All step i used in signalR listed below

the stored procedure

    ALTER PROCEDURE [dbo].[GetNotifications]

    @userid int
    AS
    BEGIN
    select n.Ntf_Title,Ntf_Description,n.Ntf_Date from dbo.SysNotifications n INNER JOIN dbo.SysNotificationUser u on  n.Ntf_ID =u.NtU_NtfID where    NtU_UserID=@userid AND NtU_IsRead=0
    END

The Hub

 [HubName("signalRHub")]
 public class NtfHub : Hub
 {
    [HubMethodName("notifyChanges")]
    public static void NotifyChanges()
    {
        var context = GlobalHost.ConnectionManager.GetHubContext<NtfHub>();
        context.Clients.All.notifyChanges();
    }


}

The StartUp Class

public class Startup
{
    public void Configuration(IAppBuilder app)
    {
        app.MapSignalR();

    }
}

The Partial View

 [HttpGet]
    public ActionResult GetNtf()
    {

        //NtfRepo rp = new NtfRepo(this.HttpContext);
        string connectionString = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
        int userid =id;
        using (SqlConnection sqlcon = new SqlConnection(connectionString))
        {
            using (SqlCommand sqlcom = new SqlCommand("[GetNotifications]", sqlcon))
            {
                sqlcon.Open();
                sqlcom.CommandType = CommandType.StoredProcedure;
                sqlcom.Parameters.AddWithValue("@userid", userid);
                sqlcom.Notification = null;
                SqlDependency dependancy = new SqlDependency(sqlcom);
                dependancy.OnChange += dependancy_OnChange;
                var reader = sqlcom.ExecuteReader();
                var ntf= reader.Cast<IDataRecord>()
                   .Select(e => new PopulateNtfBar()
                   {
                       Title = e.GetString(0),
                       Description = e.GetString(1),
                       TimeDiff = FindDifferenceTime(e.GetDateTime(2))
                   }).ToList();
                return PartialView("~/Views/Shared/CheckNotification.cshtml", ntf);
            }
        }
    }

At Last,The Script

  $(function () {
        var notification = $.connection.signalRHub;

        // Create a function that the hub can call to broadcast messages.
        notification.client.notifyChanges = function () {
            getData();
            toastr.warning("Hey,You have Ntf");
        };

        // Start the connection.
        $.connection.hub.start().done(function () {
            getData();
        }).fail(function (e) {
        });
    });


    function getData() {
        var tbl = $("#header_notification_bar")
        $.ajax({
            url: '@Url.Action("GetNtf","Home")',
            contentType: 'application/html ; charset:utf-8',
            type: 'GET',
            dataType: 'html'
        }).success(function (result) {
            tbl.empty().append(result);

        }).error(function () {

        });


    }

notification.client.notifyChanges hitting so many times if a user create a notification.Where is the problem? Any idea? i cannot optimize it

EDIT 1 I am calling NtfHub.NotifyChanges in controller.

  void dependancy_OnChange(object sender, SqlNotificationEventArgs e)
    {
        if (e.Type == SqlNotificationType.Change)
        {
            NtfHub.NotifyChanges();
        }
    }

Answer 1

Although I think that SqlDependency is wrong approach for this feature, you could try to solve this specific problem this way:
Add parameter "subscribeToNotifications" to your controller action

public ActionResult GetNtf(bool subscribeToNotifications)

Create SqlDependency only if it is True.
Then subscribe to notifications only on hub started (this will prevent creation of multiple SqlDependencies for same user):

$(function () {
    var notification = $.connection.signalRHub;

    // Create a function that the hub can call to broadcast messages.
    notification.client.notifyChanges = function () {
        getData(false);
        toastr.warning("Hey,You have Ntf");
    };

    // Start the connection.
    $.connection.hub.start().done(function () {
        getData(true);
    }).fail(function (e) {
    });
});


function getData(subscribeToNotifications) {
    var tbl = $("#header_notification_bar")
    $.ajax({
        url: '@Url.Action("GetNtf","Home")' + '?subscribeToNotifications=' + subscribeToNotifications,
        contentType: 'application/html ; charset:utf-8',
        type: 'GET',
        dataType: 'html'
    }).success(function (result) {
        tbl.empty().append(result);

    }).error(function () {

    });
}

But be aware that every page refresh will still create new listener without managing subscriptions on server side.

Option 2 is to create single SqlDependency (on server app start) omitting userId parameter - anyway you are sending notification to all users no matter which one got the message.

Option 3 - the real solution is get rid of SqlDependency at all and send notification only to specific user (recipient of the message)

Answer 2

The reason is that you are not unsubscribing from the dependancy_OnChange event, a sqldependency trigger is a one shot execution, so you have to subscribe to the new one every time it fires, what you are not doing is unsubscribing from the previous event handler, so when you subscribe to the new one, you now have multiple handlers for the same trigger.

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