SQL Server notify web server of table change

Is there a way of notifying a web server (in my case, a web server hosting a MVC2 webapp in C#) of the fact that there was a change to the database? The purpose is to keep the web server cache synchronized with the database.

There are polling constructs in ASP.NET that allow this, but I prefer a push system. I'm also under the assumption that the database can be manipulated by elements other than the web server its开发者_开发问答elf.

My limited know-how of SQL Server/ASP MVC says that one way is through creating a table TRIGGER that pretty much hits a url which will force an update.

---

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

Trigger is absolutely a big no-no. You cannot have database transactions wait on some URL to respond, your performance will collapse to basically nothing. Not to mention the issue of availability (updates will fail if the URL does not respond, for whatever reason).

---

I don't have the experience with it myself, but System.Data.SqlClient.SqlDependency class looks like whet you need.

According to MSDN:

SqlDependency is ideal for caching scenarios, where your ASP.NET application or middle-tier service needs to keep 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 refreshed.

---

The only way I can think of is to write a CLR user-defined function (UDF) and invoke it via insert/update/delete triggers on the table(s) of interest.

The UDF should spawn a thread that signals the web server and immediately returns to the trigger. For obvious reasons, it's considered really bad form to invoke within a trigger or a running transaction any potentially long running or likely to fail operation (like accessing the network.) Unless, of course, you enjoy having the wrath of the production DBAs coming down on you like Maxwell's Silver Hammer.

---

Your "limited know-how of SQL Server" is good enough. You should create an AFTER INSERT, UPDATE, DELETE trigger that's going to do what you need.

Another thing is that this is a bad idea, but you're probably aware of that..

The proper way to resolve this kind of issues is to use some kind of message queue eg. MsMQ.

---

To get notification from database when table content is changed/updated, you can use TableDependency.

The difference with .NET SqlDependency is that TableDependency raise events containing the database table values changed / deleted / inserted.

Using SqlDependency you have to execute a select to get fresh data any time SqlDependency notify your code that something has changed on database table.

With TableDependency you can avoid that because the event you receive contains all deleted/inserted/modified 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);
    }
}