Post
Ask Question

Insert XML Document in SQL Server in ASP.Net

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.

Now  I will show you how to bind the records on a GridView control from the XML file after binding the records in a GridView control then selected records from the GridView will be inserted into the SQL Server database. Use the following procedure to do that.

Create Database and Table in SQL Server

 

  1. Create Database Employee  
  2. Use Employee  
  3. create table EmpInfo  
  4. (  
  5. UserId int,  
  6. UserName nvarchar(max)  
  7. )  

 

Step 1

Open Visual Studio then select "Create New Website" --> "ASP.NET Web Site".

CreateApplication

Step 2

Now go to Solution Explorer to the right side of the application and do as shown in the following figure.

AddNewItem

Step 3

Create a new XML file.as in the following figure.

CreateXMLFile

Step 4

Use the following code in the EmployeeDetails.xml file.

 

  1. <?xml version="1.0" encoding="utf-8" ?>  
  2. <EmployeeInformation>  
  3.   <Employee>  
  4.     <ID>101</ID>  
  5.     <UserName>Pankaj Lohani</UserName>  
  6.   </Employee>  
  7.   <Employee>  
  8.     <ID>102</ID>  
  9.     <UserName>Nimit Joshi</UserName>  
  10.   </Employee>  
  11.   <Employee>  
  12.     <ID>103</ID>  
  13.     <UserName>Pravesh Khanduri</UserName>  
  14.   </Employee>  
  15.   <Employee>  
  16.     <ID>104</ID>  
  17.     <UserName>Amit Senwal</UserName>  
  18.   </Employee>  
  19.   <Employee>  
  20.     <ID>105</ID>  
  21.     <UserName>Ravi Kumar</UserName>  
  22.   </Employee>  
  23.   <Employee>  
  24.     <ID>105</ID>  
  25.     <UserName>Ainul Hasan</UserName>  
  26.   </Employee>  
  27.   <Employee>  
  28.     <ID>106</ID>  
  29.     <UserName>Ashish Topwal</UserName>  
  30.   </Employee>  
  31. </EmployeeInformation>  

 

Step 5

Add a new Web form in the empty web application as in the following figure.

AddNewForm

Step 6

Write the following code in the Default.aspx page:

  1. <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>  
  2. <!DOCTYPE html>  
  3. <html xmlns="http://www.w3.org/1999/xhtml" >  
  4. <head id="Head1" runat="server">  
  5.     <title>Untitled Page</title>  
  6. </head>  
  7. <body>  
  8.     <form id="form1" runat="server">  
  9.     <div>  
  10.     <asp:GridView ID="empdetails" runat="server" AutoGenerateColumns="False"   
  11.      HeaderStyle-ForeColor="White" CellPadding="4" ForeColor="#333333" GridLines="None" >  
  12. <AlternatingRowStyle BackColor="White" ForeColor="#284775" />  
  13. <Columns>  
  14. <asp:TemplateField>  
  15. <ItemTemplate>  
  16.   <asp:CheckBox ID="Chkbox" runat="server" />  
  17. </ItemTemplate>  
  18. </asp:TemplateField>  
  19. <asp:BoundField DataField="Id" HeaderText="EmployeeId" />  
  20. <asp:BoundField DataField="UserName" HeaderText="UserName" />  
  21. </Columns>  
  22. <EditRowStyle BackColor="#999999" />  
  23. <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />  
  24. <HeaderStyle BackColor="#5D7B9D" ForeColor="White" Font-Bold="True"></HeaderStyle>  
  25.         <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />  
  26.         <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />  
  27.         <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />  
  28.         <SortedAscendingCellStyle BackColor="#E9E7E2" />  
  29.         <SortedAscendingHeaderStyle BackColor="#506C8C" />  
  30.         <SortedDescendingCellStyle BackColor="#FFFDF8" />  
  31.         <SortedDescendingHeaderStyle BackColor="#6F8DAE" />  
  32. </asp:GridView>  
  33.     </div>  
  34.     <asp:Button ID="EmpDetailsbtn" runat="server" onclick="EmpDetails" Text="Show Employee Details" />  
  35.      <asp:Button ID="Savebtn" runat="server" onclick="SaveRecord"   
  36.         Text="Insert Selected Records" Font-Bold="True" />  
  37.         <asp:Label ID="lblmsg" runat="server"></asp:Label>  
  38.     </form>  
  39. </body>  
  40. </html>  

