Click here to Skip to main content
14,574,641 members

Static Data Helper (ASP.NET 2.0)

Rate this:
4.33 (5 votes)
Please Sign up or sign in to vote.
4.33 (5 votes)
2 Nov 2006CPOL
A helper class to manage static data for dropdown lists with caching.


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.


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
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,

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)

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();
        SqlConnection conn = new SqlConnection(
        SqlCommand cmd = new SqlCommand("proc_StaticDataSelect", conn);

        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

        ds.Load(dr, LoadOption.OverwriteChanges,
        return ds;
        if (dr != null && !dr.IsClosed)

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 DataSet from Cache
        Cache oCache = System.Web.HttpContext.Current.Cache;
        DataSet ds = (DataSet)oCache[StaticDataSetCache];

        // If DataSet is not in Cache then generate it and cache it
        if (ds == null)
            ds = CacheStaticData();

        return ds;

public static DataSet CacheStaticData()
    Cache oCache = System.Web.HttpContext.Current.Cache;

    // Get Static DataSet
    DataSet ds = GetDataSet();
    // Insert into cache
    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)
    // Get the DataTable from the cached DataSet
    DataTable dt = GetData(tableIndex);

    // Set the DataValueField and DataTextField of the LsitControl
    listControl.DataValueField = dt.Columns[0].ToString();
    listControl.DataTextField = dt.Columns[1].ToString();

    // Bind the data to the LsitControl
    listControl.DataSource = dt;

    // Add the header row if required
    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), 

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.


  • v1.0 - 21st October 2006
  • v1.1 - 25th October 2006

    Addition of RadioButtonList and CheckBoxList controls to the demo.

  • v1.2 - 2nd November 2006
    Some rewording and formatting.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Declan Bright
Ireland Ireland
I have been designing and developing business solutions for the aviation and telecommunications industries since 1999. My experience covers a wide range of technologies and I have delivered a variety of web and mobile based solutions.

Comments and Discussions

GeneralUseful Pin
kvsg_yang10-Nov-09 16:23
Memberkvsg_yang10-Nov-09 16:23 
GeneralExcellent! But question... Pin
clutchhitter17-Apr-08 6:55
Memberclutchhitter17-Apr-08 6:55 
GeneralRe: Excellent! But question... Pin
Declan Bright20-Apr-08 21:26
MemberDeclan Bright20-Apr-08 21:26 
General[Message Deleted] Pin
pune10-Mar-08 3:05
Memberpune10-Mar-08 3:05 
GeneralGood work thanks Pin
AHMETKU30-Jan-07 8:49
MemberAHMETKU30-Jan-07 8:49 
GeneralRe: Good work thanks Pin
Declan Bright31-Jan-07 7:23
MemberDeclan Bright31-Jan-07 7:23 
GeneralUsing Access Pin
Jon Selby18-Jan-07 2:23
MemberJon Selby18-Jan-07 2:23 
GeneralRe: Using Access Pin
Declan Bright18-Jan-07 6:23
MemberDeclan Bright18-Jan-07 6:23 
GeneralThank you Pin
Dan Clem20-Dec-06 10:11
MemberDan Clem20-Dec-06 10:11 
GeneralRe: Thank you Pin
Declan Bright20-Dec-06 11:47
MemberDeclan Bright20-Dec-06 11:47 
GeneralNice work Pin
alvinzc2-Nov-06 13:38
Memberalvinzc2-Nov-06 13:38 
GeneralRe: Nice work Pin
Declan Bright3-Nov-06 0:51
MemberDeclan Bright3-Nov-06 0:51 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Posted 21 Oct 2006


60 bookmarked