Saturday, March 11, 2017

Import XML file data into sql table using store procedure in ASP.NET

In this article I am going to explain how to import xml file data into sql table using store procedure 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.

Student.xml file look like this:

<?xml version="1.0" encoding="utf-16" ?>
<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></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" OnClick="btnupload_Click" /></td></tr>
            </table>
        </fieldset>
    </div>
    </form>
</body>
</html>

Import the namespace

C# code:
using System.Xml;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;
using System.Data;

VB.net code:
Imports System.Xml
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, contentXml;
            fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
            filepath = Server.MapPath("~/upload/") + fileName;
            FileUpload1.SaveAs(filepath);
            string xmlfile = File.ReadAllText(filepath);
            SqlCommand cmd = new SqlCommand("InsertXMLData", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@xmlfile", xmlfile);
            con.Open();
            cmd.ExecuteNonQuery();
            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, contentXml As String
            fileName = Path.GetFileName(FileUpload1.PostedFile.FileName)
            filepath = Server.MapPath("~/upload/") & fileName
            FileUpload1.SaveAs(filepath)
            Dim xmlfile As String = File.ReadAllText(filepath)
            Dim cmd As New SqlCommand("InsertXMLData", con)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@xmlfile", xmlfile)
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
            Response.Write("<script type=""text/javascript"">alert('file uploaded successfully');</script>")
        Catch ex As Exception
        End Try
    End Sub

Result :
Import XML file data into sql table using store procedure in ASP.NET

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

No comments:

Post a Comment