Click here to Skip to main content
14,660,962 members
Rate this:
Please Sign up or sign in to vote.
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.


[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();
        }
    }
Posted
Updated 11-Aug-18 3:20am

Rate this:
Please Sign up or sign in to vote.

Solution 2

I know the question is old, but I see it's not answered, just referenced to a working example.

You're creating new SqlDependency objects on each call of the GetAllUsers method (which I presume is not called just once) and not stopping the old-ones. This results in multiple SqlDependency objects watching for identical change in the database, and each time you call the GetAllUsers method you add another one and that multiplies into oblivion.
A more correct way of doing this would be having a separate method for initializing the SqlDependency object(s) and calling it just once.

Hope I've helped.
Cheers.
   
Rate this:
Please Sign up or sign in to vote.

Solution 1

First of all the notification may occur even if there is no actual change in the data. This may happen for example if the server is under heavy load or even problems with the notification may trigger the event. You can try using HasChanges[^] if it shows the situation differently.

One thing that may possibly enhance the accuracy is removing the parameter and using a literal instead.

What comes to the increase of notification each time, it sounds like you're creating new subscriptions each time without ending the old ones. Perhaps each call on the web method creates a new listener. Howebver, from the code I can't find calls to Start or Stop methods... For example, have a look at the example in Detecting Changes with SqlDependency[^].
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100