Will cached content update when database entries are added?

Question

Ok so! I'm still experimenting with Caching and its great so far! Quick question regarding database updates.

TLDR: When do database updates propigate to the cache? If no Cache expiration time is set, does it clear when users exit and re-enter the application? Or is HTTPContext.Current.Cache a global cache (ie, only clears when all users have exited the application [if that ever occurs])?

Say I have a table:

CREATE TABLE #DaysOfTheWeek(
     DayKey INT
    ,DayDescription VARCHAR(20)
);

INSERT INTO #DaysOfTheWeek VALUES(1, 'Monday');
INSERT INTO #DaysOfTheWeek VALUES(2, 'Tuesday');
INSERT INTO #DaysOfTheWeek VALUES(3, 'Wednesday');
INSERT INTO #DaysOfTheWeek VALUES(4, 'Thursday');
INSERT INTO #DaysOfTheWeek VALUES(5, 'Friday');

I add these to a drop down list in my ASP.NET web application (.NET 4.5).

I AM AWARE THIS IS VERY BAD CODE. DO NOT COPY AND PASTE THIS. IT IS PRONE TO SQL INJECTION. THIS SHOULD BE IN A STORED PROCEDURE. I AM A BAD PERSON.

//On page load.

private void PopulateDaysOfWeekDropDownList()
{
    var days = new List<ListItem>();

    if (HttpContext.Current.Cache["DaysOfTheWeek"] == null)
    {
        //Go to the database and get all days.
        var dt = GetDaysOfTheWeek();
        foreach(DataRow row in dt.Rows)
        {
            var day = new ListItem();
            day.Key = row["DayKey"].ToString(); //Key may not be correct, I'm not using a compiler here.
            day.Description = row["DayDescription"].ToString();
            days.add(day);
        }
        HttpContext.Current.Cache["DaysOfTheWeek"] = days;
    }
    else
    {
        //Retrieve the value from the web cache.
        days = (List<ListItem>)HttpContext.Current.Cache["DaysOfTheWeek"];
    }

    //Bind the results to the web source.
    ddlDaysOfWeek.DataSource = days;
    ddlDaysOfWeek.DataBind();
}


public DataTable GetDaysOfTheWeek()
{   
    using (var cnn = new SqlConnection("myConnectionString"))
    {
        using (var dt = new DataTable())
        {
            var command = "SELECT DayKey, DayDescription FROM #DaysOfTheWeek WITH(NOLOCK)"
            using (var cmd = new SqlCommand(command, cnn) { CommandType = CommandType.Text})
            {
                using (var adapter = new SqlDataAdapter(cmd))
                {
                    adapter.Fill(dt);
                }               

                return dt;
            }
        }
    }
}

All is well and good. This is running on my systems for a few days, and the web cache has the five days.

Let's say later I add weekends too:

INSERT INTO #DaysOfTheWeek VALUES(0, 'Sunday');
INSERT INTO #DaysOfTheWeek VALUES(6, 'Saturday');

Now the database will contain all seven days of the week. Will the next page-load event pick up the new days? Or will it take the cached copy and still only show the original five?

When (if ever) will the cached copy expire and re-fetch all seven days?

From a system admin point of view, is there anything we could do to manually re-fresh the server cache?

score 1 · Accepted Answer · answered Nov 03 '15 at 21:57

1

You can use a SqlCacheDependency. Instead of assigning the item to the cache directly, like you are in the following code:

HttpContext.Current.Cache["DaysOfTheWeek"] = days;

You would want to use a Cache.Insert with the added dependency. You can also aggregate dependencies, in case the cache depends on multiple tables.

var cacheItem = days;
var tableName = "someTable";
var databaseName = "someDb";
var cacheKey = "someCacheKey";
var aggDependency = new System.Web.Caching.AggregateCacheDependency();
aggDependency.Add(new System.Web.Caching.SqlCacheDependency(databaseName, tableName));
HttpContext.Current.Cache.Insert(cacheKey, cacheItem, aggDependency);

When the tables in the cache dependency change the cache will be invalidated and removed.

answered Nov 03 '15 at 21:57

Jonathon Chase

9,396
21
39

Haven't heard of AggregateCacheDependency before. [This thread](https://stackoverflow.com/questions/14203704/using-sqldependency-vs-periodic-polling-of-a-table-performance-impact) seems to suggest that it polls your database for data changes. Will this impact DB performance? – Mr. C Nov 03 '15 at 22:09
1

My general understanding is that using a SqlDependency is going to impact the performance more if changes are frequent, while a polling method will be better if changes are infrequent. As with most things performance, it'll come down to measuring the impact of either system against your usage needs. – Jonathon Chase Nov 03 '15 at 22:14
However, to answer your question directly, the cache won't update on a database update unless you use a SqlDependency or another method (such as polling to see if the table changed) to remove the cache-item when you consider it's data to no longer be valid. – Jonathon Chase Nov 03 '15 at 22:16

Will cached content update when database entries are added?

1 Answers1