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.
