In this example we explain that how to import excel sheet data into sql server database using sqlBulkCopy in asp.net.
In previous example we already
explain that how to use sqlBulkCopy to copy data from data table to sql server
database.there is same scenario but here we use the fetch data from excel sheet
file and transfer or insert it in sql server database.
sqlbulkexcel.aspx:-
<%@
Page Language="C#" AutoEventWireup="true" CodeBehind="sqlbulkexcel.aspx.cs" Inherits="WebApplication1.sqlbulkexcel"
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Using SqlBulkCopy to import Excel SpreadSheet
data into SQL Server in ASP.Net using C# and VB.Net</title>
</head>
<body>
<form
id="form1"
runat="server">
<asp:FileUpload ID="FileUpload1"
runat="server"
/>
<asp:Button ID="Button1"
Text="Upload"
OnClick="Upload"
runat="server"
/>
</form>
</body>
</html>
sqlbulkexcel.aspx.cs:-
using System.IO;
using System.Data;
using
System.Data.OleDb;
using
System.Data.SqlClient;
using
System.Configuration;
using System;
namespace WebApplication1
{
public partial
class sqlbulkexcel
: System.Web.UI.Page
{
protected
void Page_Load(object
sender, EventArgs e)
{
}
protected
void Upload(object
sender, EventArgs e)
{
//Upload
and save the file
string
excelPath = Server.MapPath("~/Files/"+FileUpload1.FileName);
FileUpload1.SaveAs(Server.MapPath("~/Files/" + FileUpload1.FileName));
//string
excelPath = Server.MapPath(FileUpload1.FileName);
//FileUpload1.SaveAs(Server.MapPath(FileUpload1.FileName));
string
conString = string.Empty;
string
extension = Path.GetExtension(FileUpload1.FileName);
switch
(extension)
{
case
".xls": //Excel
97-03
conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case
".xlsx": //Excel
07 or higher
conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
break;
case
".csv": //Excel
07 or higher
conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
break;
}
conString = string.Format(conString, excelPath);
using
(OleDbConnection excel_con = new OleDbConnection(conString))
{
excel_con.Open();
string
sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
null).Rows[0]["TABLE_NAME"].ToString();
DataTable
dtExcelData = new DataTable();
//[OPTIONAL]:
It is recommended as otherwise the data will be considered as String by
default.
// dtExcelData.Columns.AddRange(new
DataColumn[4] { new DataColumn("Topic", typeof(string)),
// new
DataColumn("FisrtName", typeof(string)),
// new DataColumn("LastName",typeof(string)),
//new
DataColumn("Email", typeof(string))});
using
(OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
{
oda.Fill(dtExcelData);
}
excel_con.Close();
string
consString = @"Data
Source=RIGEL\SQL2008R2;Initial Catalog=AX2012R2_Contoso_SPICA;Integrated
Security=True";
using
(SqlConnection con = new SqlConnection(consString))
{
using
(SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
//Set the database table name
sqlBulkCopy.DestinationTableName = "dbo.Lead";
//[OPTIONAL]: Map the DataTable columns with that of the
database table
//sqlBulkCopy.ColumnMappings.Add("Topic", "Topic");
//sqlBulkCopy.ColumnMappings.Add("FisrtName",
"FisrtName");
//sqlBulkCopy.ColumnMappings.Add("LastName",
"LastName");
//sqlBulkCopy.ColumnMappings.Add("Email", "Email");
con.Open();
sqlBulkCopy.WriteToServer(dtExcelData);
con.Close();
}
}
}
}
}
}
0 comments:
Post a Comment