How to refresh a page when record in db table get changed in ASP.Net 3.5

Question

I have a page that contains a Gridview showing a record from a db table e.g. "tblEmployee". If a record is inserted/updated/deleted in that table then I need my page to be reloaded so that the gridview will show the updated records. Records may be inserted/updated/deleted from any other application. To rebind the gridview I have to reload the page. Suppose I open the page in my browser and no postback is done. After 10 minutes a record is inserted into the table by some other apps. How can I reload the page automatically, not manually by clicking refresh button when the records in db table changed? I am using Sql server 2005 as DB and ASP.Net 3.5(C#)

Please suggest how to implement this.

Thanks.

Answer 1

You could use the SqlDependency class:

Here's a getting started example: SqlDependency in an ASP.NET Application (ADO.NET)

Answer 2

I see not other option that having a polling mechanism, i.e. using setTimeout+jQuery or ASP.NET AJAX Timer that pulls the information at intervals.

Basically what you want is for the server to notify the client that an update has occurred and in a web/ASP.NET application communication is always initiated by the client. So polling is your only option.

Answer 3

Here's two approaches... one using a trigger, the other using SqlDependency. I would recommend using SqlDependency, but I'm outlining another approach because you indicated trouble with SqlDependency.

Create a table with columns for table name and last update date. Create a trigger on tblEmployee to update that table on any insert/update/delete.

When the page is loaded, you'll want to store the current last update date for the table. Depending on how your page is setup, you could store it in ViewState or as a hidden field on the page. Then you'll need to poll for changes. There are many ways to do this. You could write a simple Page Method that returns the last update date, call it from JavaScript, and compare it to the date you stored in the hidden field. Or you could use an UpdatePanel with a Timer control and compare to the value you stored in ViewState.

Now this approach hits the database more than necessary. If that's a problem, use SqlDependency with SQL 2005 or greater. Create a SqlDependency and insert the current date into the Cache with this dependency object. Poll this cache item from the page as described above. When the dependency changes (OnChange event), update the cache item date again.