How can I signal a c# console app from SQL Server?

Question

Rather than poll against some tables, I'd like to signal a waiting c# app that there are new rows to be processed in a table, maybe via a trigger. Is there some way for the database to signal to a console app, or am I stuck polling the table looking for new rows?

Answer 1

Take a look at Query Notifications (SQL Server 2005+).

Microsoft SQL Server 2005 introduces query notifications, new functionality that allows an application to request a notification from SQL Server when the results of a query change. Query notifications allow programmers to design applications that query the database only when there is a change to information that the application has previously retrieved.

There is an example here of how to write a simple form app to register a query for notification: http://msdn.microsoft.com/en-us/library/a52dhwx7(VS.80).aspx.

This does require the Service Broker to be enabled on the database.

You should take a look at the notes in the Remarks section of the MSDN SqlDependency documentation to make sure it is the right choice for your scenario

Answer 2

Check if SqlCacheDependency can be of any use...

http://www.asp.net/data-access/tutorials/using-sql-cache-dependencies-cs