CODEWITHSUNDEEP
c#sql-server
Usman
Usman

Reputation: 2577

SQL Dependency OnChange event calls multiple times on page refresh reason

Can any body tell me what is the reason behind SQL dependeny OnChange event calls multiple times after page refresh. What could be the possible reason behind this? Before page refresh it is called only one time per change in the database.

Upvotes: 2

Views: 2075

Answers (2)

Alican Uzun
Alican Uzun

Reputation: 349

I was having the same problem and @usman's answer helped me a lot. I change the logic of dependency_OnChange method a little bit.

        private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
        {
          if (dependency != null)
          {
              dependency.OnChange -= dependency_OnChange;
              dependency = null;
          }
          if (e.Type == SqlNotificationType.Change)
          {
              MessagesHub.SendMessages();
          }
        }

I set the dependency to null if it is not null. If we don't set it to null it fires, on every page refresh and if the page is opened multiple times or opened from multiple browsers. As @usman did define the dependency as internal static and set dependency to null in the onChange method. This made my day. Hope it helps another who are facing the same problem.

Upvotes: 3

Usman
Usman

Reputation: 2577

Problem: When I refresh page each time a new SQL Dependency variable was created and also a new Change_Event_Handler associated with that new dependency variable, and when SQL dependency is called then it has to unsubscribe from all of the existing change events which made multiple calls to my function.

Solution: Define these both variable as static in class:

    internal static SqlCommand command = null;
    internal static SqlDependency dependency = null;

Then use the function like this, and in application start first stop the Dependency and then start again and then do other stuff like this. Check if dependency is started already then don't create new dependency connection and similarly new ChangeEvent,

    using (EmailController.command = new SqlCommand(SQL.emailmessagesbyaccount_sql(), conn.getDbConnection()))
    {
        defaultemailid = emailid;
        EmailController.command.Parameters.Add(new SqlParameter("@emailaccountid", emailid));

        EmailController.command.Notification = null;

        if (EmailController.dependency == null)
        {
            EmailController.dependency = new SqlDependency(EmailController.command);
            EmailController.dependency.OnChange += new OnChangeEventHandler(emailMessages_OnChange);
        }
        var reader = EmailController.command.ExecuteReader();
    }

and finally you have to implement the onchange_event like this:

private void emailMessages_OnChange(object sender, SqlNotificationEventArgs e)
{
    if (e.Type == SqlNotificationType.Change)
    {
        //if not null then unsubscribe the calling event
        if (EmailController.dependency != null)
        {
            EmailController.dependency.OnChange -= emailMessages_OnChange;
        }
        //do my email updates
        NotificationHub.EmailUpdateRecords();


        // here again subscribe for the new event call re initialize the
        // exising dependecy variable the one which we defined as static

        SingletonDbConnect conn = SingletonDbConnect.getDbInstance();
        using (EmailController.command = new SqlCommand(SQL.emailmessagesbyaccount_sql(), conn.getDbConnection()))
        {
            EmailController.command.Parameters.Add(new SqlParameter("@emailaccountid", defaultemailid));
            EmailController.command.Notification = null;

            EmailController.dependency = new SqlDependency(EmailController.command);
            EmailController.dependency.OnChange += new OnChangeEventHandler(emailMessages_OnChange);

            var reader = EmailController.command.ExecuteReader();

        }

    }
}

Actually this was my code logic but hope you will get pretty much good idea from this implementation how to handle this kind of issue which made me stumbling my head for a week.

Upvotes: 10

Related Questions