In
this article I am going to explain how to import xml file data into sql table
using store procedure in asp.net.
In
previous article I have explained how to create stacked and multi series columnchart using Google chart API in asp.net MVC, how to show 0 instead of nullvalue in sql server and COALESCE function of sql server with example.
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 :
Build the project and run. To test upload the XML file.
Build the project and run. To test upload the XML file.
.png)
No comments:
Post a Comment