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?

Can't you reconfigure Elmah to notify you the same time you write logs to DB? E.g. log4net calls that feature appenders. — OzrenTkalcecKrznaric
– OzrenTkalcecKrznaric, Commented Oct 25, 2013 at 18:33
For simplicity's sake, I'm not showing the SignalR wiring I'm using to communicate real-time with my web dashboard. The idea is the SqlDependency fires it's OnChange event, which alerts SignalR to notify all the subscribed clients and update the dashboards as log messages and exceptions are recorded. Works great - but only if I monitor one table! — Mister Epic
– Mister Epic, Commented Oct 25, 2013 at 19:15
So you want to notify via SignalR when there is a new log record. In log4net I'd create a custom SignalRAppender like this and e.g. attach it to the root alongside the AdoNetAppender. When log gets written, one appender writes to DB and the other one uses SignalR to push data to the client. I don't know how to do that using Elmah :( — OzrenTkalcecKrznaric
– OzrenTkalcecKrznaric, Commented Oct 25, 2013 at 19:24
The db is going to be monitored by other clients as well, I'm just trying to get some real-time monitoring going with a web dashboard... it just seems strange I can't monitor more than one table at a time! — Mister Epic
– Mister Epic, Commented Oct 25, 2013 at 19:29

krizzzn · Accepted Answer · 2015-05-05 09:40:06Z

8

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.

answered May 5, 2015 at 9:40

krizzzn

1,5021 gold badge12 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Canela Over a year ago

Thanks mate, it helped me! Also, I've created a full class that handles it - see below stackoverflow.com/a/44036662/3141018

edhubbell · Accepted Answer · 2014-01-08 21:33:22Z

Use a stored procedure that selects from both tables instead of a query.

CREATE PROCEDURE [dbo].[SQLDependency_TestTable1_TestTable2]
    @MaxIdxTestTable1 INT = 1, @MaxIdxTestTable2 INT = 1
AS
-- Don't do this - SQLDependency doesn't like.
--SET TRANSACTION ISOLATION LEVEL READ COMMITTED

-- Don't do this either - SQLDependency doesn't like.
--SELECT MAX(ID) FROM ehmetrology.TestTable1 
--SELECT COUNT(ID) FROM ehmetrology.TestTable1

-- See here for a whole list of things SQLDependency doesn't like:
-- stackoverflow.com/questions/7588572/what-are-the-limitations-of-sqldependency/7588660#7588660

SELECT DCIdx FROM TestTable1 WHERE Idx >= @MaxIdxTestTable1 
ORDER BY DCIdx DESC;

SELECT DCIdx FROM TestTable2 WHERE Idx >= @MaxIdxTestTable2 
ORDER BY DCIdx DESC;

GO

And then do this on the .NET side (pardon the VB):

    Using adapter As New SqlDataAdapter(mSQLD_Command)
        adapter.Fill(mSQLD_DataSet, SQLD_DATASET_TABLENAME)
    End Using

    ' Reload the dataset that's bound to the grid.
    If mSQLD_DataSet.Tables.Count = 2 Then
        Dim iTest1Index As Integer = 0
        Dim iTest2Index As Integer = 0

        If Integer.TryParse(mSQLD_DataSet.Tables(0).Rows(0).Item(0).ToString, iTest1Index) Then
            If iTest1Index<> moTest1.MaxDCIdx Then
                GetTest1Data(True)
            End If
        End If

        If Integer.TryParse(mSQLD_DataSet.Tables(1).Rows(0).Item(0).ToString, iTest2Index) Then
            If iTest2Index <> moTest2.MaxDCIdx Then
                GetTest2Data()
            End If
        End If

    End If

By using a stored procedure, you don't have all those records moving around as you do with a consistent select statement. You'll get notified each time either one of the 2 tables are modified, so you have to dig into the result to figure out which one has changed.

Collectives™ on Stack Overflow

Cannot monitor multiple tables with SqlDependency

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related