Everything is achievable through technology

Excel to SQL Bulk Copy

using System.IO;
using System.Data.OleDb;
using System.Data.Common;
using System.Data.SqlClient;

public void TransferFromSQl(string SqlTableName, string FileName)
string ConnStr = DataAccess.GetConnectionString();
String ExcelConnectionString = @”Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” + FileName + “;Extended Properties=\”Excel 12.0;HDR=Yes;IMEX=1\””;
using (OleDbConnection Conn = new OleDbConnection(ExcelConnectionString))
OleDbCommand Cm = new OleDbCommand(“select * from [Sheet1$]”, Conn);
using (DbDataReader dr = Cm.ExecuteReader())

using (OleDbTransaction TR = Conn.BeginTransaction())
using (SqlBulkCopy sqlb = new SqlBulkCopy(ConnStr, SqlBulkCopyOptions.FireTriggers))
sqlb.DestinationTableName = sqlTableName;
if (dr.FieldCount == 1)
Response.Write(“<script language=’javascript’>alert(‘Please Upload Excel file with Data…!’)</script>”);
Response.Write(“<script language=’javascript’>alert(‘File Upload Successfully Completed…!’)</script>”);
Response.Write(“<script language=’javascript’> { window.location = window.location.href }</script>”);




Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s