Is there something like the FileSystemWatcher for Sql Server Tables?

Question

i would like my windows service (to be written in .NET) to recognize when new rows are added to a specific table, but instead of pulling the data from the sql-server i would like to use a server push model.

does somebody has a hint for me how to achieve this? i am using sql server 2005.

tia

marc_s · Accepted Answer · 2009-08-21T13:37:22.383

There's also the ADO.NET SqlDependency mechanism if you're using client side ADO.NET with C# or VB.NET

A SqlDependency object can be associated with a SqlCommand in order to detect when query results differ from those originally retrieved. You can also assign a delegate to the OnChange event, which will fire when the results change for an associated command. You must associate the SqlDependency with the command before you execute the command. The HasChanges property of the SqlDependency can also be used to determine if the query results have changed since the data was first retrieved.

You basically associate a SqlDependency with your SqlCommand, and provide an event handler that gets called when values that make up the result set of that SqlDependency change.

using(SqlCommand cmd = new SqlCommand(queryStatement, _conn))
{ 
   cmd.Notification = null;

   SqlDependency dependency = new SqlDependency(cmd);

   dependency.OnChange += 
       new OnChangeEventHandler(OnChange);

    ......
}

In the event handler, you can then do whathever you need to do.

void OnChange(object sender, SqlNotificationEventArgs e)
{
  SqlDependency dependency = sender as SqlDependency;

  (do whatever you need to do - e.g. reload the data)

}

Marc

This is a great solution and very well may be the best in your situation, however, I'm pretty sure the "Server" (SQL server) is not "Pushing" anything... I'm curious, though, what *is* going on under the covers? — Mike DeFehr, Aug 21 '09 at 14:35
@Mike: the server **is** pushing. See here: http://rusanu.com/2006/06/17/the-mysterious-notification/ — Remus Rusanu, Aug 21 '09 at 14:55
@Marc: actually Query Notifications are available to ODBC and OleDB clients too: http://msdn.microsoft.com/en-us/library/ms130764.aspx — Remus Rusanu, Aug 21 '09 at 15:02
@Remus: excellent write-up about Sql Dependency! May I quote that in the future? :-) — marc_s, Aug 21 '09 at 20:36

score 3 · Answer 2 · answered Aug 21 '09 at 13:25

3

The closest you'll get in Sql Server 2005 is a trigger. Sql Server 2008 also has change data capture.

answered Aug 21 '09 at 13:25

Joel Coehoorn

399,467
113
570
794

score 1 · Answer 3 · answered Aug 21 '09 at 13:26

1

Yes, there is.

http://msdn.microsoft.com/en-us/library/ms171157(SQL.90).aspx

answered Aug 21 '09 at 13:26

Leahn Novash

2,861
2
20
18

Given that Notification Services were swiftly dropped again for the 2008 version, I'd not be recommending a new solution based on that. As marc_s posted, the SqlDependency/SqlNotificationRequest classes can be used from managed code. – Damien_The_Unbeliever Aug 21 '09 at 13:32
I didn't know it was dropped. It is not like I have touched it in a couple of years. However, the OP explicitly states that he is using SQL Server 2005, so it would not be a problem. That said, you are right about marc_s idea being a better one. It seems so much better to not to share the responsability with an external service. – Leahn Novash Aug 21 '09 at 14:18

score 0 · Answer 4 · answered Aug 21 '09 at 13:43

0

You could use a trigger with a webservice call from the database. But I have no idea how bad (if any) impact will be against the database.

answered Aug 21 '09 at 13:43

Matthew Whited

22,160
4
52
69

score 0 · Answer 5 · edited May 23 '17 at 12:24

Be careful using SqlDependency class to monitor changes in database tables - it has problems with memory leaks. However, you can use your own realization with DDL triggers and SQL Service Broker API or use one of open source projects, e.g. SqlDependencyEx:

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

Hope this helps.

Is there something like the FileSystemWatcher for Sql Server Tables?

5 Answers5

Linked