usman usman - 9 days ago 5
C# Question

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.

Answer

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.