--step 1:Create table
create table tblAddressProof(id int identity(1,1)primary key,name varchar(50),add_proof_id int,add_prof_value int)
--step 2: create proc
alter proc pr_saveAddressProof (
@p_name varchar(50)=null,
@p_xml xml=null,
@p_mode INT = null
)
as
begin
if @p_mode=0 --for insert
begin
insert into tblAddressProof(name,add_proof_id,add_prof_value)
select @p_name,
addressproofdetails.value('@add_proof_id','varchar(100)') AS add_proof_id,
addressproofdetails.value('@add_prof_value','varchar(100)') AS add_prof_value
from
@p_xml.nodes('/addressdetails/addressproofdetails') as temptable(addressproofdetails)
end
else if @p_mode=1 --for update
begin
delete from tblAddressProof where name=@p_name
insert into tblAddressProof(name,add_proof_id,add_prof_value)
select @p_name,
addressproofdetails.value('@add_proof_id','varchar(100)') AS add_proof_id,
addressproofdetails.value('@add_prof_value','varchar(100)') AS add_prof_value
from
@p_xml.nodes('/addressdetails/addressproofdetails') as temptable(addressproofdetails)
end
else if @p_mode=2 --for search
begin
select add_proof_id,add_prof_value from tblAddressProof where name=@p_name
end
end
exec pr_saveAddressProof
@p_name='Vicky',
@p_xml='<addressdetails>
<addressproofdetails add_proof_id="1" add_prof_value="0"></addressproofdetails>
<addressproofdetails add_proof_id="2" add_prof_value="0"></addressproofdetails>
<addressproofdetails add_proof_id="3" add_prof_value="1"></addressproofdetails>
</addressdetails>'
select *from tbladdressproof
--step 3: Write Code for Front End
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css" />
</head>
<body>
<form id="form1" runat="server">
<div class="card-header text-center">
<h3>Insert Checkbox List Value into Database using XML</h3>
</div>
<table class="col-3 table table-responsive" align="center">
<tr>
<td>Name</td>
<td>
<asp:TextBox ID="txtName" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>Address Proof</td>
<td>
<asp:CheckBoxList ID="cbAddressList" runat="server">
<asp:ListItem Value="1">Aadhar Card</asp:ListItem>
<asp:ListItem Value="2">Driving Licence</asp:ListItem>
<asp:ListItem Value="3">Voter ID</asp:ListItem>
</asp:CheckBoxList>
</td>
</tr>
<tr>
<td> </td>
<td>
<asp:Button ID="btnSubmit" runat="server" OnClick="btnSubmit_Click" Text="Submit" />
</td>
</tr>
</table>
<div>
</div>
</form>
</body>
</html>
--Step 4: Write code for Back end (Code behind page)
public partial class Default3 : System.Web.UI.Page
{
SqlConnection con;
SqlCommand cmd;
protected void Page_Load(object sender, EventArgs e)
{
string str = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
con = new SqlConnection(str);
if (!IsPostBack)
{
BindAddress();
}
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
con.Open();
cmd = new SqlCommand("pr_saveAddressProof", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@p_name", txtName.Text);
cmd.Parameters.AddWithValue("@p_xml", GetXMLForAddressProof());
cmd.Parameters.AddWithValue("@p_mode", 0);
cmd.ExecuteNonQuery();
Response.Write("<script language=javascript>alert('Record Inserted');</script>");
con.Close();
}
string GetXMLForAddressProof()
{
StringBuilder xml = new StringBuilder();
xml.Append("<addressdetails>");
foreach (ListItem item in cbAddressList.Items)
{
if (item.Selected)
{
xml.AppendFormat("<addressproofdetails add_proof_id={0} add_prof_value={1}>", "\""+ item.Value+ "\"", "\"" + 1 + "\"");
xml.Append("</addressproofdetails>");
}
else
{
xml.AppendFormat("<addressproofdetails add_proof_id={0} add_prof_value={1}>", "\"" + item.Value + "\"", "\"" + 0+ "\"");
xml.Append("</addressproofdetails>");
}
}
xml.Append("</addressdetails>");
return xml.ToString();
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
con.Open();
cmd = new SqlCommand("pr_saveAddressProof", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@p_name", txtName.Text);
cmd.Parameters.AddWithValue("@p_xml", GetXMLForAddressProof());
cmd.Parameters.AddWithValue("@p_mode", 1);
cmd.ExecuteNonQuery();
Response.Write("<script language=javascript>alert('Record Updated');</script>");
con.Close();
}
protected void btnSearch_Click(object sender, EventArgs e)
{
BindAddress();
}
public void BindAddress()
{
con.Open();
cmd = new SqlCommand("pr_saveAddressProof", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@p_name", txtName.Text);
cmd.Parameters.AddWithValue("@p_mode", 2);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
con.Close();
if (ds.Tables[0].Rows.Count > 0)
{
for (int i = 0; i < cbAddressList.Items.Count; i++)
{
string a = ds.Tables[0].Rows[i]["add_prof_value"].ToString();
if(a=="1")
{
cbAddressList.Items[i].Selected = true;
}
else
{
cbAddressList.Items[i].Selected = false;
}
}
}
}
}
