SqlCacheDependency

following Steps those are required are given below:

Prepare the database:
In order to use the SqlCacheDependency class, the database has to be configured to use the service broker (notification services).
First check if the service broker is enabled:

SELECT is_broker_enabled FROM sys.databases WHERE name = 'Northwind'

When this query returns 1 the broker is enabled and we are done with SQL Server.
If not, enable the broker by using this alter table statement:

ALTER DATABASE Northwind SET ENABLE_BROKER

Two problems I have encountered using this statement:
1. It keeps running.
2. It returns error 9772: The Service Broker in database 'Northwind' cannot be enabled because there is already an enabled Service Broker with the same ID.

This problems can be solved by using resp.:
1. Change the statement to ALTER DATABASE Northwind SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE
2. First execute statement ALTER DATABASE Northwind SET NEW_BROKER and then again try to enable the broker.

Prepare the web application:
In the Global.asax file add the following statement to the Application_Start subroutine:
System.Data.SqlClient.SqlDependency.Start("Data Source=localhost;Initial Catalog=Northwind;Integrated Security=True")
And allmost the same statement in Application_Stop:
System.Data.SqlClient.SqlDependency.Stop("Data Source=localhost;Initial Catalog=Northwind;Integrated Security=True")

Using the Cache:
1) in Global.asax file under Application_Start method
SqlDependency.Start(ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString);
2) in Global.asax file under Application_Stop method
SqlDependency.Stop(ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString);
3)Code

DataSet objDS = new DataSet();

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString);
SqlCommand cmd = new SqlCommand("select top 100 ID,CRN_ID from C1", con);
cmd.Notification = null;
cmd.NotificationAutoEnlist = true;
SqlCacheDependencyAdmin.EnableNotifications(ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString);
if(!SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString).Contains("C1"))
{ SqlCacheDependencyAdmin.EnableTableForNotifications(ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString, "C1");
}

SqlCacheDependency dependency = new SqlCacheDependency(cmd);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(objDS);

_cache.Insert("C1", objDS, dependency);


for more information please visit:
http://www.dotnetcurry.com/ShowArticle.aspx?ID=263&AspxAutoDetectCookieSupport=1