Introduction
This article shows how to read a XML file (EmployeeDetaisl.xml) residing in the application and the XML data will be displayed in a GridView control then selected rows will be inserted in a SQL Server database.
The ReadXml method reads an XML file and loads it into a DataSet. "MapPath" is a method of the "System.Web.HttpServerUtility" class, you need an instance of this class to call the method. In ASP pages an instance is available in the server member of the page that uses the "Server" variable for the current HttpContext.
Create DataBase and Table in SQL Server
Use the following code in the EmployeeDetails.xml file.
Summary
This article has shown how to read the XML file and bind XML data in a GridView control. After binding, only selected GridView rows will be inserted into a SQL Server database.
This article shows how to read a XML file (EmployeeDetaisl.xml) residing in the application and the XML data will be displayed in a GridView control then selected rows will be inserted in a SQL Server database.
The ReadXml method reads an XML file and loads it into a DataSet. "MapPath" is a method of the "System.Web.HttpServerUtility" class, you need an instance of this class to call the method. In ASP pages an instance is available in the server member of the page that uses the "Server" variable for the current HttpContext.
Create DataBase and Table in SQL Server
Create Database Employee
Use Employee
create table EmpInfo
(
UserId int,
UserName nvarchar(max)
)
<?xml version="1.0" encoding="utf-8" ?>
<EmployeeInformation>
<Employee>
<ID>101</ID>
<UserName>Pankaj Lohani</UserName>
</Employee>
<Employee>
<ID>102</ID>
<UserName>Nimit Joshi</UserName>
</Employee>
<Employee>
<ID>103</ID>
<UserName>Pravesh Khanduri</UserName>
</Employee>
<Employee>
<ID>104</ID>
<UserName>Amit Senwal</UserName>
</Employee>
<Employee>
<ID>105</ID>
<UserName>Ravi Kumar</UserName>
</Employee>
<Employee>
<ID>105</ID>
<UserName>Ainul Hasan</UserName>
</Employee>
<Employee>
<ID>106</ID>
<UserName>Ashish Topwal</UserName>
</Employee>
</EmployeeInformation>
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs"
Inherits="_Default" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="empdetails" runat="server" AutoGenerateColumns="False"
HeaderStyle-ForeColor="White" CellPadding="4" ForeColor="#333333" GridLines="None" >
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
<Columns>
<asp:TemplateField>
<ItemTemplate>
<asp:CheckBox ID="Chkbox" runat="server" />
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="Id" HeaderText="EmployeeId" />
<asp:BoundField DataField="UserName" HeaderText="UserName" />
</Columns>
<EditRowStyle BackColor="#999999" />
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#5D7B9D" ForeColor="White" Font-Bold="True"></HeaderStyle>
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<SortedAscendingCellStyle BackColor="#E9E7E2" />
<SortedAscendingHeaderStyle BackColor="#506C8C" />
<SortedDescendingCellStyle BackColor="#FFFDF8" />
<SortedDescendingHeaderStyle BackColor="#6F8DAE" />
</asp:GridView>
</div>
<asp:Button ID="EmpDetailsbtn" runat="server" onclick="EmpDetails"
Text="Show Employee Details" />
<asp:Button ID="Savebtn" runat="server" onclick="SaveRecord"
Text="Insert Selected Records" Font-Bold="True" />
<asp:Label ID="lblmsg" runat="server"></asp:Label>
</form>
</body>
</html>
Add the ConnectionString in Web.config file suck like :
<connectionStrings>
<add name="dbconnection" connectionString="Data Source=; Initial Catalog=Employee;
User=abc; Password=****" providerName="SqlClient"/>
</connectionStrings>
protected void Page_Load(object sender, EventArgs e)
{
lblmsg.Visible = false;
}
string conString = ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString;
SqlCommand com;
int check;
protected void EmpDetails(object sender, EventArgs e)
{
DataSet empinfo = new DataSet();
empinfo.ReadXml(Server.MapPath("EmployeeDetails.xml"));
empdetails.DataSource = empinfo;
empdetails.DataBind();
}
protected void SaveRecord(object sender, EventArgs e)
{
for (int i = 0; i <= empdetails.Rows.Count - 1; i++)
{
GridViewRow row = empdetails.Rows[i];
CheckBox Chbox = (CheckBox)row.FindControl("Chkbox");
if (Chbox.Checked == true)
{
check++;
}
}
if (check == 0)
{
Page.RegisterStartupScript("Alert Message",
"<script language='javascript'>alert('Please Check atleast one record');</script>");
return;
}
for (int i = 0; i <= empdetails.Rows.Count - 1; i++)
{
string eid = empdetails.Rows[i].Cells[1].Text;
string uname = empdetails.Rows[i].Cells[2].Text;
GridViewRow gvrow = empdetails.Rows[i];
CheckBox Chbox = (CheckBox)gvrow.FindControl("Chkbox");
if (Chbox.Checked == true)
{
SaveData(eid, uname);
}
lblmsg.Text = "Data Inserted Successfully..";
lblmsg.Visible = true;
}
}
void SaveData(String eid, String uname)
{
SqlConnection con = new SqlConnection(conString);
try
{
con.Open();
com = new SqlCommand("insert into EmpInfo values('" + eid + "','" + uname + "')", con);
com.ExecuteNonQuery();
con.Close();
}
catch (Exception ex)
{
Response.Write(ex.ToString());
}
}
This article has shown how to read the XML file and bind XML data in a GridView control. After binding, only selected GridView rows will be inserted into a SQL Server database.
