Binding a CheckBoxList object to an ordered Sharepoint list of reference values in a web part (c#)

It's common when creating database applications to use reference tables to provide lookup values.  These tables ofter contain a flag that is used to mark a record as no longer available, and an order column that provides a method of putting the values in an order of importantance, perhaps when using them in a drop down list or list of checkboxes.  Sharepoint is NOT a database, however there is no reason why good practice such as this cannot be applied to Sharepoint's list model.

In the following example I will show you how to set up a reference list and then use the Sharepoint object model to programmatically access this list and bind it to an control (in this case a Sharepoint CheckBoxList control).  Once you understand this, you can use the same method to connect to any reference list and bind it to other objects and controls, such as drop down lists. This example has been developed for Sharepoint 2007, but should also work for Sharepoint 2010.








Step 1:  Set up a reference list
Create a new list on your Sharepoint site with teh following structure: 

• ID - integer (this is a default field) - this will be used as the unique key (lookup value) used to link this reference filed to other lists
• Title - string (this is a default field) - this is used to hold the description of the reference field, and will be the value shown in lookups
• SortOrder - integer, default 1000 - this can be used to sort the list values in lookups and programmatically using CAML
• Enabled - Yes/No field, default Yes - setting to NO will enable you to filter out these values in views and in programmatic lookups in CAML

You can now enter you reference values into the list, for example here is a list of types of medical record:

Step 2:  Add method to webpart that use CAML to read and order the list
Now for the code that reads the Sharepoint list in a structured manner so we can use it in web parts.  First, add the following method to your web part:

/// <summary>
/// Returns a list collection, ordered using a field in the list called "SortOrder"
/// </summary>
/// <param name="listName">The literal string name of the list to query</param>
/// <returns>SPListItemCollection</returns>
private SPListItemCollection ReadOrderedList(string listName)
{
    try
    {
        using (SPWeb configWeb = SPContext.Current.Site.OpenWeb())
        {
            //Get the list
            SPList lookupList = configWeb.Lists[listName];
            // Use CAML to order the list
            SPQuery query = new SPQuery();
            // This CAML where clause searches for only items where the Enabled field is set to "Yes"
            query.Query = "<Where><Eq><FieldRef Name=\"Enabled\" /><Value Type=\"Integer\">1</Value></Eq></Where> ";
            // This CAML order by claus uses an integer field of "SortOrder"
            query.Query += "<OrderBy><FieldRef Name=\"SortOrder\" /></OrderBy>";
            SPListItemCollection lookupItems = lookupList.GetItems(query);
            return lookupItems;
        }
    }
    catch (Exception ex)
    {
        ErrorTrap("ReadOrderedList: " + ex.Message + Environment.NewLine + ex.StackTrace.ToString());
        throw;
    }
} 

This method will return the content of the list in a SPListItemCollection object. CAML is use to order the list by the "SortOrder" column, and to ignore values that do not have the boolean field "Enabled" set to true.

Step 3:  Bind the list to a control
At the class level of your webpart, create the control that you wish to bind to (in this case a CheckBoxList):

CheckBoxList myCheckList = new CheckBoxList();

To bind this control to your Sharpoint list, put this code in the CreateChildControls() method. Simply replace "My List" with the real name of your list:

// Set up the Record Section checkboxes list
    SPListItemCollection dt = ReadOrderedList("My List");
    myCheckList.DataSource = dt;
    myCheckList.DataTextField = "Title";
    myCheckList.DataValueField = "ID";
    myCheckList.DataBind();

That's it! You now have a Sharepoint object bound to your Sharepoint list of reference values.  When rendered, the output will look something like this:

Yyou can use the following method to render the CheckBoxList to the web part.  This example puts the CheckBoxList in a DIV that fixes the maximum size of the list with a scrollbar.

/// <summary>
/// render a list of checkboxes  to the page
/// </summary>
void RenderCheckBoxes()
{
    try
    {
        // Set up list of checkboxes
        myCheckList.EnableViewState = true;
        myCheckList.CellSpacing = 0;
        myCheckList.CellPadding = 0;

        for (int i = 0; i < myCheckList.Items.Count; i++)
        {
            ListItem li = new ListItem(myCheckList.Items[i].Text, myCheckList.Items[i].Value);
           
            // Set all boxes to selected if not a postback
            if (!Page.IsPostBack)
            {
                myCheckList.Items[i].Selected = true;
            }

        }
        // Give the checklist an ID (can use this in javascript etc)
        myCheckList.ID = "RecordSections";

        //Add controls to web part
        Controls.Add(new LiteralControl(@"<TABLE border='0' bordercolor='red' cellpadding='0' cellspacing='0' valign='top'>"));
        Controls.Add(new LiteralControl("<TR>"));
        Controls.Add(new LiteralControl("<TD>"));
        Controls.Add(new LiteralControl(@"<div class=""ms-frombody""><b>Record Sections</b></div>"));
        Controls.Add(new LiteralControl("</TD>"));
        Controls.Add(new LiteralControl("</TR>"));
        Controls.Add(new LiteralControl("<TR>"));

        Controls.Add(new LiteralControl("<TD>\r\n"));
        Controls.Add(new LiteralControl(@"<input type=""hidden"" id=""ScrollPos"" runat=""server""/>"));

        Controls.Add(new LiteralControl(@"<div id='RecordSectionScroll' style=""OVERFLOW: auto; WIDTH: 247px;  TOP: 0px; HEIGHT: 300px"" onscroll=""javascript:document.getElementById('ScrollPos').value = this.scrollTop""> "));

        Controls.Add(myCheckList);
        Controls.Add(new LiteralControl("</div></TD>"));
        Controls.Add(new LiteralControl("</TR>"));     
        Controls.Add(new LiteralControl("</TABLE>"));
    }
    catch (Exception ex)
    {
        ErrorTrap("RenderCheckBoxes" + ex.Message + Environment.NewLine + ex.StackTrace.ToString());
    }
}

Note that I am using literal controls to render the check boxes in a HTML table. This is pretty horrible and I don't recommend it, but it does work.

Here is the ErrorTrap method. Note that this is just a simple error handling function and is not required to render a checklist bound to a Sharepoint list, but since it appears in the examples I though I would show you what I do.

/// <summary>
/// Give an error message
/// </summary>
/// <param name="myMessage"></param>
void ErrorTrap(string myMessage)
{
    Controls.Add(new LiteralControl("<strong>ERROR: " + myMessage.ToString() + "<BR/></strong>"));
    ErrorToFile(myMessage);          
}

/// <summary>
/// Write errors to the log file
/// </summary>
/// <param name="myMessage"></param>
void ErrorToFile(string myMessage)
{
    using (StreamWriter sw = new StreamWriter("D:\\SearchErrorLog.txt"))
    {
        DateTime now = new DateTime();
        sw.Write(now.ToString() + ": " + myMessage.ToString());
    }
}