Import Xml & Excel file in Sql Database using c#
13:26
Database Table:
CREATE TABLE [dbo].[tbl_BalkUploadXml]( [ID] [uniqueidentifier] NULL, [Name] [nvarchar](200) NULL, [Phone] [nvarchar](200) NULL, [Address] [nvarchar](200) NULL, [Class] [nvarchar](200) NULL, [IsActive] [nvarchar](50) NULL ) CREATE TABLE [dbo].[tbl_BalkUploadExcel]( [ID] [uniqueidentifier] NULL, [IsListUpload] [nvarchar](1) NULL, [First_Name] [nvarchar](200) NULL, [Last_Name] [nvarchar](200) NULL, [Email] [nvarchar](200) NULL, [Phone_Number] [nvarchar](200) NULL, [Company_Name] [nvarchar](200) NULL )
Data.xml File:
<?xml version="1.0" encoding="utf-8" ?>
<data>
<Student>
<Name>Sourabh Mishra</Name>
<Phone>9999999999</Phone>
<Address>Delhi</Address>
<Class>MCA</Class>
<IsActive>1</IsActive>
</Student>
<Student>
<Name>Surbhee Mishra</Name>
<Phone>9123456789</Phone>
<Address>Delhi</Address>
<Class>BTech</Class>
<IsActive>1</IsActive>
</Student>
<Student>
<Name>Sachin</Name>
<Phone>9123499999</Phone>
<Address>Mumbai</Address>
<Class>Bsc</Class>
<IsActive>1</IsActive>
</Student>
<Student>
<Name>James</Name>
<Phone>9123499007</Phone>
<Address>London</Address>
<Class>Ms</Class>
<IsActive>1</IsActive>
</Student>
</data>
Employee.xlsx File:
BulkUpload.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="BulkUpload.aspx.cs" Inherits="MVC_Application.BulkUpload" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<h2>Upload Xml & Excel File</h2>
<br />
<div>
<asp:Button ID="btnBulkUploadXml" runat="server" Text="Load Bulk Data from Xml" OnClick="btnBulkUploadXml_Click" />
|
<asp:Button ID="btnBulkUploadExcel" runat="server" Text="Load Bulk Data from Excel" OnClick="btnBulkUploadExcel_Click" />
</div>
</form>
</body>
</html>
BulkUpload.aspx.cs
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.Common;
using System.Data.OleDb;
using System.IO;
namespace MVC_Application
{
public partial class BulkUpload : System.Web.UI.Page
{
public static string cs = ConfigurationManager.ConnectionStrings["strConnection"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnBulkUploadXml_Click(object sender, EventArgs e)
{
try
{
using (SqlConnection sqlConn = new SqlConnection(cs))
{
DataSet ds = new DataSet();
ds.ReadXml(Server.MapPath("~/xml/Data.xml"));
DataTable dtStudentMaster = ds.Tables["Student"];
dtStudentMaster.Columns.Add("ID", typeof(Guid));
foreach (DataRow row in dtStudentMaster.Rows)
{
row["ID"] = Guid.NewGuid(); // or set it to some other value
}
using (SqlBulkCopy sqlbc = new SqlBulkCopy(sqlConn))
{
sqlbc.DestinationTableName = "tbl_BalkUploadXml";
sqlbc.ColumnMappings.Add("ID", "ID");
sqlbc.ColumnMappings.Add("Name", "Name");
sqlbc.ColumnMappings.Add("Phone", "Phone");
sqlbc.ColumnMappings.Add("Address", "Address");
sqlbc.ColumnMappings.Add("Class", "Class");
sqlbc.ColumnMappings.Add("IsActive", "IsActive");
sqlConn.Open();
sqlbc.WriteToServer(dtStudentMaster);
sqlConn.Close();
Response.Write("Xml bulk data stored successfully");
}
}
}
catch (Exception ex)
{
throw ex;
}
}
protected void btnBulkUploadExcel_Click(object sender, EventArgs e)
{
try
{
string path = Server.MapPath("~/excel/Employee.xlsx");
string connectionString = string.Empty;
if (Path.GetExtension(path) == ".xlsx")
{
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";
}
else
{
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=Excel 8.0;";
}
OleDbConnection connection = new OleDbConnection();
connection.ConnectionString = connectionString;
OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
connection.Open();
DbDataReader dr = command.ExecuteReader();
DataTable ContentTable = null;
if (dr.HasRows)
{
ContentTable = new DataTable();
ContentTable.Columns.Add("ID", typeof(Guid));
ContentTable.Columns.Add("IsListUpload", typeof(string));
ContentTable.Columns.Add("First Name", typeof(string));
ContentTable.Columns.Add("Last Name", typeof(string));
ContentTable.Columns.Add("Email", typeof(string));
ContentTable.Columns.Add("Phone Number", typeof(string));
ContentTable.Columns.Add("Company Name", typeof(string));
while (dr.Read())
{
if (Convert.ToString(dr["IsListUpload"]).Trim() != string.Empty)
{
ContentTable.Rows.Add(Guid.NewGuid()
, Convert.ToString(dr["IsListUpload"]).Trim()
, Convert.ToString(dr["First Name"]).Trim()
, Convert.ToString(dr["Last Name"]).Trim()
, Convert.ToString(dr["Email"]).Trim()
, Convert.ToString(dr["Phone Number"]).Trim()
, Convert.ToString(dr["Company Name"]).Trim());
}
}
using (SqlConnection con = new SqlConnection(cs))
{
using (SqlBulkCopy sqlbc = new SqlBulkCopy(con))
{
sqlbc.DestinationTableName = "tbl_BalkUploadExcel";
sqlbc.ColumnMappings.Clear();
sqlbc.ColumnMappings.Add("ID", "ID");
sqlbc.ColumnMappings.Add("IsListUpload", "IsListUpload");
sqlbc.ColumnMappings.Add("First Name", "First_Name");
sqlbc.ColumnMappings.Add("Last Name", "Last_Name");
sqlbc.ColumnMappings.Add("Email", "Email");
sqlbc.ColumnMappings.Add("Phone Number", "Phone_Number");
sqlbc.ColumnMappings.Add("Company Name", "Company_Name");
con.Open();
sqlbc.WriteToServer(ContentTable);
con.Close();
Response.Write("Excel bulk data stored successfully");
}
}
}
dr.Close();
Response.Write("Excel bulk data stored successfully");
}
catch (Exception ex)
{
throw ex;
}
}
}
}
Web Page
Output
select * from tbl_BalkUploadXml select * from tbl_BalkUploadExcel
0 comments