Monday 13 May 2013

how to Save and retrieve ASP.Net CheckBoxList items into SQL Server Database


how to Save and retrieve ASP.Net CheckBoxList items into SQL Server Database
Database Design
I created a new database called as dbHobbies with a table called as Hobbies which has the following structure
Description: Database design- Save and retrieve checkboxlist items in SQL server database ASP.Net
Connection String
Once the database is ready you can create the connection string that will be used to connect to the database.
<addname="constr"connectionString="Data Source = .\SQLExpress;
      Initial Catalog = dbHobbies; Integrated Security = true"/>
HTML Markup
Below is the HTML Markup of the ASP.net web page.
Hobbies:
<asp:CheckBoxList ID="chkHobbies" runat="server">
</asp:CheckBoxList>
<br />
<asp:Button ID="btnUpdate" runat="server" Text="Button" OnClick = "UpdateHobbies" />

As you can see I have added a CheckBoxList and a Button that will allow the user to update the selections in the database.
The screenshot below describes how the User Interface looks

Description: Save and retrive checkboxlist items in SQL Server Database ASP.Net
 
Populating the CheckBoxList from Database
The following method is used to populate the Hobbies CheckBoxList from the SQL Server Database
C#
private void PopulateHobbies()
{
    using (SqlConnection conn = new SqlConnection())
    {
        conn.ConnectionString = ConfigurationManager
                .ConnectionStrings["constr"].ConnectionString;
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.CommandText = "select * from hobbies";
            cmd.Connection = conn;
            conn.Open();
            using (SqlDataReader sdr = cmd.ExecuteReader())
            {
                while (sdr.Read())
                {
                    ListItem item = new ListItem();
                    item.Text = sdr["Hobby"].ToString();
                    item.Value = sdr["HobbyId"].ToString();
                    item.Selected = Convert.ToBoolean(sdr["IsSelected"]);
                    chkHobbies.Items.Add(item);
                }
            }
           conn.Close();
        }
    }
}

The above method is called up in the page load event in the following way
C#
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        this.PopulateHobbies();
    }
}
Saving the Selections in Database
The following method is called up on the Submit Button Click event and is used to save the user selections to the database
C#
protected void UpdateHobbies(object sender, EventArgs e)
{
    using (SqlConnection conn = new SqlConnection())
    {
        conn.ConnectionString = ConfigurationManager
                .ConnectionStrings["constr"].ConnectionString;
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.CommandText = "update hobbies set IsSelected = @IsSelected" +
                              " where HobbyId=@HobbyId";
            cmd.Connection = conn;
            conn.Open();
            foreach (ListItem item in chkHobbies.Items)
            {
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@IsSelected", item.Selected);
                cmd.Parameters.AddWithValue("@HobbyId", item.Value);
                cmd.ExecuteNonQuery();
            }
            conn.Close();
        }
    }
}

No comments:

Post a Comment