Introduction
Here I will explain how to Import or insert data into SQL database from Excel spreadsheet using Sqlbulkcopy method.
Description
I have searched for so many posts that explain static manner some of the posts are not clearly some of the posts are not supporting for latest excel files so many problems i faced by using those examples I have done application that will support for all excel versions and it will work for you without having any problems and it will dynamically you can upload excel sheet from anywhere from your computer.
I want to copy this exeltable data into a SQL Server Database Table, called Excel_table, with the same schema.
Design your aspx page like this
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ExcelExample2.aspx.cs" Inherits="ExcelExample2" %>
<!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></title>
<style type="text/css">
.style1
{
width: 100%;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<table class="style1">
<tr>
<td>
Table Name</td>
<td>
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
</td>
<td>
</td>
</tr>
<tr>
<td>
Excel File Upload</td>
<td>
<asp:FileUpload ID="FileUpload1" runat="server" />
</td>
</tr>
<tr>
<td>
</td>
<td>
<asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Button" />
<asp:Label ID="Label1" runat="server"></asp:Label>
</td>
</tr>
</table>
</div>
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</form>
</body>
</html>
After that write the following code in codebehind button click
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Data.OleDb;
public partial class ExcelExample2 : System.Web.UI.Page
{
SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=D:\WebSite1
\WebSite1\App_Data\EmployeeDatabase.mdf;Integrated
Security=True;User Instance=True");
protected void Page_Load(object sender, EventArgs e)
{
// form_lolll();
}
protected void Button1_Click(object sender, EventArgs e)
{
try
{
SqlDataAdapter da = new SqlDataAdapter(@"SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES ", con);
DataTable dt = new DataTable();
da.Fill(dt);
bool flag;
if (dt.Rows.Count > 0)
{
string tableName_Txt = TextBox1.Text;
flag = false;
for (int j = 0; j < dt.Rows.Count; j++)
{
string tablename_DB = dt.Rows[j]["TABLE_NAME"].ToString();
//compare database table and excel table
if (tableName_Txt == tablename_DB)
{
flag = true;
break;
}
}
if (flag)
{
string connectionString = "";
if (FileUpload1.HasFile)
{
string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
string fileExtension = Path.GetExtension(FileUpload1.PostedFile.FileName);
string fileLocation = Server.MapPath("~/App_Data/" + fileName);
FileUpload1.SaveAs(fileLocation);
//Check whether file extension is xls or xslx
if (fileExtension == ".xls")
{
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;
HDR=Yes;IMEX=2\"";
}
else if (fileExtension == ".xlsx")
{
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0
;Data Source=" + fileLocation + ";Extended Properties=\"Excel ;
HDR=Yes;IMEX=2\"";
}
//Create OleDB Connection and OleDb Command
OleDbConnection conn = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = conn;
OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
DataTable dt1 = new DataTable();
conn.Open();
DataTable dtExcelSheetName = conn.GetOleDbSchemaTable
(OleDbSchemaGuid.Tables, null);
string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
cmd.CommandText = "SELECT * FROM [" + getExcelSheetName + "]";
dAdapter.SelectCommand = cmd;
dAdapter.Fill(dt1);
conn.Close();
SqlDataAdapter da1 = new SqlDataAdapter(@"SELECT * FROM "
+ tableName_Txt + " ", con);
DataTable dt2 = new DataTable();
da1.Fill(dt2);
//compare database table colums count and excel file colums count
if (dt1.Columns.Count == dt2.Columns.Count)
{
flag = false;
for (int i = 0; i < dt1.Columns.Count; i++)
{
//compare database table colums names and excel file colum names
if (dt1.Columns[i].ColumnName == dt2.Columns[i].ColumnName
& dt1.Columns[i].GetType() == dt2.Columns[i].GetType())
{
flag = true;
}
else
{
flag = false;
Response.Write("Both Column Names are Not Equal \n");
break;
}
}
if (flag)
{
con.Open();
conn.Open();
string excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;
HDR=Yes;IMEX=2\"";
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
excelConnection.Open();
OleDbCommand cmd1 = new OleDbCommand("Select * from
[" + getExcelSheetName + "]", excelConnection);
OleDbDataReader dr;
dr = cmd.ExecuteReader();
SqlBulkCopy sqlBulk = new SqlBulkCopy(con);
sqlBulk.DestinationTableName = tableName_Txt;
sqlBulk.WriteToServer(dr);
excelConnection.Close();
conn.Close();
con.Close();
Label1.Text = "Insert successfully";
}
}
else
{
Label1.Text = " Both Datatables Columns are Not Equal";
}
}
}
else
{
Label1.Text = "Required Table Not Exits in DataBase";
TextBox1.Text = "";
TextBox1.Focus();
}
}
}
catch
{
throw;
}
}
}
