SqlDependency OnChange Event Fires many times for every single event on Database

Question

I'm developing a notification system using SqlDependency and signalR, the problem I can't deal with is when I change the attribute value "IsOnline" in DB to True or False based on attendee status, OnChange event fires many times, first time a new user log in I get two notifications then the second time I get more like 4 then more then more. The number of notifications increase every time a new one sign in or sign out. I'm sure the problem in SqlDependency not in SignalR, I'm going to share with you part of my code.

Thanks in advance.

  [System.Web.Services.WebMethod]

    public static IEnumerable<AttendeeList> GetAllUsers()
    {
        var AttendeeList = new List<AttendeeList>();

        try
        {
            using (var connection = new SqlConnection(_connString))
            {
                connection.Open();
                string str = "";
                str += "SELECT [AttendeeID], ";
                str += "       [IsAllowToUploadDocuments],";
                str += "       [IsOnline], ";
                str += "       [AttendeeTypeName],";
                str += "       [UserName] ";
                str += "       FROM [dbo].[Meeting_Attendees]   ";
                str += "       INNER JOIN [dbo].[aspnet_Users]  ON [aspnet_Users].[UserId] = [Meeting_Attendees].[AttendeeID] ";
                str += "       INNER JOIN   [dbo].[AttendeeType] ON [dbo].[AttendeeType].[AttendeeTypeID] = [dbo].[Meeting_Attendees].[AttendeeTypeID] ";
                str += "       WHERE [MeetingID]=@MeetingID ORDER BY [IsOnline] DESC";

                using (var command = new SqlCommand(@str, connection))
                {
                    SqlParameter prm = new SqlParameter("@MeetingID", SqlDbType.Int);
                    prm.Direction = ParameterDirection.Input;
                    prm.DbType = DbType.Int32;
                    prm.Value = Convert.ToInt32(Properties.Settings.Default.MeetingID);
                    command.Parameters.Add(prm);
                    command.Notification = null;

                    var dependency = new SqlDependency(command);
                    dependency.OnChange += new OnChangeEventHandler(dependencyUsers_OnChange);

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

                    var reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        AttendeeList.Add(item: new AttendeeList { UserName = (string)reader["UserName"], UserType = (string)reader["AttendeeTypeName"], IsOnline = (bool)reader["IsOnline"], IsAllowToUploadDocuments = (bool)reader["IsAllowToUploadDocuments"], IsCurrentUser = true ? (Guid)reader["AttendeeID"] == new Guid(Properties.Settings.Default.UserID.ToString()) : false });
                    }
                }
            }
        }
        catch { }
        return AttendeeList;
    }

    private static void dependencyUsers_OnChange(object sender, SqlNotificationEventArgs e)
    {
        if (e.Type == SqlNotificationType.Change && e.Info == SqlNotificationInfo.Update)
        {
            //Call SignalR  
            MessagesHub.UpdateUsers();
        }
    }

Answer 1

To make sure event handler are registered once, do the "-=" before the "+=":

oDependency.OnChange -= new OnChangeEventHandler(DBUpdateNotificationReeived);
oDependency.OnChange += new OnChangeEventHandler(DBUpdateNotificationReeived);

Check to see if ur SQL table doesn't have triggers (aside the sqltabledependency one) that "update" the record being updated.

Answer 2

I had also the same problem with multiple calls on OnChange event in my project, but I fixed it with help counter variable. Following this example, in my case, the dependencyUsers_OnChange function from example was firing twice.

I initialized the counter variable as global. After "scanning" the state of your temporary data before any change, I also set the value of counter to 0.

Following your example, after this step, the modification was made at dependencyUsers_OnChange in if statement:

    private static void dependencyUsers_OnChange(object sender, SqlNotificationEventArgs e)
    {
            if (e.Type == SqlNotificationType.Change && e.Info == SqlNotificationInfo.Update && counter == 0)
            {
                //Call SignalR  
                MessagesHub.UpdateUsers();
                counter++; //The update is done once
            }
            else 
            {
                counter = 0; //if the update is needed in the same iteration, please don't update and set the counter to 0
            }
     }

