- writing to a file (simply create or delete an empty file) in a specific location where a CacheDependency can listen to and callBack a method on change.
- implement an HttpHandler that gets the notification with a parameter of what is changed, and let the database send an http request to the IIS server on a trigger.
I am sure there could be worse methods but even these two have a lot of complications in a secured architecture with high performance requirements, like we have these days.
Fortunately, since SQL-Servet 2000, there is a new component implemented into SQL-server called Service Broker that act on changes in tables that are marked to be notified on change. Internally they write to a specific table mentioning about the change.
In order to configure a database for Service Broker you can call a statement like this:
Use Northwind
ALTER DATABASE Northwind SET ENABLE_BROKER
For more information about Service Broker see the MSDN.
Next, you need to set the ASP.NET to recieve the notification via dependencies. To do so , you need to add the command object to the dependency:
// Create the dependency.
SqlCacheDependency empDependency = new SqlCacheDependency(cmd);
// Add a cache item that will be invalidated if one of its records changes
// (or a new record is added in the same range).
Cache.Insert("Employees", ds, empDependency);
To start the notification on application service you need to add the following to the Global.ascx.cs in Application_Start
SqlDependency.Start(connectionString);
It is a good practice to stop the notifications on Application_End
SqlDependency.Stop(connectionString);
Once this is implemented, you can get the notifications through the OnChange event of the SqlDependency class.
