SqlDependency onchange无限循环

Question

我有一个简单的查询，事件在正确的时间触发。但是，一旦触发，SqlDependency对象的属性SqlDependency始终设置为true。

第一次触发OnChange时，SqlNotificationEventArgs信息属性将被“插入”。第二次触发事件时，它“已经改变了”。

• 我在OnChange事件中注释了我的所有代码，以验证我的代码没有导致更改。
• 在数据库中启用Servicebroker。

下面的代码会导致无限循环的onChange事件吗？

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;
}

Answer 1

看来，OnChange处理程序和SqlDependency实例都只适用于一个事件。在触发事件并取消订阅处理程序之后，需要将处理程序注册到新的SqlDependency对象。

有关详细信息，请参阅此处的链接：http://msdn.microsoft.com/en-us/library/a52dhwx7(v=vs.80).aspx

Answer 2

我就是这样处理的..。

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;

}