Why isn't the Cache invalidated after table update using the SqlCacheDependency?
- by Jason
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.
