15

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.

Andre Pena
  • 56,650
  • 48
  • 196
  • 243
  • System.Web.Caching.CacheDependency is supposed doing what you are looking for it – Alberto León Sep 10 '12 at 14:01
  • 1
    I don't know if it is supported by Azure, but [`SqlDependency`](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldependency.aspx) might be just the thing. – Oded Sep 10 '12 at 14:02
  • 1
    @AlbertoLeón - It is better to use `SqlDependency` if not in a web context. – Oded Sep 10 '12 at 14:02
  • I like the SQLDependency answer. How does the data get inserted? Could you raise an event there? – paparazzo Sep 10 '12 at 14:16
  • I like it too, I'm still reading about. @Blam, I could raise an event, I'm just not quite sure how – Andre Pena Sep 10 '12 at 14:19
  • Not sure about handling and event on thread. But it was only a comment. http://msdn.microsoft.com/en-us/library/9aackb16(v=vs.100) – paparazzo Sep 10 '12 at 14:28
  • This link here on SO may be interesting: http://stackoverflow.com/questions/11275559/sqldependency-solution-for-azure-table – Simon Mourier Sep 10 '12 at 14:28
  • just a quick note... that SQL Server on Azure and SQL Azure (now SQL Database) are two different things with different capabilities. There are definitely things you can't do with SQL Azure that may be possible if you host your own instance of SQL Server in a Virtual Machine (IaaS vs. PaaS of SQL Database). There's certainly a tradeoff here, but I can see from the responses that people are answering with one or the other offering in mind – Jim O'Neil Sep 10 '12 at 15:56
  • I followed [this](http://techbrij.com/database-change-notifications-asp-net-signalr-sqldependency) post and successfully used SqlSependency to trigger database changes to show push notifications to client using signalR but recently we moved to SQL Azure and it doesnt support SqlSependency so Is there a better way than [this](http://stackoverflow.com/questions/9880091/monitor-data-changes-in-sql-azure) to get notifications when SQL Azure data changes or when new data is inserted, whats your suggestion as you have accepted the answer ? – Shaiju T Jul 20 '16 at 17:56

2 Answers2

10

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.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
5

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.

Christian Del Bianco
  • 1,008
  • 13
  • 13