Cybercrux

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);
Conn.Open();
using (DbDataReader dr = Cm.ExecuteReader())
{

using (OleDbTransaction TR = Conn.BeginTransaction())
{
using (SqlBulkCopy sqlb = new SqlBulkCopy(ConnStr, SqlBulkCopyOptions.FireTriggers))
{
sqlb.DestinationTableName = sqlTableName;
sqlb.WriteToServer(dr);
if (dr.FieldCount == 1)
{
Response.Write(“<script language=’javascript’>alert(‘Please Upload Excel file with Data…!’)</script>”);
}
else
{
TR.Commit();
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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s