how to remove event handler?

[babyekc]

hi all,

i got this problem, where i can't remove the event handler that i subscribed earlier, hence it causes duplicate event called.

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

this is where i subscribe my event. when there's changes from db (based on the sql i subscribed), it will trigger the dependencyScheduledJobs_onChanged event. it works perfectly fine on this stage.

public void dependencyScheduledJobs()
        {
            try
            { 
                using (var connection = new SqlConnection(getCnConnectionString()))
                {
                    connection.Open();

                    DateTime dateTimeStart = DateTime.Parse(DateTime.UtcNow.AddDays(-1).ToShortDateString() + " 00:00:00");
                    DateTime dateTimeEnd = DateTime.Parse(DateTime.UtcNow.AddDays(1).ToShortDateString() + " 23:59:59");

                    // need to change/confirm
                    string sqlQuery = "SELECT [req].[id], " +
                                      "       [req].[service_date_time] " +
                                      "  FROM [dbo].[cn_req] [req] " +
                                      " WHERE [req].[service_type_id] IN (1, 2) " +
                                      "   AND [req].[job_status_id] = 7 " +
                                      "   AND [req].[is_scheduled] = 1 " +
                                      "   AND [req].[service_date_time] >= @dateTimeStart " +
                                      "   AND [req].[service_date_time] <= @dateTimeEnd ";

                    using (SqlCommand command = new SqlCommand(@sqlQuery, connection))
                    {
                        command.Parameters.Add(new SqlParameter("@dateTimeStart", dateTimeStart));
                        command.Parameters.Add(new SqlParameter("@dateTimeEnd", dateTimeEnd));

                        command.Notification = null;

                        scheduledJobsDependency = new SqlDependency(command);
                        scheduledJobsDependency.OnChange += new OnChangeEventHandler((sender, e) => dependencyScheduledJobs_onChanged(sender, e));
                        if (connection.State == System.Data.ConnectionState.Closed)
                            connection.Open();

                        using (var reader = command.ExecuteReader())
                        {
                        }
                    }
                    //insertSubscription();
                }
            }
            catch (Exception ex)
            {
                modCommonFunctions.LogFile("dependencyScheduledJobs: " + ex.Message, "Error");
            }
        }

event gets triggered if there's changes from table. it remove the handler, and then call dependencyScheduledJobs() in which it will re-subscribe another event.

private void dependencyScheduledJobs_onChanged(object sender, SqlNotificationEventArgs e)
        {

            scheduledJobsDependency = (SqlDependency)sender;
            scheduledJobsDependency.OnChange -= dependencyScheduledJobs_onChanged;

            dependencyScheduledJobs();

        }

here is the problem, i've got a timer, say every hour if there's no update from the table i subscribed, reset the sql dependency. so i tried this, but it WONT unsubscribe the event. when i re-subscribe the sql dependency, by calling the dependencyScheduledJobs() again, when there's changes in the table, the dependencyScheduledJobs_onChanged event will be fired 2 times (depending how many times reset, if 10 times reset, it will fire 10 times).

public void deleteSubscription()
        {
               
                            scheduledJobsDependency.OnChange -= dependencyScheduledJobs_onChanged;

                        }

[jmcilhinney]

The problem is that you're not actually specifying that 'dependencyScheduledJobs_onChanged' method as the event handler. You're specifying a lambda that calls that method. Do you not notice the difference between the way you add and the way you remove?

scheduledJobsDependency.OnChange += new OnChangeEventHandler((sender, e) => dependencyScheduledJobs_onChanged(sender, e));

scheduledJobsDependency.OnChange -= dependencyScheduledJobs_onChanged;

That means that you never actually remove the handler that you added. You should be adding the same way as you're removing:

scheduledJobsDependency.OnChange += dependencyScheduledJobs_onChanged;

[babyekc]

my bad, i was trying to use diff way to fix it. actually, previously was this:

scheduledJobsDependency.OnChange += dependencyScheduledJobs_onChanged;

this is how i declare:

internal static SqlDependency scheduledJobsDependency = null;

i changed back to this,
on dependencyScheduledJobs() - i add the handler:

scheduledJobsDependency.OnChange += dependencyScheduledJobs_onChanged;

on dependencyScheduledJobs_onChanged - i remove the handler, and re-subscribe again on dependencyScheduledJobs():

scheduledJobsDependency.OnChange -= dependencyScheduledJobs_onChanged;

when reset - deleteSubscription(), i remove the handler, and re-subscribe again on dependencyScheduledJobs():

scheduledJobsDependency.OnChange -= dependencyScheduledJobs_onChanged;

but it still fired 2 times (depending how many times reset, if 10 times reset, it will fire 10 times) on dependencyScheduledJobs_onChanged

[PlausiblyDamp]

That looks like it should only fire the once... Could you post the actual code you are using, just in case there is something you have overlooked somewhere.

[babyekc]

Here it is:

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using Microsoft.AspNet.SignalR.Client;
using System;
using CommonLib;
using System.Reflection;

public class ConnectSqlDependency
{
    //internal static SqlCommand command = null;
    //internal static SqlDependency dependency = null;

    internal static SqlDependency scheduledJobsDependency = null;

    private string getCnConnectionString()
    {
        var connectionString = clsCrypt.DecryptText(ConfigurationManager.ConnectionStrings["cnEntities"].ToString());
        //var connectionStringBuilder = new System.Data.Entity.Core.EntityClient.EntityConnectionStringBuilder(connectionString);
        //var connectionStringProvider = connectionStringBuilder.ProviderConnectionString;
        return connectionString;
        //var connectionString = clsCrypt.DecryptText(ConfigurationManager.ConnectionStrings["cnConnection"].ConnectionString);
        //return connectionString;
    }

    public void startSqlDependency()
    {
        try
        {
            //SqlDependency.Start(getCmConnectionString());
            SqlDependency.Start(getCnConnectionString());
            //SqlDependency.Start(getGuConnectionString());
            modCommonFunctions.LogFile("Sql Dependency started", "Status");
        }
        catch (Exception ex)
        {
            modCommonFunctions.LogFile("startSqlDependency: " + ex.Message, "Error");
        }
    }

    public void stopSqlDependency()
    {
        try
        {
            //SqlDependency.Stop(getCmConnectionString());
            SqlDependency.Stop(getCnConnectionString());
            //SqlDependency.Stop(getGuConnectionString());
            modCommonFunctions.LogFile("Sql Dependency stopped", "Status");
        }
        catch (Exception ex)
        {
            modCommonFunctions.LogFile("stopSqlDependency: " + ex.Message, "Error");
        }
    }

    public void killSqlDependency()
    {
        try
        {
            // kill subscriptions
            deleteSubscription();
        }
        catch (Exception ex)
        {
            modCommonFunctions.LogFile("killSqlDependency: " + ex.Message, "Error");
        }
    }
        
    public void setDependencies()
    {
        dependencyScheduledJobs();
    }

