below code is usefull importing excel into database using bulk insert
public static DataTable GetDataTableFromExcel(string SourceFilePath)
{
try
{
DataTable dtNew = new DataTable();
DataSet ds = new DataSet();
string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + SourceFilePath + ";" +
"Extended Properties=Excel 8.0;";
using (OleDbConnection cn = new OleDbConnection(ConnectionString))
{
cn.Open();
DataTable dbSchema = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dbSchema == null || dbSchema.Rows.Count < 1)
{
throw new Exception("Error: Could not determine the name of the first worksheet.");
}
for (int i = 0; i < dbSchema.Rows.Count; i = i + 2)
{
string WorkSheetName = dbSchema.Rows[i]["TABLE_NAME"].ToString();
OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [" + WorkSheetName + "]", cn);
DataTable dt = new DataTable(WorkSheetName);
da.Fill(dt);
da.Fill(ds);
}
dtNew = ds.Tables[0];
return dtNew;
}
}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString());
}
}
protected void btnImport_Click(object sender, EventArgs e)
{
OdbcConnection connection;
SqlBulkCopy bulkCopy;
fn1 = System.IO.Path.GetFileName(fyle.PostedFile.FileName);
if ((fyle.PostedFile != null) && (fyle.PostedFile.ContentLength > 0))
{
string fn = System.IO.Path.GetFileName(fyle.PostedFile.FileName);
string[] ext = fn.Split(new char[] { '.' });
int count = ext.Length;
if (ext[count-1].ToString() == "xls")
{
fn2 = ext[0].ToString().Trim() + "-" + String.Format("{0:ddMMyyyyHHmmss}", DateTime.Now) + "." + ext[count-1].ToString().Trim();
fn1 = System.IO.Path.GetFileName(fyle.PostedFile.FileName);
string SaveLocation = Server.MapPath("EmpWorkFromIChart") + "\\" + fn2;
path = SaveLocation;
fyle.PostedFile.SaveAs(SaveLocation);
DataTable dt = GetDataTableFromExcel(path);
using (SqlConnection con =new SqlConnection(ConfigurationSettings.AppSettings.Get("GisHRoneconn")))
{
con.Open();
using (SqlBulkCopy copy = new SqlBulkCopy(con))
{
copy.ColumnMappings.Add(0, 0);
copy.ColumnMappings.Add(1, 1);
copy.ColumnMappings.Add(2, 2);
copy.ColumnMappings.Add(3, 3);
copy.ColumnMappings.Add(4, 4);
copy.ColumnMappings.Add(5, 5);
copy.ColumnMappings.Add(6, 6);
copy.ColumnMappings.Add(7, 7);
copy.ColumnMappings.Add(8, 8);
copy.ColumnMappings.Add(9, 9);
copy.ColumnMappings.Add(10, 10);
copy.ColumnMappings.Add(11, 11);
copy.ColumnMappings.Add(12, 12);
copy.ColumnMappings.Add(13, 13);
copy.ColumnMappings.Add(14, 14);
copy.ColumnMappings.Add(15, 15);
copy.ColumnMappings.Add(16, 16);
copy.ColumnMappings.Add(17, 17);
copy.ColumnMappings.Add(18, 18);
copy.DestinationTableName = "<table name="">";
copy.WriteToServer(dt);
}
}
}
}
}</table>