This example shows how to bind DropDownList, ListBox and CheckBoxList control with data from database using the ADO.NET way.
Note that in this demo, I’m using the Northwind database. Now let’s set up the connection string.
STEP 1: Setting up the Connection string
In your web.config file set up the connection string there as shown below:
<connectionStrings>
<add name="DBConnection" connectionString="Data Source=.\SQLEXPRESS;
AttachDbFilename=|DataDirectory|\Northwind.mdf;
Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient"/>
</connectionStrings>
Note: DBConnection is the name of the Connection String that we can use as a reference in our codes later.
In code behind, you can reference that connection string like this:
private string GetConnectionString(){
return ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
}
Since we are done setting up the connection string then we can start populating the Controls, first let’s start populating the DropDownList.
STEP 2: Populating the DropDownList
To start, grab a DropDownList control from the visual studio ToolBox and place it the WebForm. The ASPX source should look something like this:
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Binding DropDownList, ListBox and CheckBoxList</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:DropDownList ID="DropDownList1" runat="server">
</asp:DropDownList>
</div>
</form>
</body>
</html>
Here’s the code block for binding the DropDownList in the code behind:
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections.Specialized;
using System.Text;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindDropDownList();
}
}
private string GetConnectionString()
{
return ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
}
private void BindDropDownList()
{
DataTable dt = new DataTable();
SqlConnection connection = new SqlConnection(GetConnectionString());
try
{
connection.Open();
string sqlStatement = "SELECT Top(20)* FROM Customers";
SqlCommand sqlCmd = new SqlCommand(sqlStatement, connection);
SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
sqlDa.Fill(dt);
if (dt.Rows.Count > 0)
{
DropDownList1.DataSource =dt;
DropDownList1.DataTextField = "ContactName"; // the items to be displayed in the list items
DropDownList1.DataValueField = "CustomerID"; // the id of the items displayed
DropDownList1.DataBind();
}
}
catch (System.Data.SqlClient.SqlException ex)
{
string msg = "Fetch Error:";
msg += ex.Message;
throw new Exception(msg);
}
finally
{
connection.Close();
}
}
}
Here’s the page output below
STEP 3: Binding the ListBox Control
We can bind the ListBox control the same way as what we did for binding the DropDownList by simply setting its DataTextField and DataValueField.
Now let’s grab a ListBox control from the visual studio toolbox. The mark up should look something like this:
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Binding DropDownList, ListBox and CheckBoxList</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:ListBox ID="ListBox1" runat="server"Height="200px" Width="100px">
</asp:ListBox>
</div>
</form>
</body>
</html>
And here are the relevant codes for binding the ListBox in the code behind.
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections.Specialized;
using System.Text;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindListBox();
}
}
private string GetConnectionString()
{
return ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
}
private void BindListBox()
{
DataTable dt = new DataTable();
SqlConnection connection = new SqlConnection(GetConnectionString());
try
{
connection.Open();
string sqlStatement = "SELECT Top(20)* FROM Customers";
SqlCommand sqlCmd = new SqlCommand(sqlStatement, connection);
SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
sqlDa.Fill(dt);
if (dt.Rows.Count > 0)
{
ListBox1.DataSource =dt;
ListBox1.DataTextField = "ContactName"; // the items to be displayed in the list items
ListBox1.DataValueField = "CustomerID"; // the id of the items displayed
ListBox1.DataBind();
}
}
catch (System.Data.SqlClient.SqlException ex)
{
string msg = "Fetch Error:";
msg += ex.Message;
throw new Exception(msg);
}
finally
{
connection.Close();
}
}
}
Below is the page output:
STEP 4: Binding the CheckBoxList Control
Same procedure as what we did for DropDownList and ListBox..
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Binding DropDownList, ListBox and CheckBoxList</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:CheckBoxList ID="CheckBoxList1" runat="server" Width="200px">
</asp:CheckBoxList>
</div>
</form>
</body>
</html>
Here are relevant codes for binding the CheckBoxList in the code behind:
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections.Specialized;
using System.Text;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindCheckBoxList();
}
}
private string GetConnectionString()
{
return ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;
}
private void BindCheckBoxList()
{
DataTable dt = new DataTable();
SqlConnection connection = new SqlConnection(GetConnectionString());
try
{
connection.Open();
string sqlStatement = "SELECT Top(20)* FROM Customers";
SqlCommand sqlCmd = new SqlCommand(sqlStatement, connection);
SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
sqlDa.Fill(dt);
if (dt.Rows.Count > 0)
{
CheckBoxList1.RepeatColumns = 4; // set the number of columns in the CheckBoxList
CheckBoxList1.DataSource =dt;
CheckBoxList1.DataTextField = "ContactName"; // the items to be displayed in the list items
CheckBoxList1.DataValueField = "CustomerID"; // the id of the items displayed
CheckBoxList1.DataBind();
}
}
catch (System.Data.SqlClient.SqlException ex)
{
string msg = "Fetch Error:";
msg += ex.Message;
throw new Exception(msg);
}
finally
{
connection.Close();
}
}
}
The page output will look something like below:
That Simple! :)
Technorati Tags:
ADO.NET,
ASP.NET,
C#