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.
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.
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)
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:
public static void StartSqlPollingBackgroundWorker()
var worker = new BackgroundWorker();
worker.DoWork += new DoWorkEventHandler(SqlPollingWork);
worker.WorkerReportsProgress = false;
worker.WorkerSupportsCancellation = true;
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 class, I added the following code for stopping the background worker:
public static void StopSqlPollingBackgroundWorker()
var SqlPoller = HttpContext.Current.Application["SqlPollingBackgroundWorker"];
if (SqlPoller != null)
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)
HttpContext.Current = (HttpContext)e.Argument;
var settings = Properties.Settings.Default;
var cacheMins = settings.CacheRefreshIntervalMinutes;
System.Threading.Thread.Sleep(cacheMins * 60 * 1000);
catch (Exception ex)
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,
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_system_update LastSysUpdate ??????
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.
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.