Use .NET's BackgroundWorker to Keep ASP.NET Cache in Sync with a SQL Server Database

Introduction

Storing frequently accessed database data, like lookup lists, in ASP.NET’s cache is a great way to optimize a web application’s performance. But anytime you store database data in ASP.NET’s cache, you have to provide a way to update that cache. This ensures changes made in the database make it into the web application’s cache. This is handled using either notifications or polling.

Notifications are possible when using SQL Server 2005 with notification services and .NET’s SqlDependency object from the System.Data.SqlClient namespace. But notification services aren’t available in SQL Server 2008 or other databases. Even if you are using SQL Server 2005, employing this technology requires more server configuration and therefore complicates deployment.

Polling is often handled by setting an expiration of the cached item at some time interval such as hourly. That way the cache holds the data for an hour and then automatically dumps it. Then when the data is requested and the cache is empty, the data is read from the database and placed back into the cache. This approach works fairly well, but with two drawbacks. First, it requires the cache be refreshed even when it is not needed. Second, it runs on the same thread as the request so some unlucky user has to wait a little longer for their request to return while the data is read from the database and stored in the cache.

The BackgroundWorker object in .NET enables a better solution. When the web application starts, it spins off a background thread, and passes to it the application’s HttpContext object, enabling the thread, access to the application’s cache. When the web application ends, it stops the background thread. The background thread runs a continuous loop of sleeping for some time interval, and then polls the database to see if data has changed during that interval. If data has changed, the cache is refreshed. This solution is highly efficient because it runs on a background thread and refreshes the cache only when needed.

The Code

I prefer to approach things from the top down. So, I’ll start by setting up the web application, so when it starts up, it calls a routine to start the background worker. Here’s the code from Global.asax.cs:

protected void Application_Start(object sender, EventArgs e)
{
    SiteUtil.StartSqlPollingBackgroundWorker();
}

Next, I’ll add the code for starting the BackgroundWorker to a class I’ve named SiteUtil. I’ve added comments here to help make the code self-explanatory:

/// <summary>
/// This routine creates a background worker and starts it working on another thread to 
/// periodically poll SQL server and refresh this applications cached data as needed.
/// </summary>
public static void StartSqlPollingBackgroundWorker()
{
    var worker = new BackgroundWorker();
    worker.DoWork += new DoWorkEventHandler(SqlPollingWork);
    worker.WorkerReportsProgress = false;
    worker.WorkerSupportsCancellation = true; 
    worker.RunWorkerCompleted += 
           new RunWorkerCompletedEventHandler(SqlPollingWorkCompleted);
    worker.RunWorkerAsync(HttpContext.Current); //Pass HttpContext to background worker

    //Add this BackgroundWorker object instance to the application variables 
    //so it can be cleared when the Application_End event fires.
    HttpContext.Current.Application.GetVariables().SqlPollingBackgroundWorker = worker;
    HttpContext.Current.Application["SqlPollingBackgroundWorker"] = worker;
}

Next, we’ll add code to Global.asax.cs to stop the background worker when the web application stops.

protected void Application_End(object sender, EventArgs e)
{
    SiteUtil.StopSqlPollingBackgroundWorker();
}

In the SiteUtil class, I added the following code for stopping the background worker:

public static void StopSqlPollingBackgroundWorker()
{
    //If background worker process is running then clean up that object.
    var SqlPoller = HttpContext.Current.Application["SqlPollingBackgroundWorker"];
    if (SqlPoller != null)
        SqlPoller.CancelAsync();
}

At this point, we’ve written code to start and stop the BackgroundWorker when the web application starts and stops. Now, we have to define what the worker should do while working and when it completes. In the StartSqlPollingBackgroundWorker routine above, we setup the BackgroundWorker to fire the SqlPollingWork routine when the thread starts, and the SqlPollingWorkCompleted routine when the work is completed. The following routine defines what the BackgroundWorker does while it’s running:

private static void SqlPollingWork(object sender, DoWorkEventArgs e)
{
    //Get current HttpContext from the DoWorkEventArgs object
    HttpContext.Current = (HttpContext)e.Argument;

    //Run a constant loop that polls SQL server at an interval dictated 
    //by the CacheRefreshIntervalMinutes application setting. This loop 
    //will run as long as the web application is running. When the web
    //application stops, this process will be stopped.
    while (true)
    {
        //Sleep for some period of time
        var settings = Properties.Settings.Default;
        var cacheMins = settings.CacheRefreshIntervalMinutes;
        System.Threading.Thread.Sleep(cacheMins * 60 * 1000);

        try
        {
            //TODO: Check the database for changes and if they
            //exist refresh the data stored in the cache.
            DataLayer. PollDatabaseForCacheUpdates();
        }
        catch (Exception ex)
        {
            //TODO: Log this exception here because it 
            //will not be trapped in Application_Error
        }
    } 
}

Note the first line of code in the SqlPollingWork routine. This is the magic of this code. By setting the context on the new thread to the context supplied by the web application, everything called by that thread can function just as it would when running on the main thread of the web application. In other words, the background thread can access the Application object of the web application’s thread.

The last bit of the BackgroundWorker code we need to define is the SqlPollingWorkCompleted routine that gets called when the thread completes. Even though our thread never completes until we stop it, we are required to supply this routine. Here’s the code:

private static void SqlPollingWorkCompleted(object sender, 
               RunWorkerCompletedEventArgs e)
{
    //Do nothing
}

At this point, we have created a web application that spins off a new background thread when it starts, and stops that background thread when the web application stops. The background thread runs a continuous cycle of sleeping for a time, and polls a database for changes. The final step is to write code to determine if changes exist in the database and refresh the cache accordingly. This could be accomplished in many different ways, but SQL Server offers a slick mechanism for telling when data last changed in any given table.

SELECT object_name(object_id) TableName, 
    last_user_update LastUpdate, 
    last_system_update LastSysUpdate ??????
FROM sys.dm_db_index_usage_stats
WHERE db_name(database_id) = @DatabaseName
AND object_name(object_id) NOT LIKE 'sys%'
AND last_user_update > @DatetimeDataWasCached

To run this query, the account you are using to access SQL Server must have the “View server state” permission. You can use this query to determine when data was last updated in various tables in the database, and then refresh the cache accordingly.