SELECT name, is_broker_enabled FROM sys.databases
2) To enable the Service Broker on your database
ALTER DATABASE Pubs SET ENABLE_BROKER
GO
3) Inform SQL Server that the user running IIS has permission to subscribe to query notificatoins.
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO "IIS_SERVER\ASPNET"
4) Create a connectionStrings in the web.config
<connectionstrings>
<add name="myDbConnectionString" connectionstring="Data Source=localhost;Initial Catalog=MyDB;Integrated Security=True" providername="System.Data.SqlClient">
</connectionstrings>
5) Enable the website to be able to receive notifications.
protected void Application_Start(object sender, EventArgs e)
{
SqlDependency.Start(ConfigurationManager.ConnectionStrings["myDbConnectionString"].ConnectionString);
}
The following code also stops the listener:
protected void Application_End(object sender, EventArgs e)
{
SqlDependency.Stop(ConfigurationManager.ConnectionStrings["myDbConnectionString"].ConnectionString);
}
6) Sets up the cach dependency. Example code.
string tableName = query.Context.Mapping.GetTable(typeof(T)).TableName;
List
if (result == null)
{
using (SqlConnection cn = new SqlConnection(query.Context.Connection.ConnectionString))
{
cn.Open();
SqlCommand cmd = new SqlCommand(query.Context.GetCommand(query).CommandText, cn);
cmd.Notification = null;
cmd.NotificationAutoEnlist = true;
SqlCacheDependencyAdmin.EnableNotifications(query.Context.Connection.ConnectionString);
if (!SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(query.Context.Connection.ConnectionString).Contains(tableName))
{
SqlCacheDependencyAdmin.EnableTableForNotifications(query.Context.Connection.ConnectionString, tableName);
}
SqlCacheDependency dependency = new SqlCacheDependency(cmd);
cmd.ExecuteNonQuery();
result = query.ToList();
HttpContext.Current.Cache.Insert(tableName, result, dependency);
}
}
