SqlDependency OnChange Event Steals many times for each event in the database

Question

SqlDependency OnChange Event Steals many times for each event in the database

I am developing a notification system using SqlDependency and signalR, the problem that I cannot deal with is when I change the IsOnline attribute value in the DB to True or False based on the member status, the OnChange event fires many times, the first time I register new user, I get two notifications and the second time I get more than 4, then more than more. The number of notifications increases each time there is a new entry or exit. I'm pretty sure the problem with SqlDependency is not SignalR, I'm going to share some of my code with you.

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

+3

c # asp.net webforms signalr sqldependency

AQADDOUMI Jul 27. 15 at 9:03

source to share

3 answers

Remus Rusanu · Answer 1 · 2015-07-27T09:32:04+0000

Query notifications light up when a modified result set can be changed, see Understanding When Query Notifications Occur . Typically, you may receive more notifications than actual data changes:

Note that SQL Server may issue 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 a query, a notification might be triggered even if the row update did not change the columns in the query results. Request notifications are intended to support the overall goal of improving performance for applications that cache data. When the server is under heavy load, SQL Server may issue a subscription request notification message rather than doing the work of determining if the query results have changed.

Due to the issues in your case, it is impossible to tell from your post, in particular it is unclear how you handle the updates so that four more notifications will appear as a result. Receiving 4 notifications means you have sent 4 requests for notification, so you probably have a problem in your code as well, and you are rewriting.

Read Using SQL Trace to Troubleshoot Query Notifications and try to troubleshoot what is happening, where notifications are generated and discarded.

Ananth · Answer 2 · 2016-06-08T04:15:08+0000

I am running into the same problem when using SignalR and SQL Dependency

The line was executed more than once. The event must be signed only once. oDependency.OnChange + = new OnChangeEventHandler (DBUpdateNotificationReived);

arman1991 · Answer 3 · 2017-06-22T11:43:48+0000

I had the same problem with multiple calls on an event OnChange

in my project, but I fixed it with a help counter variable. Following this example, in my case the function dependencyUsers_OnChange

from the example included twice .

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

Following your example, after this step, the modification was made to dependencyUsers_OnChange

in if

:

    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 look something like this:

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
            }
     }

Hope this idea is helpful for someone, I solved the problem in my project with this solution.

SqlDependency OnChange Event Steals many times for each event in the database

More articles: