How do I check there is no OnChangeEventHandler before adding a new one in Sql Server

Question

I understand that I need to constantly renew the OnChangeEventHandler every time a callback is received.

I would like to ensure that there is no existing handler before adding a new one.

How do I do this? Is there a count? (see HERE comment)?

   public void InitialiseDependency(Action onDependencyMethod)
    {
        this.onDependencyMethod = onDependencyMethod;
        SqlDependency.Start(connectionString, null);

        using (SqlCommand command = new SqlCommand(
                "SELECT [Symbol] FROM [dbo].[tblOrders] WHERE [Status] = 'NEW'",
                conn))
        {

            SqlDependency dependency = new SqlDependency(command);

            //HERE if (dependency.OnChangeEventHandlers.count()) > 0 { return; }

            dependency.OnChange += new OnChangeEventHandler(OnDependencyChange);

            using (SqlDataReader reader = command.ExecuteReader())
            {
                // Process the DataReader.
            }
        }
    }

Does `dependency.OnChange` have a method like `dependency.OnChange.GetInvocationList()` ? — Mike Nakis, Dec 10 '15 at 16:20
@MikeNakis It does, but you can't use `OnChange` anywhere other than on the left side of a `+=` or `-=`, so you can't directly get the invocation list or cast it to a `MultiCastDelegate`. — Ron Beyer, Dec 10 '15 at 16:26
@MikeNakis No, its an `event`, which changes the rules a bit. — Ron Beyer, Dec 10 '15 at 16:28

Ron Beyer · Answer 1 · 2015-12-10T16:32:20.517

0

There isn't an easy way to do this, but if you look through the reference source, you can see that there is a field _eventList that gets added to each time the OnChange event is added to.

So in order to get that number, we need to do some reflection:

var countList = dependency.GetType().GetField("_eventList",
                    BindingFlags.NonPublic | BindingFlags.Instance)
                    .GetValue(dependency);

The next issue is that the countList is a List of an internal class, but it is returned as an object. In order to get the count, we need to cast it to an IList first:

int count = ((IList)countList).Count;

After that, you should have a count of the number of event subscribers. Its important to note that it only works for this type, since other types do not necessarily implement events in the same way.

edited Dec 10 '15 at 16:32

answered Dec 10 '15 at 16:26

Ron Beyer

11,003
1
19
37

But dependency is created new each time in my code (I followed examples I found on web) will it have evenlists from before? – ManInMoon Dec 10 '15 at 16:35
No, in that case I'm not sure how you can reasonably expect that something would already be subscribed to it? – Ron Beyer Dec 10 '15 at 16:52
Well, the dependency trigger does live past the scope of the variable in the above method. I really find it hard to understand what is happening. I assume, the dependency in the code is temporary and just serves to set a dependency at the server... – ManInMoon Dec 10 '15 at 22:16
What you are creating is a memory leak if you let `dependency` go out of scope while having subscribed event handlers on it. This is really bad practice since you can't clean up those resources, basically you have zombie objects lying around in memory that you can't clean up. The `dependency` is not temporary in code, you need to hold a reference to it. – Ron Beyer Dec 10 '15 at 22:19

How do I check there is no OnChangeEventHandler before adding a new one in Sql Server

1 Answers1