Is there something like FileSystemWatcher tables for Sql Server?

Question

I would like my Windows service (written in .NET) to recognize when new rows are added to a specific table, but instead of pulling data from an SQL server, I would like to use a push server model.

Does anyone have a clue how to do this? I am using SQL Server 2005.

TIA

Answer 1

There is also an ADO.NET SqlDependency mechanism if you use ADO.NET on the client side with C # or VB.NET

The SqlDependency object may be associated with the SqlCommand in order to determine when the query results differ from those originally received. You can also designate a delegate for the OnChange event, which fires when the results change for the associated command. You must associate SqlDependency with the command before you execute the command. The HasChanges SqlDependency property can also be used to determine if query results have changed since the data was first received.

Basically, you bind SqlDependency to your SqlCommand and provide an event handler that is called when the values ​​that make up the result set of this 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 do what you need.

 void OnChange(object sender, SqlNotificationEventArgs e) { SqlDependency dependency = sender as SqlDependency; (do whatever you need to do - eg reload the data) } 

Mark

Answer 2

The closest you get to Sql Server 2005 is the trigger. Sql Server 2008 also has a collection of change data.

Answer 3

Yes there is.

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

Answer 4

You can use a trigger with a web service call from the database. But I do not know how bad (if any) the impact will be against the database.

Answer 5

Be careful when using the SqlDependency class to monitor changes to database tables - it has problems with memory leaks. However, you can use your own implementation with DDL triggers and the SQL Service Broker API, or use one of the open source projects, for example. 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.