Wednesday, October 19, 2011

Notify Database changes to ASP.NET pages

In the old times there were two ways of getting notifications from the database into the ASP.NET pages.
  1. 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.

  2. 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

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


It is a good practice to stop the notifications on Application_End


Once this is implemented, you can get the notifications through the OnChange event of the SqlDependency class.