Click here to Skip to main content
Click here to Skip to main content

Static Data Helper (ASP.NET 2.0)

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

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
        {
            // 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;
        listControl.DataBind();

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

  • 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.

License

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

Share

About the Author

Declan Bright
Architect
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 Pinmemberkvsg_yang10-Nov-09 17:23 
GeneralExcellent! But question... Pinmemberclutchhitter17-Apr-08 7:55 
GeneralRe: Excellent! But question... PinmemberDeclan Bright20-Apr-08 22:26 
General[Message Deleted] Pinmemberpune10-Mar-08 4:05 
GeneralGood work thanks PinmemberAHMETKU30-Jan-07 9:49 
GeneralRe: Good work thanks PinmemberDeclan Bright31-Jan-07 8:23 
GeneralUsing Access PinmemberMember #373804518-Jan-07 3:23 
GeneralRe: Using Access PinmemberDeclan Bright18-Jan-07 7:23 
GeneralThank you PinmemberDan Clem20-Dec-06 11:11 
GeneralRe: Thank you PinmemberDeclan Bright20-Dec-06 12:47 
GeneralNice work Pinmemberalvinzc2-Nov-06 14:38 
GeneralRe: Nice work PinmemberDeclan Bright3-Nov-06 1:51 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141216.1 | Last Updated 2 Nov 2006
Article Copyright 2006 by Declan Bright
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid