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

Answer 1

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

Answer 2

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