Sunday, March 12, 2017

Import XML data into Sql table using Sqlbulkcopy in asp.net

In this article I am going to explain how to import XML file data into Sql table using Sqlbulkcopy in asp.net.


Description:
I have an xml file (student) which store the information of student and want to import its data into Sql table using Sqlbulkcopy.

Student.xml file look like this:

<?xml version="1.0"?>
<students>
  <student>
    <name>Tarun</name>
    <class>X</class>
  <roll_no>1234</roll_no>
  </student>
<student>
    <name>Ram</name>
    <class>X</class>
  <roll_no>1235</roll_no>
  </student>
<student>
    <name>Rajeev</name>
    <class>X</class>
  <roll_no>1236</roll_no>
  </student>
<student>
    <name>Gopal</name>
    <class>X</class>
  <roll_no>1237</roll_no>
  </student>
</students>

Implementation:
I have create a table Students

Id
int
name
varchar(100)
class
varchar(50)
roll_no
int

HTML structure of page:
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <fieldset style="width:25%">
            <legend> Import XML data </legend>
            <table>
                <tr><td>Upload file :</td><td> <asp:FileUpload ID="FileUpload1" runat="server" /></td></tr>
                <tr><td></td><td><asp:Button ID="btnupload" runat="server" Text="Upload" /></td></tr>
            </table>
        </fieldset>
    </div>
    </form>
</body>
</html>

Import the namespace
C# code:
using System.Data.SqlClient;
using System.Configuration;
using System.IO;
using System.Data;

VB.net code:
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.IO
Imports System.Data
Import XML file
On button click write the below given code:

C# code:
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ToString());

protected void btnupload_Click(object sender, EventArgs e)
    {
        try
        {
            string fileName, filepath;
            fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
            filepath = Server.MapPath("~/upload/") + fileName;
            FileUpload1.SaveAs(filepath);
            DataSet ds = new DataSet();          
            DataTable dt = new DataTable();
            ds.ReadXml(filepath);
            dt = ds.Tables[0];
            con.Open();
            SqlBulkCopy bulkCopy = new SqlBulkCopy(con);        
            bulkCopy.ColumnMappings.Add("name", "name");
            bulkCopy.ColumnMappings.Add("class", "class");
            bulkCopy.ColumnMappings.Add("roll_no", "roll_no");
            bulkCopy.DestinationTableName = "Students";
            bulkCopy.WriteToServer(dt);
            con.Close();
            Response.Write("<script type=\"text/javascript\">alert('file uploaded successfully');</script>");
        }
        catch (Exception ex) { }
    }
VB.net code:
Private con As New SqlConnection(ConfigurationManager.ConnectionStrings("con").ToString())

    Protected Sub btnupload_Click(sender As Object, e As EventArgs) Handles btnupload.Click
        Try
            Dim fileName As String, filepath As String
            fileName = Path.GetFileName(FileUpload1.PostedFile.FileName)
            filepath = Server.MapPath("~/upload/") & fileName
            FileUpload1.SaveAs(filepath)
            Dim ds As New DataSet()
            Dim dt As New DataTable()
            ds.ReadXml(filepath)
            dt = ds.Tables(0)
            con.Open()
            Dim bulkCopy As New SqlBulkCopy(con)
            bulkCopy.ColumnMappings.Add("name", "name")
            bulkCopy.ColumnMappings.Add("class", "class")
            bulkCopy.ColumnMappings.Add("roll_no", "roll_no")
            bulkCopy.DestinationTableName = "Students"
            bulkCopy.WriteToServer(dt)
            con.Close()
            Response.Write("<script type=""text/javascript"">alert('file uploaded successfully');</script>")
        Catch ex As Exception
        End Try
    End Sub

Result:
Import XML data into Sql table using Sqlbulkcopy in asp.net


Build the project and run. To test upload the XML file.


No comments:

Post a Comment