This Way:
Ref:
<div>
<asp:DropDownList ID="ddlUsers" runat="server" >
<asp:ListItem Text="Azim" />
<asp:ListItem Text="Andrew" />
<asp:ListItem Text="Sabah" />
</asp:DropDownList>
Hobbies:
<asp:CheckBoxList ID="chkHobbies" runat="server">
</asp:CheckBoxList>
<br />
<asp:Button ID="btnUpdate" runat="server" Text="Button" OnClick="UpdateHobbies" />
</div>
C#:
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
this.PopulateHobbies();
}
}
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();
}
}
}
private void Delete()
{
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = ConfigurationManager
.ConnectionStrings["constr"].ConnectionString;
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = "Delete from UserHobby Where UserName = @UserName";
cmd.Connection = conn;
conn.Open();
foreach (ListItem item in chkHobbies.Items)
{
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@UserName", ddlUsers.SelectedItem.Text);
cmd.ExecuteNonQuery();
}
conn.Close();
}
}
}
protected void UpdateHobbies(object sender, EventArgs e)
{
this.Delete();
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = ConfigurationManager
.ConnectionStrings["constr"].ConnectionString;
using (SqlCommand cmd = new SqlCommand("ManageHobbies", conn))
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
conn.Open();
foreach (ListItem item in chkHobbies.Items)
{
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@Name", ddlUsers.SelectedItem.Text);
cmd.Parameters.AddWithValue("@IsSelected", item.Selected);
cmd.Parameters.AddWithValue("@HobbyId", item.Value);
cmd.ExecuteNonQuery();
}
conn.Close();
}
}
}
SP:
ALTER PROCEDURE ManageHobbies
@Name VARCHAR(50)
,@HobbyId INT
,@IsSelected BIT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO UserHobby (UserName,HobbyId,IsSelected) VALUES(@Name,@HobbyId,@IsSelected)
END
GO
CREATE TABLE [dbo].[UserHobby](
[ID] [int] IDENTITY(1,1) NOT NULL,
[UserName] [varchar](50) NOT NULL,
[HobbyId] [int] NOT NULL,
[IsSelected] [bit] NOT NULL,
CONSTRAINT [PK_UserHobby] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Thank You.