Thursday, March 11, 2010

COPY EXCELL SHEET DATA TO MS SQL DATA BASE WITH C#.NET

using System.Data.SqlClient;
using System.Data;
using System.Data.OleDb;

public class CopyExcellToMSSql
{
string _sourceConnectionString=;
string _destinationConnectionString;

public CopyExcellToMSSql(string sourceConnectionString,
string destinationConnectionString)
{
_sourceConnectionString =
sourceConnectionString;
_destinationConnectionString =
destinationConnectionString;
}

public void CopyTable(string Ftable,string Ttable)
{
using (OleDbConnection source =
new OleDbConnection(_sourceConnectionString))
{
string sql = string.Format("SELECT * FROM [{0}]",
Ftable);

OleDbCommand command = new OleDbCommand(sql, source);

source.Open();
IDataReader dr = command.ExecuteReader();

using (SqlBulkCopy copy =
new SqlBulkCopy(_destinationConnectionString))
{
copy.DestinationTableName = Ttable;
copy.WriteToServer(dr);
}
}
}
}

Calling above method
public void CopyData{
string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\\Documents and Settings\\Dinesh\\Desktop\\D-Garment\\work detailes.xls; Extended Properties=""Excel 8.0;HDR=Yes"";";

string sqlConnectionString ="Data Source=KIT\MYSERVER; Initial Catalog=EMS;User ID=ems; Password=ems123";

CopyExcellToMSSql cpLogic = new CopyExcellToMSSql(excelConnectionString, sqlConnectionString);
cpLogic.CopyTable("Employee$", "TempEMPLOYEE");// Employee is work sheet and "TempEMPLOYEE" is table