A Multi-Purpose All-In-One Web Control

• Download source code - 569 KB

Introduction

This article describes how to dynamically create a control (of your choice) and populate it with data from a SQL Server database. I am going to assume you have some knowledge in creating ASP.NET web pages (and some SQL), so I won't go into the exact step-by-step details.

Background

I needed a simple control that I could reuse as often as I needed for the purpose of picking parameters which I would use to run reports on web pages. I also needed to be able to easily select the type of control I wanted to display for the purposes of the page I would be using it on. This article demonstrates an effective way to easily create such a control that can be toggled to act as a CheckboxList, RadioButtonList, or ListBox and populates it with predetermined or on demand data from a SQL Server database. Adding additional control types, such as a combo box (I use RadComboBox by Telerik) and adding additional preset queries (I have over a dozen) is easy to accomplish by expanding on the control in a few places. This essentially provides a simple way to avoid having to rebuild controls for every web page (or create numerous custom controls), and consolidates it all into one custom control.

Using the code

I'm going to skip ahead to show you how simple it is to implement the control, then we'll get into building the control itself.

In your ASPX page, you'll want to register the control (which, for this example, I will call sqlPicker):

<%@ Register TagPrefix="myCtrl" TagName="myControl" Src="~/Controls/SQLPicker.ascx" %>

And then, you'll be able to call the control somewhere in your page, as in the following example:

<myCtrl:myControl ID="customer" runat="server" ControlType="RadioList" 
     SqlQuery="CustomerList" />
- AND/OR -
<myCtrl:myControl ID="countries" runat="server" ControlType="CheckList" 
     SqlQuery="CountryList" Delimiter="," />
- AND/OR -
<myCtrl:myControl ID="customQuery" runat="server" ControlType="ListBox" 
     SelectCommand="select user, id from userlist" DataTextField="user" 
     DataValueField="id" delimiter=";" />

And, in your code-behind, you can get the text and values for your selection(s), like so:

Label1.Text = "VALUE: " + myControl.Value + " TEXT: " + myControl.Text;

This control supports both single and multiple selections. You can specify a delimiter if you intend it to be multi-selectable.

You can expand upon the functionality of this control as you require it.

The code

First things first. Create a blank Web Control in the Controls folder of your website. For this example, I called mine SQLPicker.ascx.

For the SQLPicker.ascx file, only one additional line is required. A PlaceHolder.

<%@ Control Language="C#" AutoEventWireup="true" CodeFile="SQLPicker.ascx.cs" 
    Inherits="Controls_SQLPicker" %>
<asp:PlaceHolder ID="ControlPlaceHolder" runat="server"></asp:PlaceHolder>

That was the easy part. Now, for the code-behind (SQLPicker.ascx.cs).

First, you'll need to specify a using statement if you're going to be running a SQL query to populate the control.

using System.Data.SqlClient;

Now, let's build the actual control.

Let's start by enumerating the types of controls we'll be able to display (which will be set by ControlType when the control is used). If done correctly, you'll notice the four options we specified through the intellisense when using the control. Note: I also use some controls by Telerik RadControls (such as a combobox), but since not everyone has them, I didn't include them as an example.

public enum myControlType
{
    CheckList,
    RadioList,
    ListBox
}

private myControlType myControl;
public myControlType ControlType
{
    get { return myControl; }
    set { myControl = value; }
}

Let's also go ahead and allow for other settings to be specified, such as height and width...

private string myWidth;
public string Width
{
    get { return myWidth; }
    set { myWidth = value; }
}
private string myHeight;
public string Height
{
    get { return myHeight; }
    set { myHeight = value; }
}
public enum mySqlQuerySelection
{
    CustomerList,
    CountryList
}
private mySqlQuerySelection mySqlQuery;
public mySqlQuerySelection SqlQuery
{
    set { mySqlQuery = value; }
}
private string mySelectCommand;
public string SelectCommand
{
    set { mySelectCommand = value; }
}
private string myDataTextField;
public string DataTextField
{
    set { myDataTextField = value; }
}
private string myDataValueField;
public string DataValueField
{
    set { myDataValueField = value; }
}

