Cannot monitor multiple tables with SqlDependency

Question

I have two tables in my db, one that records exceptions, and another that records log messages.

I am leveraging the SqlDependency object to be notified when those tables change so that I can update my web dashboard. I got this working:

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!");
    }

This is working well, so with the wind in my sails, I then took a crack at doing something similar for the log messages:

 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!");
    }

At this point, I am alerted only to when the log table has changed. With this additional SqlDependency in the mix, ELMAHdependency_OnChange never gets called. If I comment out my GetLogMessages() method, then ELMAHdependency_OnChange is called once more.

It looks like multiple SqlDependency objects are mutually exclusive. Any ideas on how I can monitor two tables at the same time?

Answer 1

It is possible to concatenate another SqlStatement using a semicolon.

Here's a snippet from your code, with my changes.

 [...]
 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))
 {
 [...]

It's also important to re-register the SqlDependency once it has called the event. Or else the event is only triggered once..

    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() would contain the code to set up the SqlDependency.