    public void insertSubscription()
    {
        try
        {
            using (var connection = new SqlConnection(getCmConnectionString()))
            {
                connection.Open();

                string sqlQuery = "SELECT TOP 1 " +
                                    "       id, " +
                                    "       database_id, " +
                                    "       object_id " +
                                    "  FROM sys.dm_qn_subscriptions " +
                                    " ORDER BY id DESC ";

                using (SqlCommand command = new SqlCommand(@sqlQuery, connection))
                {
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            sqlQuery = "DELETE FROM cm_sql_dependency_subscription WHERE object_id =  " + reader.GetInt32(2) + ";";
                            using (SqlCommand command2 = new SqlCommand(@sqlQuery, connection))
                            {
                                command2.ExecuteNonQuery();
                                modCommonFunctions.LogFile("Subscription deleted, Object Id: " + reader.GetInt32(2), "Status");
                            }

                            sqlQuery = "INSERT INTO cm_sql_dependency_subscription (qn_subscription_id, database_id, object_id) VALUES (" + reader.GetInt32(0) + ", " + reader.GetInt32(1) + ", " + reader.GetInt32(2) + ");";
                            using (SqlCommand command3 = new SqlCommand(@sqlQuery, connection))
                            {
                                command3.ExecuteNonQuery();
                                modCommonFunctions.LogFile("Subscription created, Subscription Id: " + reader.GetInt32(0), "Status");
                            }
                        }
                    reader.Close();
                    }
                }
                connection.Close();
            }
        }
        catch (Exception ex)
        {
            modCommonFunctions.LogFile("insertSubscription: " + ex.Message, "Error");
        }
        finally
        {
            // what to do if exception raised
        }
    }

    public void deleteSubscription()
    {
        try
        {
            using (var connection = new SqlConnection(getCmConnectionString()))
            {
                connection.Open();

                string sqlQuery = "SELECT DISTINCT " +
                                    "       sds.qn_subscription_id, " +
                                    "       sds.database_id " +
                                    "  FROM cm_sql_dependency_subscription sds JOIN sys.dm_qn_subscriptions dqs " +
                                    "    ON sds.qn_subscription_id = dqs.id; ";
                    
                using (SqlCommand command = new SqlCommand(@sqlQuery, connection))
                {
                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            sqlQuery = "KILL QUERY NOTIFICATION SUBSCRIPTION " + reader.GetInt32(0) + ";";
                            using (SqlCommand command2 = new SqlCommand(@sqlQuery, connection))
                            {
                                command2.ExecuteNonQuery();
                                modCommonFunctions.LogFile("Subscription killed, Subscription Id: " + reader.GetInt32(0), "Status");
                            }                                
                        }

                        sqlQuery = "TRUNCATE TABLE cm_sql_dependency_subscription;";
                        using (SqlCommand command3 = new SqlCommand(@sqlQuery, connection))
                        {
                            command3.ExecuteNonQuery();
                            modCommonFunctions.LogFile("Sql Dependency subscriptions table truncated.", "Status");
                        }
                        reader.Close();
                    }
                }

                connection.Close();

                scheduledJobsDependency.OnChange -= dependencyScheduledJobs_onChanged;

            }
        }
        catch (Exception ex)
        {
            modCommonFunctions.LogFile("deleteSubscription: " + ex.Message, "Error");
        }
        finally
        {
            // what to do if exception raised
        }
    }
        
    public void dependencyScheduledJobs()
    {
        try
        { 
            using (var connection = new SqlConnection(getCnConnectionString()))
            {
                connection.Open();

                DateTime dateTimeStart = DateTime.Parse(DateTime.UtcNow.AddDays(-1).ToShortDateString() + " 00:00:00");
                DateTime dateTimeEnd = DateTime.Parse(DateTime.UtcNow.AddDays(1).ToShortDateString() + " 23:59:59");

                // need to change/confirm
                string sqlQuery = "SELECT [req].[id], " +
                                    "       [req].[service_date_time] " +
                                    "  FROM [dbo].[cn_req] [req] " +
                                    " WHERE [req].[service_type_id] IN (1, 2) " +
                                    "   AND [req].[job_status_id] = 7 " +
                                    "   AND [req].[is_scheduled] = 1 " +
                                    "   AND [req].[service_date_time] >= @dateTimeStart " +
                                    "   AND [req].[service_date_time] <= @dateTimeEnd ";

                using (SqlCommand command = new SqlCommand(@sqlQuery, connection))
                {
                    command.Parameters.Add(new SqlParameter("@dateTimeStart", dateTimeStart));
                    command.Parameters.Add(new SqlParameter("@dateTimeEnd", dateTimeEnd));

                    command.Notification = null;

                    scheduledJobsDependency = new SqlDependency(command);
                    scheduledJobsDependency.OnChange += dependencyScheduledJobs_onChanged;

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

                    using (var reader = command.ExecuteReader())
                    {
                    }
                }
                insertSubscription();
            }
        }
        catch (Exception ex)
        {
            modCommonFunctions.LogFile("dependencyScheduledJobs: " + ex.Message, "Error");
        }
        finally
        {
            // what to do if exception raised
        }
    }

    private void dependencyScheduledJobs_onChanged(object sender, SqlNotificationEventArgs e)
    {

        try
        {

            var queryStringData = new Dictionary<string, string>();
            queryStringData.Add("Username", "ConnectSqlDependency");

            IHubProxy hub;
            string url = ConfigurationManager.AppSettings["CommunicationHubUrl"];
            var connection = new HubConnection(url, queryStringData);
            hub = connection.CreateHubProxy("ConnectCommunicationServiceHub");
            connection.Start().Wait();

            string action = "";
            if (e.Info == SqlNotificationInfo.Insert)
            {
                action = "Insert";
            }
            else if (e.Info == SqlNotificationInfo.Update)
            {
                action = "Update";
            }
            else
            {
                action = "";
                modCommonFunctions.LogFile("Sql Dependency unhandled SqlNotificationInfo: " + e.Info.ToString(), "Status");
            }

            if (action.Length > 0)
            {
                hub.Invoke("sendDataChanged", "ScheduledJobs", action).Wait();
            }

            connection.Stop();

        }
        catch (Exception ex)
        {
            modCommonFunctions.LogFile("dependencyScheduledJobs_onChanged: " + ex.Message, "Error");
        }

        //SqlDependency scheduledJobsDependency = (SqlDependency)sender;
        //scheduledJobsDependency.OnChange -= new OnChangeEventHandler(dependencyScheduledJobs_onChanged);

        scheduledJobsDependency = (SqlDependency)sender;
        scheduledJobsDependency.OnChange -= dependencyScheduledJobs_onChanged;

        dependencyScheduledJobs();

    }

This is where unsubscribe it, on deleteSubscription():

scheduledJobsDependency.OnChange -= dependencyScheduledJobs_onChanged;

[PlausiblyDamp]

If you put a breakpoint on the line you are unsubscribing does it ever get hit?

[babyekc]

If you put a breakpoint on the line you are unsubscribing does it ever get hit?

yes it will

[HeribertoLugo]

