Hi Ayush,
In this example i have read each excel in DataTable and return all the DataTable in DataSet.
Then looping through the DataTable present in the DataSet inserting to database using SqlBulkCopy class WriteToServer method.
For table maping i have assigned DestinationTableName i have used the same name in the excel and passed the excel name to the DestinationTableName property.
And removed the ColumnMappings from the code. If you want to add ColumnMappings then check with condition.
Check this example. Now please take its reference and correct your code.
Namespaces
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
Controller
public class HomeController : Controller
{
// GET: /Home/
public ActionResult Index()
{
return View();
}
[HttpPost]
public ActionResult Index(HttpPostedFileBase[] postedFile)
{
DataSet ds = GetDataTablesFromExcel(postedFile);
for (int i = 0; i < ds.Tables.Count; i++)
{
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Constring"].ConnectionString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
sqlBulkCopy.DestinationTableName = ds.Tables[0].TableName;
con.Open();
sqlBulkCopy.WriteToServer(ds.Tables[0]);
con.Close();
}
}
}
return View();
}
private DataSet GetDataTablesFromExcel(HttpPostedFileBase[] postedFiles)
{
DataSet ds = new DataSet();
string path = Server.MapPath("~/Uploads/");
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
for (int j = 0; j < postedFiles.Length; j++)
{
HttpPostedFileBase postedFile = postedFiles[j];
string filePath = string.Empty;
filePath = path + Path.GetFileName(postedFile.FileName);
string extension = Path.GetExtension(postedFile.FileName);
postedFile.SaveAs(filePath);
string conString = string.Empty;
switch (extension)
{
case ".xls": //Excel 97-03.
conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx": //Excel 07 and above.
conString = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
break;
}
conString = string.Format(conString, filePath);
using (OleDbConnection connExcel = new OleDbConnection(conString))
{
using (OleDbCommand cmdExcel = new OleDbCommand())
{
using (OleDbDataAdapter odaExcel = new OleDbDataAdapter())
{
cmdExcel.Connection = connExcel;
connExcel.Open();
DataTable dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
connExcel.Close();
string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
connExcel.Open();
cmdExcel.CommandText = "SELECT * From [" + sheetName + "]";
odaExcel.SelectCommand = cmdExcel;
DataTable dt = new DataTable();
dt.TableName = Path.GetFileNameWithoutExtension(postedFile.FileName);
odaExcel.Fill(dt);
connExcel.Close();
ds.Tables.Add(dt);
}
}
}
}
Directory.Delete(path, true);
return ds;
}
}
View
<% using (Html.BeginForm("Index", "Home", FormMethod.Post, new { enctype = "multipart/form-data" }))
{%>
<input type="file" name="postedFile" multiple required />
<input type="submit" value="Import" />
<% } %>