private string myDelimiter;
public string Delimiter
{
    set { myDelimiter = value; }
}
private string myID;
public override string ID
{
    set { myID = value; }
}

Now comes the nuts and bolts of the control:

CheckBoxList cbl = new CheckBoxList();
RadioButtonList rbl = new RadioButtonList();
ListBox lb = new ListBox();

protected void Page_Load(object sender, EventArgs e)
{
    // Lets get the settings that were set by the control and 
    // store them to some variables
    string sID = this.myID;
    string sWidth = this.myWidth;
    string sHeight = this.myHeight;
    myControlType sControlType = this.myControl;
    mySqlQuerySelection sQuery = this.mySqlQuery;
    string sSelectCommand = this.mySelectCommand;
    string sDataTextField = this.myDataTextField;
    string sDataValueField = this.myDataValueField;
    
    int width = -1;
    int height = -1;
    bool pixel = true;

    // We'll need to see if width & height are set to pixel or percent
    if (null != sWidth && sWidth != string.Empty)
    {
        if (!sWidth.Contains("%"))
        {
            sWidth = sWidth.TrimEnd('p', 'x');
        }
        else
        {
            pixel = false;
            sWidth = sWidth.TrimEnd('%');
        }
        width = Convert.ToInt32(sWidth);
    }
    if (null != sHeight && sHeight != string.Empty)
    {
        if (!sHeight.Contains("%"))
        {
            sHeight = sHeight.TrimEnd('p', 'x');
        }
        else
        {
            pixel = false;
            sHeight = sHeight.TrimEnd('%');
        }
        height = Convert.ToInt32(sHeight);
    }

    // Lets create an empty placeholder where are control will go
    PlaceHolder ph = new PlaceHolder();

    // Now we need to determine if the control is using a preset query
    // or one that is user specified
    string query = "";
    if (null != sSelectCommand && sSelectCommand != string.Empty)
        query = sSelectCommand;
    else
        query = GetQueryString();
    string dataTextField = "";
    if (null != sDataTextField && sDataTextField != string.Empty)
        dataTextField = sDataTextField;
    else
        dataTextField = GetDataTextField();
    string dataValueField = "";
    if (null != sDataValueField && sDataValueField != string.Empty)
        dataValueField = sDataValueField;
    else
        dataValueField = GetDataValueField();

    // Now lets build the control & populate it based on these settings
    switch (sControlType)
    {
        default:
            break;
        case myControlType.CheckList:
            cbl.DataSource = GetDataTable(query);
            cbl.DataTextField = dataTextField;
            cbl.DataValueField = dataValueField;
            cbl.DataBind();
            cbl.ID = sID;
            ph.Controls.Add(cbl); // adds this control into the placeholder
            break;
        case myControlType.RadioList:
            rbl.DataSource = GetDataTable(query);
            rbl.DataTextField = dataTextField;
            rbl.DataValueField = dataValueField;
            rbl.DataBind();
            rbl.ID = sID;
            ph.Controls.Add(rbl);
            break;
        case myControlType.ListBox:
            lb.DataSource = GetDataTable(query);
            lb.DataTextField = dataTextField;
            lb.DataValueField = dataValueField;
            lb.DataBind();
            lb.ID = sID;
            lb.SelectionMode = ListSelectionMode.Multiple;
            lb.ToolTip = "Press Ctrl to select multiple values";
            if (pixel == true && width != -1)
                lb.Width = Unit.Pixel(width);
            else if (width != -1)
                lb.Width = Unit.Percentage(Convert.ToDouble(width));
            if (pixel == true && height != -1)
                lb.Height = Unit.Pixel(height);
            else if (height != -1)
                lb.Height = Unit.Percentage(Convert.ToDouble(height));
            ph.Controls.Add(lb);
            break;
    }
    // Finally lets take the control we built and place it into the
    // placeholder on the aspx page
    ControlPlaceHolder.Controls.Add(ph);
}

Additional routines used in page_load are as follows. Hopefully, it's self explanatory. If not, please feel free to comment.

