Is there a way to be notified in .NET when data changes in the Sql Server? Like an Event?

Question

Is there a way to bind an event to Sql Server (specially Azure) so that, when there's new data in a table, for instance, my .NET process will be notified?

Another option that I tought about would be a Sql Server long polling query. That is, I execute a query but it does not return anything until there's something to return or when a timeout is reached.

To be more clear about my problem, I have a different threads in different instances of Windows Azure waiting for notifications. They must take action as soon as there is a new notification. I'd like not to keep them querying the database or the storage every 2 seconds.

Answer 1

You can use SqlTableDependency

SqlTableDependency is a generic C# component used to receive notifications when the content of a specified database table change.

What is the difference with .NET SqlDepenency ?

Basically, the main difference is that SqlTableDependency send events containing values for the record inserted, changed or deleted, as well as the DML operation (insert/delete/update) executed on the table: SqlDepenency doesn't tell what data was changed on the database table, they only say that something has changed.

Answer 2

Not in Azure. In box product you have Query Notifications and its derivatives (SqlNotificationRequest, SqlDependency and SqlCacheDependency), see The Mysterious Notification for details. You can use it as a LINQ wrapper with LinqToCache.

But Query Notifications are not supported on Azure. On Azure your application should notify whenever it updates the database, using Azure Queues.