Tags

, ,

string sSourceConstr = String.Format(@”Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=’Excel 12.0;'”, Server.MapPath(“Excel”) + “//” + “test.xlsx”);
string sDestConstr = ConfigurationManager.ConnectionStrings[“TestConnection”].ConnectionString;
OleDbConnection sSourceConnection = new OleDbConnection(sSourceConstr);
using (sSourceConnection)
{
string sql = string.Format(“select * from [{0}$]”, “data”);
OleDbCommand command = new OleDbCommand(sql, sSourceConnection);
sSourceConnection.Open();
using (OleDbDataReader dr = command.ExecuteReader())
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sDestConstr))
{
bulkCopy.DestinationTableName = “Emp”;
//You can mannualy set the column mapping by the following way.
bulkCopy.ColumnMappings.Add(“Name”, “Name”);
bulkCopy.ColumnMappings.Add(“Class”, “Class”);
bulkCopy.ColumnMappings.Add(“RollNo”, “RollNo”);
bulkCopy.WriteToServer(dr);
}
}
}

Advertisements