private string GetQueryString()
{
    mySqlQuerySelection sqlQuery = this.mySqlQuery;
    string query = "";
    switch (sqlQuery)
    {
        default:
            query = "";
            break;
        case mySqlQuerySelection.CustomerList:
            query = "select customer_name as name, customer_id as id " +
              "from myCustomerTable order by 1";
            break;
        case mySqlQuerySelection.CountryList:
            query = "select country, country_code from myCountryTable order by 1";
            break;
    }
    return query;
}
private string GetDataTextField()
{
    mySqlQuerySelection sqlQuery = this.mySqlQuery;
    string dtf = "";
    switch (sqlQuery)
    {
        default:
            dtf = "";
            break;
        case mySqlQuerySelection.CustomerList:
            dtf = "name";
            break;
        case mySqlQuerySelection.CountryList:
            dtf = "country";
            break;
    }
    return dtf;
}
private string GetDataValueField()
{
    mySqlQuerySelection sqlQuery = this.mySqlQuery;
    string dvf = "";
    switch (sqlQuery)
    {
        default:
            dvf = "";
            break;
        case mySqlQuerySelection.CustomerList:
            dvf = "id";
            break;
        case mySqlQuerySelection.CountryList:
            dvf = "country_code";
            break;
    }
    return dvf;
}
public DataTable GetDataTable(string query)
{
    DataTable myDataTable = new DataTable();

    // retrieve connection string stored in the web.config file
    String ConnString = 
      ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString;
    SqlConnection conn = new SqlConnection(ConnString);
    SqlDataAdapter adapter = new SqlDataAdapter();
    SqlCommand command = new SqlCommand(query, conn);
    command.CommandTimeout = 0;
    adapter.SelectCommand = command;
    conn.Open();
    try
    {
        adapter.Fill(myDataTable);
    }
    finally
    {
        conn.Close();
    }
    return myDataTable;
}

And lastly, a way for the control user to get the selection values:

public string Text
{
    get
    {
        string text = "";
        string delimiter = this.myDelimiter;
        myControlType sControlType = this.myControl;
        switch (sControlType)
        {
            case myControlType.CheckList:
                for (int x = 0; x < cbl.Items.Count; x++)
                {
                    if (cbl.Items[x].Selected == true)
                        text += cbl.Items[x].Text + delimiter;
                }
                break;
            case myControlType.RadioList:
                text = rbl.SelectedItem.Text;
                break;
            case myControlType.ListBox:
                foreach (ListItem lstItem in lb.Items)
                {
                    if (lstItem.Selected == true)
                    {
                        if (text.Length == 0)
                        {
                            text = lstItem.Text;
                        }
                        else
                        {
                            // Add text to label.
                            text += delimiter + lstItem.Text;
                        }
                    }
                }
                break;
        }
        return text;
    }
}
public string Value
{
    get
    {
        string value = "";
        string delimiter = this.myDelimiter;
        myControlType sControlType = this.myControl;
        switch (sControlType)
        {
            case myControlType.CheckList:
                for (int x = 0; x < cbl.Items.Count; x++)
                {
                    if (cbl.Items[x].Selected == true)
                        value += cbl.Items[x].Value + delimiter;
                }
                if (value.Length > 1)
                    value = value.Substring(0, (value.Length - 1));
                break;
            case myControlType.RadioList:
                value = rbl.SelectedValue;
                break;
            case myControlType.ListBox:
                foreach (ListItem lstItem in lb.Items)
                {
                    if (lstItem.Selected == true)
                    {
                        if (value.Length == 0)
                        {
                            value = lstItem.Value;
                        }
                        else
                        {
                            // Add text to label.
                            value += delimiter + lstItem.Value;
                        }
                    }
                }
                break;
        }
        return value;
    }
}

That should about cover the basics. The control can be expanded as required to provide additional ways to present the data (such as a combo box) and additional preset queries to expand the selection available. The result is a control that you can instantly transform from one type to another and populate it with whatever data you choose, with little code on your actual web page.

History

• 1.0 - Initial release.
• 1.1 - Project sample attached.