SqlDependency onchange event infinite loop

Question

I have a simple query, and the event fires at the correct time. However, once fired, the property, .HasChanges, of the SqlDependency object is always set as true.

The first time OnChange is fired, the SqlNotificationEventArgs Info property is "Inserted". The second time the event is fired it's "Already Changed".

I commented all of my code in the OnChange event out to verify that my code wasn't causing the change.
Servicebroker is enabled in the database

Is there a reason the following code causes an infinite loop of onChange events?

static void Main()
{
    SqlDependency.Stop(Properties.Settings.Default.DEVConnectionString);
    SqlDependency.Start(Properties.Settings.Default.DEVConnectionString);

    using (SqlConnection cn = new SqlConnection(Properties.Settings.Default.DEVConnectionString))
    {
        cn.Open();

        using (SqlCommand cmd = new SqlCommand("SELECT UserPageActionLogID, PageActionID FROM dbo.UserPageActionLog WHERE PageActionID != 3 ORDER BY UserPageActionLogID ASC", cn))
        {
            cmd.Notification = null;

            SqlDependency dep = new SqlDependency(cmd);
            dep.OnChange += dep_onchange;

            using (SqlDataReader dr = cmd.ExecuteReader())
            {
                while (dr.Read())
                {
                    //Do nothing on first run
                }
            }
        }
    }
    Application.Run(); //Prevents the application from closing
}

private static void dep_onchange(object sender, SqlNotificationEventArgs e)
{
    SqlDependency dependency = sender as SqlDependency;
    dependency.OnChange -= dep_onchange;

    //Do stuff for the function. I commented this out and still had an issue

    //Resubscribe to the event to continue catching future changes
    dependency.OnChange += dep_onchange;
}

Did you test without the code that unsubscribes and resubscribes? Perhaps that sort of work should be performed outside of the event handler. — Dan Ling, May 28 '14 at 19:36
@DanLing I don't think it can be resubscribed to outside of the onchange function. The line where I call Application.Run() means the program is just waiting. After onchange event runs nothing else will be called. I'd have no way of adding the handler after the event fires. — Brandon, May 28 '14 at 19:40
@DanLing Also, it wouldn't matter anyway. HasChanges is still set to true. No matter when I resubscribe, the event will be fired as soon as I resubscribe. — Brandon, May 28 '14 at 19:42

score 2 · Accepted Answer · answered May 28 '14 at 20:09

It appears that the both the OnChange handler and the SqlDependency instance are only good for ONE event. After the event is fired and you unsubscribe the handler, you need to register your handler to a NEW SqlDependency object.

Please see the link here for full details: http://msdn.microsoft.com/en-us/library/a52dhwx7(v=vs.80).aspx

score 0 · Answer 2 · answered Aug 28 '20 at 18:02

Here is how I handled it...

public class ClientClass
{
    public ClientClass()
    {
        var watchForChange = new WatchForChange(connectionString);
        watchForChange.TableChanged += WatchForChange_TableChanged;
        watchForChange.StartWatching();
    }

    private void WatchForChange_TableChanged(object sender, EventArgs e)
    {
        // Some COde
    }
}


public class WatchForChange
{
    // Should implement IDisposable
    private string _connectionString;
    private SqlDependency _sqlWatcher;

    public WatchForChange(string connectionString)
    {
        _connectionString = connectionString;
    }

    public void StartWatching()
    {
        using (var sqlConnection = new SqlConnection(_connectionString))
        {
            sqlConnection.Open();
            using var sqlCommand = new SqlCommand("select somefield from dbo.sometable", sqlConnection);
            {
                SqlDependency.Start(_connectionString);
                _sqlWatcher = new SqlDependency(sqlCommand);
                _sqlWatcher.OnChange += _sqlWatcher_OnChange;
            }

            // Notifies SQL Server that something is listening for changes to this table
            using var sqlDataReader = sqlCommand.ExecuteReader();
        }
    }

    private void _sqlWatcher_OnChange(object sender, SqlNotificationEventArgs e)
    {
        // Unsubscribe and set to null
        _sqlWatcher.OnChange -= _sqlWatcher_OnChange;
        _sqlWatcher = null;

        SqlNotificationInfo sqlNotificationInfo = e.Info;

        // Raise the event on Inserts and Updates
        if (sqlNotificationInfo.Equals(SqlNotificationInfo.Insert) || sqlNotificationInfo.Equals(SqlNotificationInfo.Update))
        {
            OnTableChanged(e);
        }

        // Create a new instance of _sqlWatcher (SqlDependency)
        StartWatching();
    }

    protected virtual void OnTableChanged(EventArgs e)
    {
        TableChanged?.Invoke(this, e);
    }

    public event EventHandler TableChanged;

}

SqlDependency onchange event infinite loop

2 Answers2

Linked