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)
{
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;
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);
}
PlaceHolder ph = new PlaceHolder();
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();
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);
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;
}
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();
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
{
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
{
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.