Help: SQLCacheDependency not working. When I step through my code
with the debugger I notice that the condition below:
(cacheItem == null)
is always true.
I have setup SQLCacheDependency to work with 11 tables in the
database. When I update the database without actually changing the
"PensionFund" table then I notice that the Cache is being invalidated
anyway. This is not the behavior I want. I only want the Cache to be
invalidated when I alter the PensionFund table.
data_list class:
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Caching;
using System.Web.Security;
using System.Data.SqlClient;
using System.Text;
using System.Collections.Generic;
public class data_list
{
public static List<PensionFundGetPensionFunds(out bool changed)
{
changed = false;
string cacheName = "PensionFund";
List<PensionFundcacheItem = HttpRuntime.Cache[cacheName] as
List<PensionFund>;
if (cacheItem == null)
{
changed = true;
cacheItem = GetPensionFunds_FromDataSource();
SqlCacheDependency dep = new SqlCacheDependency("Admin",
cacheName);
HttpRuntime.Cache.Insert(cacheName, cacheItem, dep);
}
return cacheItem;
}
private static List<PensionFundGetPensionFunds_FromDataSource()
{
List<PensionFundpensionFunds = new List<PensionFund>();
using (SqlConnection dConn = new
SqlConnection(data_connection.SqlConnectionString) )
{
using (SqlCommand cmd = new
SqlCommand("PensionFund_Get_All_For_Listing", dConn))
{
cmd.CommandType = CommandType.StoredProcedure;
try
{
dConn.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
PensionFund pensionFund = new PensionFund
(
(int)reader["PensionFundID"],
reader["FundName"].ToString()
);
pensionFunds.Add(pensionFund);
}
}
}
catch (Exception e)
{ pensionFunds = null; }
}
}
return pensionFunds;
}
}
web.config entries:
<connectionStrings>
<add name="myDBConnString_localdev" connectionString="Data Source=.
\SQLEXPRESS;Integrated Security=True;Initial Catalog=myDB_40;Min Pool
Size=5;" providerName="System.Data.SqlClient"/>
</connectionStrings>
<system.web>
<caching>
<sqlCacheDependency enabled="true" pollTime="1000">
<databases>
<add name="Admin"
connectionStringName="myDBConnString_localdev" pollTime="1000" />
</databases>
</sqlCacheDependency>
</caching>
</system.web>
