CODEWITHSUNDEEP
c#mysqlsqlasp.net
Mr. C
Mr. C

Reputation: 1710

Will cached content update when database entries are added?

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?

Upvotes: 1

Views: 2515

Answers (1)

Jonathon Chase
Jonathon Chase

Reputation: 9704

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.

Upvotes: 1

Related Questions