Flexible Database Persisted Application Settings
Have you ever needed to say replace the location of your images in your application from one location to another, or had a database routine that ran nightly and you wanted to gracefully control access to your application during that process? In this article I am going to show how I approached this on one of my projects and have been pretty happy with it so far.
Here's the 10,000 foot view of the solution. Build a SQL table that has 2 varchar(255) columns, one named AppVarName and the other AppVarValue. Build a class that checks to see if the DataTable exists in the cache, if not retrieve the DataTable from SQL and place it in the cache. The class then loops through the rows in the DataTable creating and assigning application variables with the AppVarName field from the table and populates it's value from the AppVarValue field. Place a call to this class in the Application_start, and Application_BeginRequest methods in the Global.asax. Now in all your pages you have access to this information.
I personally use this in my Master page, in the Page_Load method I check the value of "ApplicationStatus" which I've created in my table and assigned a value of "1" to. If the value is "1" I do nothing, if the value is "0" I redirect to a friendly "Down For Maintenance" page. This allows me to set the value in say a reindexing routine and then change it back when it's finished. I also use different status values to enable or disable certain features of my application, this allows me a lot of flexibility.
Now on to the code snippets. For this article I am using the Enterprise Library Data Access Application Block, if you don't use this it's not a big deal and I think you can see how to adapt this to your own data access pattern. I'll attach the code to the post, but here's the "gist" of it...please pardon any skipped steps...refer to the attached solution for the details.
First make a table called ApplicationSettings with 2 varchar(255) columns one named AppVarName and the other named AppVarValue.
Next make a stored procedure named GetApplicationSettings which reads simply like
SELECT AppVarName, AppVarValue From ApplicationSettings
Now create a blank solution. Add a C# Class Library Project, and a C# ASP.NET web site.
Add the AppManager Class to your Library:
using
System;
using
System.Collections.Generic;
using
System.Linq;
using
System.Text;
using
System.Data;
using
System.Web;
using
Microsoft.Practices.EnterpriseLibrary.Data;
using
System.Data.Common;
namespace
Web.AppManager
{
public class ApplicationVariableManager
{
public void UpdateApplicationVars()
{
string cacheKey = "ApplicationVariables";
int cacheDuration = 0;DataTable dtVariables = HttpRuntime.Cache[cacheKey] as DataTable;if (dtVariables == null)
{
Database myDatabase = DatabaseFactory.CreateDatabase("myConnectionString");using (DbCommand dbCommand = myDatabase.GetStoredProcCommand("GetApplicationSettings"))
{
using (DataSet myds = myDatabase.ExecuteDataSet(dbCommand))
{
dtVariables = myds.Tables[0].Copy();
}
}
foreach (DataRow myDataRow in dtVariables.Rows)
{
if (myDataRow.Field<string>("AppVarName") == "SettingsCache")
{
cacheDuration = Convert.ToInt32(myDataRow.Field<string>("AppVarValue"));
}
if (cacheDuration == 0)
{
cacheDuration = 300;
}
}
HttpRuntime.Cache.Insert(cacheKey, dtVariables, null, DateTime.Now.AddSeconds(cacheDuration), TimeSpan.Zero);
}
foreach (DataRow myDataRow in dtVariables.Rows)
{
HttpContext.Current.Application[myDataRow.Field<string>("AppVarName")] = myDataRow.Field<string>("AppVarValue");
}
}
}
}
Add a reference in your web app to the Class Library
Create a Global.asax and add update the Application_Start and Application_BeginRequest methods to look like this:
protected void Application_Start(object sender, EventArgs e)
{
Web.AppManager.ApplicationVariableManager AppManager = new Web.AppManager.ApplicationVariableManager();
AppManager.UpdateApplicationVars();}protected void Application_BeginRequest(object sender, EventArgs e)
{
Web.AppManager.ApplicationVariableManager AppManager = new Web.AppManager.ApplicationVariableManager();AppManager.UpdateApplicationVars();
}
Add a check in your Page_Load of your site master's code behind this check:
if (Convert.ToInt32(Application["ApplicationStatus"])==0)
Response.Redirect("/Down.aspx");
Now make a Down.aspx page and make sure your Default.aspx page is using your site master. Voila. The CacheSettings variable allows you to tinker with how long that variables are cached.
All standard disclaimers apply....works on my machine. Let me know if you have questions or can improve it (I am sure there is room there for improvement). Here's the code.