SQLCacheDependency not working - Cache item is always null

Discussion in 'ASP .Net' started by mark4asp, Aug 7, 2007.

  1. mark4asp

    mark4asp Guest

    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>
     
    mark4asp, Aug 7, 2007
    #1
    1. Advertising

  2. mark4asp

    mark4asp Guest

    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.
     
    mark4asp, Aug 7, 2007
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Joel Barsotti

    Can't get SqlCacheDependency working correctly

    Joel Barsotti, Feb 7, 2006, in forum: ASP .Net
    Replies:
    7
    Views:
    3,733
    dhaval42
    Dec 21, 2009
  2. Replies:
    5
    Views:
    26,710
    Mike Schilling
    Mar 29, 2006
  3. Jayender
    Replies:
    0
    Views:
    876
    Jayender
    Aug 30, 2006
  4. Replies:
    2
    Views:
    539
  5. Anton
    Replies:
    0
    Views:
    518
    Anton
    Mar 12, 2007
Loading...

Share This Page