temporarily suspend sqldependency notification for update

Question

I have a SELECT query that I am monitoring using SQLDependency. Everything works fine, but I would like to temporarily suspend the dependency while I make an UPDATE to the data set that the query is monitoring, one that I know will fire the change notification from the database.

Currently I set a flag that marks the fact that I'm doing the update, let the dependency fire and then I reset it so I can continue monitoring the query.

There has to be a more elegant way to do it than that.

Could you not Stop the listener and Start it after the update? — Rhys Jones, Feb 06 '15 at 08:30

score 0 · Answer 1 · edited May 23 '17 at 11:43

Be careful using SqlDependency class - it has the problems with memory leaks. Hovewer, you can use an open source realization of the SqlDependency class - SqlDependencyEx. It uses a database trigger and native Service Broker notification to receive events about the table changes. This is an usage example:

int changesReceived = 0;
using (SqlDependencyEx sqlDependency = new SqlDependencyEx(
          TEST_CONNECTION_STRING, TEST_DATABASE_NAME, TEST_TABLE_NAME)) 
{
    sqlDependency.TableChanged += (o, e) => changesReceived++;
    sqlDependency.Start();

    // Make table changes.
    MakeTableInsertDeleteChanges(changesCount);

    // Wait a little bit to receive all changes.
    Thread.Sleep(1000);
}

Assert.AreEqual(changesCount, changesReceived);

With SqlDependecyEx you are able to monitor just INSERT or DELETE, avoiding UPDATE. Hope this helps.

temporarily suspend sqldependency notification for update

1 Answers1