c# - 无法使用 SqlDependency 监视多个表

Question

我的数据库中有两个表，一个记录异常，另一个记录日志消息。

我正在利用 SqlDependency 对象在这些表更改时收到通知，以便我可以更新我的网络仪表板。我得到了这个工作:

public IEnumerable<ElmahException> GetExceptions()
    {
        using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["elmah-sqlserver"].ConnectionString))
        {
            connection.Open();
            using (SqlCommand command = new SqlCommand(@"SELECT [ErrorId],[Application],[Host],[Type],[Source],[Message],[User],[StatusCode],[TimeUtc],[Sequence],[AllXml]
           FROM [dbo].[ELMAH_Error] ORDER BY [TimeUtc] desc", connection))
            {
                // Make sure the command object does not already have
                // a notification object associated with it.
                command.Notification = null;

                SqlDependency dependency = new SqlDependency(command);
                dependency.OnChange += new OnChangeEventHandler(ELMAHdependency_OnChange);

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

                using (var reader = command.ExecuteReader())
                    return reader.Cast<IDataRecord>()
                        .Select(x => new ElmahException()
                        {
                            ErrorId = x.GetGuid(0),
                            Application = x.GetString(1),
                            Host = x.GetString(2),
                            Type = x.GetString(3),
                            Source = x.GetString(4),
                            Error = x.GetString(5),
                            User = x.GetString(6),
                            Code = x.GetInt32(7),
                            TimeStamp = x.GetDateTime(8).ToString().Replace("T", " ")
                        }).ToList();
            }

        }
    }

    private void ELMAHdependency_OnChange(object sender, SqlNotificationEventArgs e)
    {
        Console.Write("Exception table changed!");
    }

这工作得很好，所以在我顺风顺水的情况下，我开始尝试对日志消息做类似的事情:

 public IEnumerable<LogMessage> GetLogMessages()
    {
        using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["elmah-sqlserver"].ConnectionString))
        {
            connection.Open();
            using (SqlCommand command = new SqlCommand(@"SELECT [application],[time_stamp],[logLevel],[logger],[message]
           FROM [dbo].[LogTable] ORDER BY [time_stamp] desc", connection))
            {
                // Make sure the command object does not already have
                // a notification object associated with it.
                command.Notification = null;

                SqlDependency dependency = new SqlDependency(command);
                dependency.OnChange += new OnChangeEventHandler(NLOGdependency_OnChange);

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

                using (var reader = command.ExecuteReader())
                    return reader.Cast<IDataRecord>()
                        .Select(x => new LogMessage()
                        {
                            Application = x.GetString(0),
                            TimeStamp = x.GetDateTime(1).ToString().Replace("T", " "),
                            LogLevel = x.GetString(2),
                            Logger = x.GetString(3),
                            Message = x.GetString(4)
                        }).ToList();
            }

        }
    }

    private void NLOGdependency_OnChange(object sender, SqlNotificationEventArgs e)
    {
        Console.Write("Log table has changed!");
    }

此时，我仅在日志表发生更改时收到警报。有了这个额外的 SqlDependency，ELMAHdependency_OnChange 永远不会被调用。如果我注释掉我的 GetLogMessages() 方法，则会再次调用 ELMAHdependency_OnChange。

看起来多个 SqlDependency 对象是互斥的。关于如何同时监控两个表的任何想法？

Answer 1

可以使用分号连接另一个 SqlStatement。

这是您的代码片段，包含我的更改。

 [...]
 connection.Open();

 var queries = new [] {@"SELECT [application],[time_stamp],[logLevel],[logger],[message] FROM [dbo].[LogTable] ORDER BY [time_stamp] desc",
                       @"SELECT [ErrorId],[Application],[Host],[Type],[Source],[Message],[User],[StatusCode],[TimeUtc],[Sequence],[AllXml] FROM [dbo].[ELMAH_Error] ORDER BY [TimeUtc] desc"};

 using (SqlCommand command = new SqlCommand(string.Join("; ", queries), connection))
 {
 [...]

在调用事件后重新注册 SqlDependency 也很重要。否则事件只触发一次..

    private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
    {
        SqlDependency dependency = sender as SqlDependency;
        if (dependency != null) dependency.OnChange -= dependency_OnChange;

        if (e.Type == SqlNotificationType.Change)
        {
            // Do things
        }
        SetupDatabaseDependency();
    }

SetupDatabaseDependency() 将包含设置 SqlDependency 的代码。