I decided to write this example because I always encounter this kind of issues at http://forums.asp.net .
This demo basically shows how to save the CheckBoxList selected items in the database and retain its selected items on postbacks or when the page is loaded again. To get started then lets start by creating the database for storing the data.
STEP 1: Creating the database.
- Launch Sql Server Management Studion Express and then connect
- Expand the Databases folder from the Sql Server object explorer
- Right click on the Databases folder and select “New Database”
- From the pop up window, input the database name you like and click add
- Expand the Database folder that you have just added
- Right click on the Tables folder and select “New Table”
Then add the following fields below:
Note that in this demo, I named the table as “MyTable". I also set the Id to auto increment so that the id will be automatically generated for every new added row. To do this select the Column name “Id” and in the column properties set the “Identity Specification” to yes.
Now, since the table is new then lets add a data in the Employees field for displaying purposes. The table would look something like this:
STEP2: Setting up the UI.
For the simplicity of this demo I just set the UI like this below in the WebForm.
.jpg)
ASPX Source:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Demo.aspx.cs"
Inherits="Demo" Debug="true" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Sample Demo</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:CheckBoxList ID="CheckBoxList1" runat="server">
</asp:CheckBoxList> <br />
<asp:Button ID="Button1" runat="server" Text="Save Changes"
OnClick="Button1_Click" />
</div>
</form>
</body>
</html>
As you can see the IU is very simple. It just contain a single CheckBoxList and a Button.
STEP3: Setting up the ConnectionString
We can set the ConnectionString in the web.config file this way:
<connectionStrings>
<add name="MyConsString" connectionString="Data Source=WPHVD185022-9O0;
Initial Catalog=MyDatabase;
Integrated Security=SSPI;"
providerName="System.Data.SqlClient" />
</connectionStrings>
Note: MyConsString is the name of the Connection String that we can use as a reference in our codes for setting the connection string later.
Then we can call the ConnectionString that was set up from the web.config file this way:
public string GetConnectionString(){
//sets the connection string from your web config file "ConnString" is the name of your Connection String
return System.Configuration.ConfigurationManager.ConnectionStrings["MyConsString"].ConnectionString;
}
STEP4: Binding the CheckBoxList with Data.
In this demo, we are going to bind our CheckBoxList with the list of Employees that comes from the Northwind database using the ADO.NET way.
Here are the code blocks below:
private void BindCheckBoxList(){
DataTable dt = new DataTable();
SqlConnection connection = new SqlConnection(GetConnectionString());
try
{
connection.Open();
string sqlStatement = "SELECT * FROM MyTable";
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 = "Employees"; // the items to be displayed in the list items
CheckBoxList1.DataValueField = "Employees"; // the id of the items displayed
CheckBoxList1.DataBind();
//Setting the Selected Items in the ChecBoxList based from the value in the database
//to do this, lets iterate to each items in the list
for (int i = 0; i < dt.Rows.Count; i++)
{
if (!string.IsNullOrEmpty(dt.Rows[i]["IsSelected"].ToString()))
{
CheckBoxList1.Items[i].Selected = Convert.ToBoolean(dt.Rows[i]["IsSelected"]);
}
}
}
}
catch (System.Data.SqlClient.SqlException ex)
{
string msg = "Fetch Error:";
msg += ex.Message;
throw new Exception(msg);
}
finally
{
connection.Close();
}
}
protected void Page_Load(object sender, EventArgs e){
if (!Page.IsPostBack){
BindCheckBoxList();
}
}
STEP5: Creating the Method for Saving the CheckBoxList Selected Items to the database.
Here’s the code block below:
private void Update(string name, bool isSelected) {
SqlConnection connection = new SqlConnection(GetConnectionString());
SqlCommand cmd;
string sqlStatement = string.Empty;
try {
connection.Open();
sqlStatement = "UPDATE MyTable SET IsSelected = @IsSelected WHERE Employees = @Employees";
cmd = new SqlCommand(sqlStatement, connection);
cmd.Parameters.AddWithValue("@Employees", name);
cmd.Parameters.AddWithValue("@IsSelected", isSelected);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
catch (System.Data.SqlClient.SqlException ex) {
string msg = "Insert/Update Error:";
msg += ex.Message;
throw new Exception(msg);
}
finally {
connection.Close();
}
}
STEP6: Calling the Method for Saving the state of CheckBoxList selected items.
Here’s the code block at Button_Click event :
protected void Button1_Click(object sender, EventArgs e){
string employeeName = string.Empty;
for (int i = 0; i < CheckBoxList1.Items.Count; i++)
{
if (CheckBoxList1.Items[i].Selected)
{
employeeName = CheckBoxList1.Items[i].Text;
Update(employeeName, CheckBoxList1.Items[i].Selected);
}
}
//Rebind the List to retain the selected items on postbacks
BindCheckBoxList();
}
Here's the page output below:
That’s it! Hope you will find this example useful!
Technorati Tags:
ADO.NET,
ASP.NET,
C#