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<PensionFund> GetPensionFunds(out bool changed)
  {
    changed = false;
    string cacheName = "PensionFund";
    List<PensionFund> cacheItem = 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<PensionFund> GetPensionFunds_FromDataSource()
  {
    List<PensionFund> pensionFunds = 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>

OK, My fault. Sorry for bothering y'all. I noticed that although the
content of data in the PensionFund table is NOT altered it is actually
changed each time because the PensionFund table is either INSERTed
into or UPDATEd. Hence the SQLCacheDependency is rightly flagging a
change. Ideally I need to record whether the PensionFund table data
changes so that, then and only then will I need to UPDATE it.

On 7 Aug, 15:49, mark4asp  wrote:

> Help: SQLCacheDependency not working.