Pages

Monday, May 30, 2011

Uploading Excel file to SQL Server using SqlBulkCopy

string strConnection = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{

}
protected void btnUpload_Click(object sender, EventArgs e)
{
//Create connection string to Excel work book
string excelConnectionString
=@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Details.xls;
Extended Properties=""Excel 8.0;HDR=YES;""";

//Create Connection to Excel work book
OleDbConnection excelConnection =new OleDbConnection(excelConnectionString);

//Create OleDbCommand to fetch data from Excel
OleDbCommand cmd = new OleDbCommand("Select [ID],[Name],[Location] from [Detail$]",excelConnection);

excelConnection.Open();
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();

SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
sqlBulk.DestinationTableName = "Details";
sqlBulk.ColumnMappings.Add("ID", "ID");
sqlBulk.ColumnMappings.Add("Name", "Name");
sqlBulk.WriteToServer(dReader);
}

No comments:

Post a Comment