Add the ConnectionString in Web.config file suck like :

  1. <connectionStrings>  
  2.  <add name="dbconnection" connectionString="Data Source=; Initial Catalog=Employee;   
  3. User=abc; Password=****" providerName="SqlClient"/>  
  4. </connectionStrings> 

Step 7

Now use the following code in Default.aspx.cs:

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.UI;  
  6. using System.Data;  
  7. using System.Data.SqlClient;  
  8. using System.Configuration;  
  9. using System.Xml;  
  10. using System.Web.UI.WebControls;  
  11. public partial class _Default : System.Web.UI.Page  
  12. {  
  13.     protected void Page_Load(object sender, EventArgs e)  
  14.     {  
  15.         lblmsg.Visible = false;  
  16.     }  
  17.     string conString = ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString;  
  18.     SqlCommand com;  
  19.     int check;  
  20.     protected void EmpDetails(object sender, EventArgs e)  
  21.     {  
  22.         DataSet empinfo = new DataSet();  
  23.         empinfo.ReadXml(Server.MapPath("EmployeeDetails.xml"));  
  24.         empdetails.DataSource = empinfo;  
  25.         empdetails.DataBind();  
  26.     }  
  27.     protected void SaveRecord(object sender, EventArgs e)  
  28.     {  
  29.         for (int i = 0; i <= empdetails.Rows.Count - 1; i++)  
  30.         {  
  31.             GridViewRow row = empdetails.Rows[i];  
  32.             CheckBox Chbox = (CheckBox)row.FindControl("Chkbox");  
  33.             if (Chbox.Checked == true)  
  34.             {  
  35.                 check++;  
  36.             }  
  37.         }  
  38.         if (check == 0)  
  39.         {  
  40.             Page.RegisterStartupScript("Alert Message",  
  41.          "<script language='javascript'>alert('Please Check atleast one record');</script>");  
  42.             return;  
  43.         }  
  44.         for (int i = 0; i <= empdetails.Rows.Count - 1; i++)  
  45.         {  
  46.             string eid = empdetails.Rows[i].Cells[1].Text;  
  47.             string uname = empdetails.Rows[i].Cells[2].Text;  
  48.             GridViewRow gvrow = empdetails.Rows[i];  
  49.             CheckBox Chbox = (CheckBox)gvrow.FindControl("Chkbox");  
  50.             if (Chbox.Checked == true)  
  51.             {  
  52.                 SaveData(eid, uname);  
  53.             }  
  54.             lblmsg.Text = "Data Inserted Successfully..";  
  55.             lblmsg.Visible = true;  
  56.         }  
  57.     }  
  58.     void SaveData(String eid, String uname)  
  59.     {  
  60.         SqlConnection con = new SqlConnection(conString);  
  61.         try  
  62.         {  
  63.             con.Open();  
  64.             com = new SqlCommand("insert into EmpInfo values('" + eid + "','" + uname + "')", con);  
  65.             com.ExecuteNonQuery();  
  66.             con.Close();  
  67.         }  
  68.         catch (Exception ex)  
  69.         {  
  70.             Response.Write(ex.ToString());  
  71.         }  
  72.     }  
  73. }   

The GridView "Rows" property provides access to the data rows in the GridView. The Rows property returns a collection of GridView instances that make up the Gridview's datarows. The row's CheckBox is programmitically accessed using gridrows.FindControl("Chkbox"). Checkboxes allow the user to select one or more rows  from the gridvew control and you can access the CheckBoxes in code to determine whether a given checkbox is checked or to change the checked state.

Step 8

Debug the application by pressing F5 to execute the Web form. After debugging the application the output will be as in the following figure:

AferDebug

Step 9

If you will click the "Insert button" without checking any record then a form will generate a validation as in the following figure.

ValidateForm

Step 10

Check the records that you want to insert into the database as in the following figure.

SaveDataInDatabase

Now if you want to show the table in a database just write the query and press Enter as in the following figure:

  1. select * from EmpInfo  

Showtable

Step 11

Check the Multiple Records to insert in database as in the following figure.

CheckMultipleRecords

  1. select * from EmpInfo  

ShowRecordsInTable

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.


Similar Articles