Why isn't the Cache invalidated after table update using the SqlCacheDependency?

Question

I have been trying to get SqlCacheDependency working. I think I have everything set up correctly, but when I update the table, the item in the Cache isn't invalidated.

Can you look at my code and see if I am missing anything?

I enabled the Service Broker for the Sandbox database. I have placed the following code in the Global.asax file. I also restart IIS to make sure it is called.

void Application_Start(object sender, EventArgs e) {
   SqlDependency.Start(ConfigurationManager.ConnectionStrings["SandboxConnectionString"].ConnectionString);
}

I have placed this entry in the web.config file:

<system.web>
    <caching>
        <sqlCacheDependency enabled="true" pollTime="10000">
            <databases>
                <add name="Sandbox" connectionStringName="SandboxConnectionString"/>
            </databases>
        </sqlCacheDependency>
    </caching>
</system.web>

I call this code to put the item into the cache:

protected void CacheDataSetButton_Click(object sender, EventArgs e) {
    using (SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SandboxConnectionString"].ConnectionString)) {
        using (SqlCommand sqlCommand = new SqlCommand("SELECT PetID, Name, Breed, Age, Sex, Fixed, Microchipped FROM dbo.Pets", sqlConnection)) {
            using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand)) {
                DataSet petsDataSet = new DataSet();
                sqlDataAdapter.Fill(petsDataSet, "Pets");

                SqlCacheDependency petsSqlCacheDependency = new SqlCacheDependency(sqlCommand);
                Cache.Insert("Pets", petsDataSet, petsSqlCacheDependency, DateTime.Now.AddSeconds(10), Cache.NoSlidingExpiration);
            }           
        }
    }
}

Then I bind the GridView with this code:

protected void BindGridViewButton_Click(object sender, EventArgs e) {
    if (Cache["Pets"] != null) {
        GridView1.DataSource = Cache["Pets"] as DataSet;
        GridView1.DataBind();
    }
}

Between attempts to DataBind the GridView, I change the table's values expecting it to invalidate the Cache["Pets"] item, but it seems to stay in the Cache indefinitely.

Answer 1

You must attach the SqlCacheDependency to the SqlCommand before you execute the command:

using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand)) {
    DataSet petsDataSet = new DataSet();
    SqlCacheDependency petsSqlCacheDependency = 
        new SqlCacheDependency(sqlCommand);
    sqlDataAdapter.Fill(petsDataSet, "Pets");
    Cache.Insert("Pets", petsDataSet, petsSqlCacheDependency,
        DateTime.Now.AddSeconds(10), Cache.NoSlidingExpiration);
}