If We Get Huge Number Of Records To Insert Into Database In Less Time Then We Use 'SqlBulkCopy' In C#. The Example Of How To Use SqlBulkCopy In C# To Insert Excel Sheet Records Into Database.
Create A Table : 'EXCEL_TABLE' In Sql Server
CREATE TABLE EXCEL_TABLE
(
UID INT IDENTITY(1,1),
UNAME VARCHAR(200)
)
And The Design Page In Visual Studio 2008 is:
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td align="center" valign="middle" bgcolor="#f8efeb" style="height: 20px">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="46%" align="left">
<table width="250" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="5">
</td>
<td width="350">
<a href="#">
<asp:Label ID="lblTitle" runat="server" Text="Import Data"></asp:Label></a><span class="rar">>></span>
</td>
</tr>
</table>
</td>
<td align="right" valign="middle" colspan="2">
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td align="center">
</td>
</tr>
<tr>
<td align="center">
<table id="tabErrMess" visible="false" runat="server" width="380" height="25" border="0"
cellpadding="0" cellspacing="0">
<tr>
<td align="center" valign="middle">
<table width="460px" border="0" cellspacing="0" cellpadding="0">
<tr>
<td align="left" >
<asp:Label ID="lblMessage" runat="server"></asp:Label>
</td>
</tr>
</table>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td align="left" style="padding-left: 100px">
<asp:Label ID="lblTitle1" runat="server" Text="Import Data"></asp:Label>
</td>
</tr>
<tr>
<td align="center" style="height: 5px;">
</td>
</tr>
<tr>
<td align="center" valign="middle">
<table width="80%" border="0" cellpadding="0" cellspacing="0" >
<tr>
<td height="8">
</td>
<td align="center" bgcolor="#f8efeb">
</td>
<td>
</td>
</tr>
<tr>
<td>
</td>
<td align="center" bgcolor="#f8efeb">
</td>
<td>
</td>
</tr>
<tr>
<td>
</td>
<td align="center" bgcolor="#f8efeb">
<table width="750" border="0" cellpadding="0" cellspacing="0">
<tr>
<td>
</td>
<td width="99%" height="10" bgcolor="#f8efeb">
</td>
<td>
</td>
</tr>
<tr>
<td>
</td>
<td align="center" valign="top" bgcolor="#f8efeb">
<table border="0" cellspacing="0" cellpadding="1">
<tr>
<td align="left" height="25" valign="middle">
File
</td>
<td align="center" valign="middle">
:
</td>
<td align="left" valign="middle">
<asp:FileUpload ID="fleUpload" runat="server" />
</td>
</tr>
</table>
</td>
<td>
</td>
</tr>
<tr>
<td>
</td>
<td align="center" style="height: 10px" valign="top" bgcolor="#f8efeb">
</td>
<td>
</td>
</tr>
<tr>
<td>
</td>
<td align="center" valign="top" bgcolor="#f8efeb">
<table border="0" cellspacing="0" cellpadding="0">
<tr>
<td>
<table border="0" cellspacing="0" cellpadding="1">
<tr>
<td>
</td>
<td align="left">
<asp:Button ID="btnSAVE" Width="45px" runat="server" Text="Save" OnClick="btnSAVE_Click" />
</td>
</tr>
</table>
</td>
<td style="width: 10px;">
</td>
<td>
<table border="0" cellspacing="0" cellpadding="1">
<tr>
<td>
</td>
<td align="left">
<asp:Button ID="btnCancel" runat="server" Width="58px" Text="Cancel" OnClick="btnCancel_Click" />
</td>
</tr>
</table>
</td>
</tr>
</table>
</td>
<td>
</td>
</tr>
<tr>
<td>
</td>
<td align="center" valign="top" bgcolor="#f8efeb">
</td>
<td>
</td>
</tr>
</table>
</td>
<td>
</td>
</tr>
<tr>
<td>
</td>
<td align="center" height="10" bgcolor="#f8efeb">
</td>
<td>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td align="center" style="height: 8px">
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
And The Source Code:
using System;
using System.Web;
using System.Web.UI;
using System.Data.OleDb;
using System.IO;
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnSAVE_Click(object sender, EventArgs e)
{
string strConnection = ConfigurationManager.AppSettings["defaultConnection"];
#region xlsx
if (fleUpload.PostedFile.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
{
try
{
string strDocument = fleUpload.PostedFile.FileName;
strDocument = DateTime.Now.Day.ToString() + "_" + DateTime.Now.Month.ToString() + "_" + DateTime.Now.Year.ToString() + "_" + DateTime.Now.Hour.ToString() + "_" + DateTime.Now.Minute.ToString() + "_" + DateTime.Now.Second.ToString() + strDocument.Substring(strDocument.LastIndexOf("\\") + 1);
string strAbsolutePathOfFile = Server.MapPath("ExcelSheet/" + strDocument);
fleUpload.PostedFile.SaveAs(strAbsolutePathOfFile);
string ext = Path.GetExtension(strDocument);
if (ext == ".xlsx")
{
string sCSVConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strAbsolutePathOfFile + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\";";
using (OleDbConnection connection = new OleDbConnection(sCSVConnectionString))
{
connection.ConnectionString = sCSVConnectionString;
connection.Open();
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT * FROM [Sheet1$]";
using (DbDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
sqlBulk.BulkCopyTimeout = 0;
sqlBulk.BatchSize = 2;
//You Can Use this BatchSize based on Your records You are going to insert
sqlBulk.DestinationTableName = "EXCEL_TABLE";
sqlBulk.ColumnMappings.Add("registrant_name","UNAME");
sqlBulk.WriteToServer(dr);
}
dr.Close();
}
}
tabErrMess.Visible = true;
lblMessage.Text = "All Records Inserted Successfully";
connection.Close();
}
}
}
catch (Exception ex)
{
ClientScript.RegisterClientScriptBlock(Page.GetType(), "alert", "alert('" + ex.Message + ".');", true);
return;
}
}
else
{
tabErrMess.Visible = true;
lblMessage.Text = "Please Upload Xlsx File";
}
#endregion
}
protected void btnCancel_Click(object sender, EventArgs e)
{
}
In Web.Config:
<appSettings>
<add key="defaultConnection" value="Data Source=.;Initial Catalog='database name';uid=sa;pwd=123;"/>
</appSettings>
The Sample Excel File Which I used , registrant_name is the header of that particular column.
Create A Table : 'EXCEL_TABLE' In Sql Server
CREATE TABLE EXCEL_TABLE
(
UID INT IDENTITY(1,1),
UNAME VARCHAR(200)
)
And The Design Page In Visual Studio 2008 is:
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td align="center" valign="middle" bgcolor="#f8efeb" style="height: 20px">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="46%" align="left">
<table width="250" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="5">
</td>
<td width="350">
<a href="#">
<asp:Label ID="lblTitle" runat="server" Text="Import Data"></asp:Label></a><span class="rar">>></span>
</td>
</tr>
</table>
</td>
<td align="right" valign="middle" colspan="2">
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td align="center">
</td>
</tr>
<tr>
<td align="center">
<table id="tabErrMess" visible="false" runat="server" width="380" height="25" border="0"
cellpadding="0" cellspacing="0">
<tr>
<td align="center" valign="middle">
<table width="460px" border="0" cellspacing="0" cellpadding="0">
<tr>
<td align="left" >
<asp:Label ID="lblMessage" runat="server"></asp:Label>
</td>
</tr>
</table>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td align="left" style="padding-left: 100px">
<asp:Label ID="lblTitle1" runat="server" Text="Import Data"></asp:Label>
</td>
</tr>
<tr>
<td align="center" style="height: 5px;">
</td>
</tr>
<tr>
<td align="center" valign="middle">
<table width="80%" border="0" cellpadding="0" cellspacing="0" >
<tr>
<td height="8">
</td>
<td align="center" bgcolor="#f8efeb">
</td>
<td>
</td>
</tr>
<tr>
<td>
</td>
<td align="center" bgcolor="#f8efeb">
</td>
<td>
</td>
</tr>
<tr>
<td>
</td>
<td align="center" bgcolor="#f8efeb">
<table width="750" border="0" cellpadding="0" cellspacing="0">
<tr>
<td>
</td>
<td width="99%" height="10" bgcolor="#f8efeb">
</td>
<td>
</td>
</tr>
<tr>
<td>
</td>
<td align="center" valign="top" bgcolor="#f8efeb">
<table border="0" cellspacing="0" cellpadding="1">
<tr>
<td align="left" height="25" valign="middle">
File
</td>
<td align="center" valign="middle">
:
</td>
<td align="left" valign="middle">
<asp:FileUpload ID="fleUpload" runat="server" />
</td>
</tr>
</table>
</td>
<td>
</td>
</tr>
<tr>
<td>
</td>
<td align="center" style="height: 10px" valign="top" bgcolor="#f8efeb">
</td>
<td>
</td>
</tr>
<tr>
<td>
</td>
<td align="center" valign="top" bgcolor="#f8efeb">
<table border="0" cellspacing="0" cellpadding="0">
<tr>
<td>
<table border="0" cellspacing="0" cellpadding="1">
<tr>
<td>
</td>
<td align="left">
<asp:Button ID="btnSAVE" Width="45px" runat="server" Text="Save" OnClick="btnSAVE_Click" />
</td>
</tr>
</table>
</td>
<td style="width: 10px;">
</td>
<td>
<table border="0" cellspacing="0" cellpadding="1">
<tr>
<td>
</td>
<td align="left">
<asp:Button ID="btnCancel" runat="server" Width="58px" Text="Cancel" OnClick="btnCancel_Click" />
</td>
</tr>
</table>
</td>
</tr>
</table>
</td>
<td>
</td>
</tr>
<tr>
<td>
</td>
<td align="center" valign="top" bgcolor="#f8efeb">
</td>
<td>
</td>
</tr>
</table>
</td>
<td>
</td>
</tr>
<tr>
<td>
</td>
<td align="center" height="10" bgcolor="#f8efeb">
</td>
<td>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td align="center" style="height: 8px">
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
And The Source Code:
using System;
using System.Web;
using System.Web.UI;
using System.Data.OleDb;
using System.IO;
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnSAVE_Click(object sender, EventArgs e)
{
string strConnection = ConfigurationManager.AppSettings["defaultConnection"];
#region xlsx
if (fleUpload.PostedFile.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
{
try
{
string strDocument = fleUpload.PostedFile.FileName;
strDocument = DateTime.Now.Day.ToString() + "_" + DateTime.Now.Month.ToString() + "_" + DateTime.Now.Year.ToString() + "_" + DateTime.Now.Hour.ToString() + "_" + DateTime.Now.Minute.ToString() + "_" + DateTime.Now.Second.ToString() + strDocument.Substring(strDocument.LastIndexOf("\\") + 1);
string strAbsolutePathOfFile = Server.MapPath("ExcelSheet/" + strDocument);
fleUpload.PostedFile.SaveAs(strAbsolutePathOfFile);
string ext = Path.GetExtension(strDocument);
if (ext == ".xlsx")
{
string sCSVConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strAbsolutePathOfFile + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\";";
using (OleDbConnection connection = new OleDbConnection(sCSVConnectionString))
{
connection.ConnectionString = sCSVConnectionString;
connection.Open();
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = "SELECT * FROM [Sheet1$]";
using (DbDataReader dr = command.ExecuteReader())
{
while (dr.Read())
{
SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
sqlBulk.BulkCopyTimeout = 0;
sqlBulk.BatchSize = 2;
//You Can Use this BatchSize based on Your records You are going to insert
sqlBulk.DestinationTableName = "EXCEL_TABLE";
sqlBulk.ColumnMappings.Add("registrant_name","UNAME");
sqlBulk.WriteToServer(dr);
}
dr.Close();
}
}
tabErrMess.Visible = true;
lblMessage.Text = "All Records Inserted Successfully";
connection.Close();
}
}
}
catch (Exception ex)
{
ClientScript.RegisterClientScriptBlock(Page.GetType(), "alert", "alert('" + ex.Message + ".');", true);
return;
}
}
else
{
tabErrMess.Visible = true;
lblMessage.Text = "Please Upload Xlsx File";
}
#endregion
}
protected void btnCancel_Click(object sender, EventArgs e)
{
}
In Web.Config:
<appSettings>
<add key="defaultConnection" value="Data Source=.;Initial Catalog='database name';uid=sa;pwd=123;"/>
</appSettings>
The Sample Excel File Which I used , registrant_name is the header of that particular column.
The design Page of Our Example.
After Inserting All Records In the Database the Page Displays Like This
As Our Excel Sheet Contains A Header so the header record is not inserted and the remaining records are inserted into the database successfully.
If we insert more than 50000 records at a time it takes not more than 5 sec to insert into database.
