In
this article I am going to explain how to import XML file data into Sql table
using Sqlbulkcopy in asp.net.
In
previous article I have explained how to import xml file data into sql tableusing store procedure in asp.net, 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
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
No comments:
Post a Comment