Introduction
Here is a simple helper class designed to manage static data in an ASP.NET application. The helper class will retrieve multiple tables of static data from a SQL Server database, store the data in a DataSet, and cache it at application startup or upon first request. Fetching the data from the cache and binding it to a ListControl (DropDownList / ListBox / RadioButtonList / CheckBoxList) is achieved with one line of code.
Background
If you have ever developed a web application with many DropDownList controls, then you can find yourself repeating a lot of code. Code must be written to fetch the lookup data from a database, cache the data, and bind it to the DropDownList controls, possibly on several pages.
Setting up the Database
The demo was developed with SQL Server 2005 Express Edition although the same concept will work with SQL Server 2000. In the demo is a simple database with three tables to hold lookup values for Categories, Status, and Types. There is a stored procedure: proc_StaticDataSelect, to retrieve the data from the three tables in multiple resultsets.
CREATE PROCEDURE dbo.proc_StaticDataSelect
AS
SET NOCOUNT ON;
SELECT StatusId, Status FROM [Statuses]
SELECT TypeId, Type FROM [Types]
SELECT CategoryId, Category, Description FROM [Categories]
Using the code
In the sample application there is a class, StaticData.cs in the App_Code folder. Near the top of the class, there is an enum called Tables which contains a value for each resultset, and in the same order as they are returned by the stored procedure.
public enum Tables
{
Statuses = 0,
Types,
Categories
}
The public method Bind retrieves the static data from the cache and binds it to a ListControl (DropDownList / ListBox / RadioButtonList / CheckBoxList). It takes a ListControl and a table index as parameters, and there is an overload to allow the addition of a header row to the ListControl.
To bind static data to a DropDownList called TypeFilter:
<asp:DropDownList ID="TypeFilter" runat="server"></asp:DropDownList>
StaticData.Bind(this.TypeFilter, StaticData.Tables.Types, "All Types");
The Tables enum is used to indicate the index of the table within the DataSet. This prevents using a number to indicate a discrete value.
The public method CacheStaticData fetches static data from the database and caches it in a DataSet. This method can be called in the Application_Start event in the Global.asax. If it is not called at application startup; it will be called upon the first call to the Bind method.
void Application_Start(object sender, EventArgs e)
{
StaticData.CacheStaticData();
}
To add data for another ListControl, you simply add a SELECT statement to the proc_StaticDataSelect stored procedure, and add a table name at the same index to the Tables enum. Then you are ready to bind the data to a ListControl, the data will be retrieved and cached with the rest of the static data.
How it works
In the demo, all code is in the StaticData class although the data access code could be in a separate class or project.
The GetDataSet method makes the connection to the database, and fetches the data via a SqlDataReader. It loads the multiple resultsets into one DataSet using the Load method of a DataSet. (Depending on your configuration, you may need to modify the connection string in the web.config.)
private static DataSet GetDataSet()
{
SqlDataReader dr = null;
DataSet ds = new DataSet();
try
{
SqlConnection conn = new SqlConnection(
ConfigurationManager.ConnectionStrings[
"StaticDataHelperConnectionString"].ToString());
SqlCommand cmd = new SqlCommand("proc_StaticDataSelect", conn);
conn.Open();
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
ds.Load(dr, LoadOption.OverwriteChanges,
GetDataTablesValuesArray());
return ds;
}
finally
{
if (dr != null && !dr.IsClosed)
{
dr.Close();
}
}
}
The third parameter of the Load method requires a string array of table names. The GetDataTablesValuesArray method generates a string array of table names from the Tables enum.
private static string[] GetDataTablesValuesArray()
{
string[] s = new string[0];
foreach (Tables value in Enum.GetValues(typeof(Tables)))
{
Array.Resize(ref s, s.Length + 1);
s.SetValue(value.ToString(), s.Length - 1);
}
return s;
}
The private property StaticDataSet and the public method CacheStaticData manage the caching of the DataSet.
private static DataSet StaticDataSet
{
get
{
Cache oCache = System.Web.HttpContext.Current.Cache;
DataSet ds = (DataSet)oCache[StaticDataSetCache];
if (ds == null)
{
ds = CacheStaticData();
}
return ds;
}
}
public static DataSet CacheStaticData()
{
Cache oCache = System.Web.HttpContext.Current.Cache;
DataSet ds = GetDataSet();
oCache.Insert(StaticDataSetCache, ds);
return ds;
}
The public method Bind is used to retrieve the static data from the Cache and bind it to a ListControl.
public static void Bind(ListControl listControl, Tables tableIndex)
{
Bind(listControl, tableIndex, "");
}
public static void Bind(ListControl listControl,
Tables tableIndex, string header)
{
DataTable dt = GetData(tableIndex);
listControl.DataValueField = dt.Columns[0].ToString();
listControl.DataTextField = dt.Columns[1].ToString();
listControl.DataSource = dt;
listControl.DataBind();
if (header.Length > 0)
{
listControl.Items.Insert(0, new ListItem("- " +
header + " -", "0"));
}
}
This method retrieves a DataTable from the cached DataSet at a specified table index. It sets the DataValueField and DataTextField properties of the ListControl based on the column names of the DataTable and then binds the DataTable to the ListControl.
If a header string parameter is passed, it is inserted as the top item.
Usage Limitations
This helper class is designed to manage static data, therefore the data is cached for the life time of the application. If you have functionality which allows an admin user to update the lookup tables, you can call the StaticData.CacheStaticData(); method when an update takes place to refresh the cached data.
For data which will change with any sort of regularity, you can set an expiration using the Cache.Insert overload.
oCache.Insert(StaticDataSetCache, ds, null, DateTime.Now.AddMinutes(30),
Cache.NoSlidingExpiration);
You can also setup a cache dependency with SQL Server 2005 but that is beyond the scope of this article.
Points of Interest
The DataSet.Load method can load multiple resultsets into a DataSet from a single stored procedure via a SqlDataReader. Caching this DataSet is a convenient way of storing multiple tables of static data within an ASP.NET application.
History
