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 is not rocket science, there are a few extra moving parts that need to be set-up in your web.config and on SQL Server 2000. When using SQL Server 2005, all of that goes away :)

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:

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;
   }
}