SqlCacheDependency using ASP.NET 2.0 and SQL Server 2005

SqlCacheDependency using Asp.net 2.0 and sqlserver 2005 is a beautiful thing :) Although getting SqlCacheDependency to work with SQL Server 2000 have to add some additional step.

Now we will go with Asp.net 2.0 with sqlserver 2005

Enable Service Broker

Before SqlCacheDependency will work with SQL Server 2005, you first have to enable Service Broker, which is reponsible for the notification services that let the web cache know a change has been made to the underlying database and that the item in the cache must be removed.
ALTER DATABASE Store SET ENABLE_BROKER;
GO

--SqlCacheDependency.Start() in Global.asax

In ASP.NET, you need to run SqlCacheDependency.Start(connectionString) in the Global.asax:

Example:

void Application_Start(object sender, EventArgs e)
{
string connectionString = WebConfigurationManager.
ConnectionStrings["Catalog"].ConnectionString;
SqlDependency.Start(connectionString);
}

SqlCacheDependency in ASP.NET 2.0 Example
Now you can just create your SqlCacheDependency as normal in your ASP.NET 2.0 page. Here is a simple example:
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
DataTable categories = (DataTable)Cache.Get("Categories");
if (categories == null)
{
categories = GetCategories();
Label1.Text = System.DateTime.Now.ToString();
}
GridView1.DataSource = categories.DefaultView;
GridView1.DataBind();
}
private DataTable GetCategories()
{
string connectionString = WebConfigurationManager.
ConnectionStrings["Catalog"].ConnectionString;
DataTable categories = new DataTable();
using (SqlConnection connection =
new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(
"SELECT CategoryID,Code,Title
FROM dbo.Categories", connection);
SqlCacheDependency dependency =
new SqlCacheDependency(command);
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = command;
DataSet dataset = new DataSet();
adapter.Fill(dataset);
categories = dataset.Tables[0];
Cache.Insert("Categories", categories, dependency);
}
return categories;
}
}