Import XML data into Sql table using Sqlbulkcopy in asp.net - Free Asp.Net,MVC,AngularJs,Jquery,Javascript,Sql Server,WCF,Entity framework snippets and tutorial


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.


What do you think about this article?

If you found this article useful, please share and follow on Facebook, Twitter, Google Plus and other social media websites. To get free updates subscribe to newsletter. Please put your thoughts and feedback in comments section.

Share this

Share on FacebookTweet on TwitterPlus on Google+
loading...


EmoticonEmoticon