In your case, the solution will be something like:

int counter = 0; //initialization of help counter
[System.Web.Services.WebMethod]

    public static IEnumerable<AttendeeList> GetAllUsers()
    {
        var AttendeeList = new List<AttendeeList>();

        try
        {
            using (var connection = new SqlConnection(_connString))
            {
                connection.Open();
                string str = "";
                str += "SELECT [AttendeeID], ";
                str += "       [IsAllowToUploadDocuments],";
                str += "       [IsOnline], ";
                str += "       [AttendeeTypeName],";
                str += "       [UserName] ";
                str += "       FROM [dbo].[Meeting_Attendees]   ";
                str += "       INNER JOIN [dbo].[aspnet_Users]  ON [aspnet_Users].[UserId] = [Meeting_Attendees].[AttendeeID] ";
                str += "       INNER JOIN   [dbo].[AttendeeType] ON [dbo].[AttendeeType].[AttendeeTypeID] = [dbo].[Meeting_Attendees].[AttendeeTypeID] ";
                str += "       WHERE [MeetingID]=@MeetingID ORDER BY [IsOnline] DESC";

                using (var command = new SqlCommand(@str, connection))
                {
                    SqlParameter prm = new SqlParameter("@MeetingID", SqlDbType.Int);
                    prm.Direction = ParameterDirection.Input;
                    prm.DbType = DbType.Int32;
                    prm.Value = Convert.ToInt32(Properties.Settings.Default.MeetingID);
                    command.Parameters.Add(prm);
                    command.Notification = null;

                    var dependency = new SqlDependency(command);
                    counter = 0; //Whenewer the web method is called, set te counter to 0
                    dependency.OnChange += new OnChangeEventHandler(dependencyUsers_OnChange);

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

                    var reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        AttendeeList.Add(item: new AttendeeList { UserName = (string)reader["UserName"], UserType = (string)reader["AttendeeTypeName"], IsOnline = (bool)reader["IsOnline"], IsAllowToUploadDocuments = (bool)reader["IsAllowToUploadDocuments"], IsCurrentUser = true ? (Guid)reader["AttendeeID"] == new Guid(Properties.Settings.Default.UserID.ToString()) : false });
                    }
                }
            }
        }
        catch { }
        return AttendeeList;
    }

    private static void dependencyUsers_OnChange(object sender, SqlNotificationEventArgs e)
    {
            if (e.Type == SqlNotificationType.Change && e.Info == SqlNotificationInfo.Update && counter == 0)
            {
                //Call SignalR  
                MessagesHub.UpdateUsers();
                counter++; //The update is done once
            }
            else 
            {
                counter = 0; //if the update is needed in the same iteration, please don't update and set the counter to 0
            }
     }

I hope this idea will be helpfull for someone, I solved the problem in my project with this solution.

Answer 3

I fall into the same issue while using SignalR and SQL Dependency

The line was getting executed more than once. The event should be subscribed only once. oDependency.OnChange += new OnChangeEventHandler(DBUpdateNotificationReeived);

Answer 4

Query Notifications fire when the monitored result set may had changed, see Understanding When Query Notifications Occur. As a general rule, you may get more notifications than actual data changes:

Notice that SQL Server may produce a query notification in response to events that do not change the data, or in response to a change that does not actually affect the results of the query. For example, when an UPDATE statement changes one of the rows returned by the query, the notification may fire even if the update to the row did not change the columns in the query results.. Query notifications are designed to support the overall goal of improving performance for applications that cache data. When the server is heavily loaded, SQL Server may produce a query notification message for the subscription rather than performing the work of determining whether the results of the query have changed.

Whether that causes problems in your case is impossible to tell from your post, specifically is not clear how you handle the updates so that it results in 4 more notifications. Getting 4 notifications implies you posted 4 queries to be notified, so is likely you have a problem in your code as well and you oversubscribe.

Read Using SQL Trace to Troubleshoot Query Notifications and try to troubleshoot what's going on, where are the notifications created and invalidated.