its actually common practice to unsubscribe to an event before subscribing to it. helps prevent duplicate subscriptions, if that is your actual problem.

so put this

scheduledJobsDependency.OnChange -= dependencyScheduledJobs_onChanged;

before this

scheduledJobsDependency.OnChange += dependencyScheduledJobs_onChanged;

you can put it in the line before, but because you have so much going on in that function, i would say to put it on the first line inside the function. dont worry if its null first, thats ok. if it cant remove it because it was never there, it will just skip it.

you should do that to all functions that have code that subscribes to an event.

so looks like this:

    public void dependencyScheduledJobs()
    {
        try
        { 
            scheduledJobsDependency.OnChange -= dependencyScheduledJobs_onChanged;
            using (var connection = new SqlConnection(getCnConnectionString()))
            {
                connection.Open();

                DateTime dateTimeStart = DateTime.Parse(DateTime.UtcNow.AddDays(-1).ToShortDateString() + " 00:00:00");
                DateTime dateTimeEnd = DateTime.Parse(DateTime.UtcNow.AddDays(1).ToShortDateString() + " 23:59:59");

                // need to change/confirm
                string sqlQuery = "SELECT [req].[id], " +
                                    "       [req].[service_date_time] " +
                                    "  FROM [dbo].[cn_req] [req] " +
                                    " WHERE [req].[service_type_id] IN (1, 2) " +
                                    "   AND [req].[job_status_id] = 7 " +
                                    "   AND [req].[is_scheduled] = 1 " +
                                    "   AND [req].[service_date_time] >= @dateTimeStart " +
                                    "   AND [req].[service_date_time] <= @dateTimeEnd ";

                using (SqlCommand command = new SqlCommand(@sqlQuery, connection))
                {
                    command.Parameters.Add(new SqlParameter("@dateTimeStart", dateTimeStart));
                    command.Parameters.Add(new SqlParameter("@dateTimeEnd", dateTimeEnd));

                    command.Notification = null;

                    scheduledJobsDependency = new SqlDependency(command);
                    scheduledJobsDependency.OnChange += dependencyScheduledJobs_onChanged;

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

                    using (var reader = command.ExecuteReader())
                    {
                    }
                }
                insertSubscription();
            }
        }
        catch (Exception ex)
        {
            modCommonFunctions.LogFile("dependencyScheduledJobs: " + ex.Message, "Error");
        }
        finally
        {
            // what to do if exception raised
        }
    }

[HeribertoLugo]

After thought:
looking at your code i dont see why removing first wouldnt work for you. but im guessing whats going on is that you are adding these new handlers, and you are losing their reference. if you really must only unsubscribe in your other method, consider keeping a list of those objects you are creating and go through the list to unsubscribe.

this line here is what im talking about:

scheduledJobsDependency = new SqlDependency(command);
                    scheduledJobsDependency.OnChange += dependencyScheduledJobs_onChanged;

so i think when you remove the handler, you might be removing only the last one and this code might have been called more than once already. and then you cant remove it. so keep a

List<scheduledJobsDependency>

and to unsubscribe go through the list and unsubscribe from all of them. seems you have a race condition. but i dont know if it must be like this, or you can do it the way i wrote before.

so something like this:

scheduledJobsDependency = new SqlDependency(command);
scheduledJobsDependencies.Add(scheduledJobsDependency );
                    scheduledJobsDependency.OnChange += dependencyScheduledJobs_onChanged;

then to unsubscribe:

foreach(scheduledJobsDependency dependency in scheduledJobsDependencies)
{
dependency.OnChange -= dependencyScheduledJobs_onChanged;
}

This was going to be my actual reply before i noticed the code
if that does not work for you because for whatever reason you need to only remove it on deleteSubscription() then just use a variable for event handler.

declare the event handler variable, assign the event handler to the variable, subscribe to the event using the variable, unsubscribe from the event using the variable, set variable to null, check if variable is null - if not assign the handler and subscribe using the variable.

like this:

       private EventHandler scheduledJobsDependencyHandler;
        public void deleteSubscription()
        {
            // .....
            // your other code
            scheduledJobsDependency.OnChange -= this.scheduledJobsDependencyHandler;
            this.scheduledJobsDependencyHandler = null;
            // .....
            // your other code
        }

        public void dependencyScheduledJobs()
        {
            // .....
            // your other code

            if (scheduledJobsDependencyHandler == null)
            {
                this.scheduledJobsDependencyHandler = dependencyScheduledJobs_onChanged;
                scheduledJobsDependency.OnChange += this.scheduledJobsDependencyHandler;
            }
            // .....scheduledJobsDependency.OnChange += dependencyScheduledJobs_onChanged
            // your other code
        }