SQL Server notifies the web server of a table change

Question

Is there a way to notify the web server (in my case, the web server hosting the MVC2 webapp in C #) that a database failure has occurred? The goal is to synchronize the web server cache with the database.

ASP.NET has polling objects that allow this, but I prefer the push system. I also believe that elements other than the web server itself can be manipulated to the database.

My limited SQL Server / ASP MVC know-how says one way is to create a TRIGGER table, which pretty much affects the URL that will force the update.

Answer 1

SqlDependency is the way to go if the rate of change is moderate. For high rates of change, you are better off polling the changes. To understand how SqlDependency works, read the Mysterious Notification . ASP already has built-in support for SqlDependency, namely SqlCacheDependency . There is also LinqToCache , which adds the SqlDependency feature to any arbitrary LINQ query, if possible.

The trigger is absolutely big, no no. You cannot expect database transactions to wait on some URL to respond, your performance will crash mostly. Not to mention the availability issue (updates will fail if the URL is not responding for any reason).

Answer 2

I have no experience with it myself, but the System.Data.SqlClient.SqlDependency class looks like what you need.

According to MSDN:

SqlDependency is ideal for caching scenarios where your ASP.NET application or mid-tier service needs to store certain information cached in memory. SqlDependency allows you to receive notifications when the original data in the database changes so that the cache can be updated.

Answer 3

The only way I can think of is to write a custom CLR function (UDF) and call it using insert / update / delete triggers in the table of interest.

UDF should spawn a stream that is signaled by the web server and returns immediately to the trigger. For obvious reasons, he considered a really bad form for a call in a trigger or a running transaction of any potentially long-term or probable unsuccessful operation (for example, when accessing the network). Unless, of course, you like the anger of database manufacturers to fall on you, like Maxwell Silver Hammer.

Answer 4

Your "limited SQL Server know-how" is good enough. You must create an AFTER INSERT, UPDATE, DELETE trigger that will do what you need.

Another thing is that this is a bad idea, but you probably know about it.

The correct way to solve such problems is to use some message queue, for example. MSMQ

Answer 5

To receive notification from the database when the contents of the table change / update, you can use TableDependency .

The difference with .NET SqlDependency is that TableDependency raises events that contain database table values ​​that have been changed / deleted / inserted.

Using SqlDependency , you must make a choice to receive fresh data at any time. SqlDependency notify your code that something has changed in the database table.

With TableDependency you can avoid this because the event you receive contains all deleted / inserted / changed values:

 string conString = "data source=.;initial catalog=myDB;integrated security=True"; using(var tableDependency = new SqlTableDependency<Customers>(conString)) { tableDependency.OnChanged += TableDependency_Changed; tableDependency.Start(); Console.WriteLine("Waiting for receiving notifications..."); Console.WriteLine("Press a key to stop"); Console.ReadKey(); } ... ... void TableDependency_Changed(object sender, RecordChangedEventArgs<Customers> e) { if (e.ChangeType != ChangeType.None) { var changedEntity = e.Entity; Console.WriteLine("DML operation: " + e.ChangeType); Console.WriteLine("ID: " + changedEntity.Id); Console.WriteLine("Name: " + changedEntity.Name); Console.WriteLine("Surname: " + changedEntity.Surname); } }