Hi Ayush,
Check this example. Now please take its reference and correct your code.
Database
I have made use of the following table Customers with the schema as follows.
You can download the database table SQL by clicking the download link below.
Download SQL file
Controller
public class HomeController : Controller
{
// GET: /Home/
public ActionResult Index()
{
return View(new DataTable());
}
[HttpPost]
public ActionResult Index(HttpPostedFileBase[] postedFile)
{
DataTable dt = GetDataTableFromExcel(postedFile);
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Constring"].ConnectionString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
sqlBulkCopy.DestinationTableName = "dbo.Customers";
sqlBulkCopy.ColumnMappings.Add("Name", "Name");
sqlBulkCopy.ColumnMappings.Add("Country", "Country");
con.Open();
sqlBulkCopy.WriteToServer(dt);
con.Close();
}
}
return View(dt);
}
private DataTable GetDataTableFromExcel(HttpPostedFileBase[] postedFiles)
{
DataTable dt = new DataTable();
for (int j = 0; j < postedFiles.Length; j++)
{
HttpPostedFileBase postedFile = postedFiles[j];
string filePath = string.Empty;
string path = Server.MapPath("~/Uploads/");
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
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;
odaExcel.Fill(dt);
connExcel.Close();
}
}
}
Directory.Delete(path, true);
}
return dt;
}
}
View
<%@ Page Language="C#" Inherits="System.Web.Mvc.ViewPage<DataTable>" %>
<%@ Import Namespace="System.Data" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Index</title>
</head>
<body>
<div>
<% using (Html.BeginForm("Index", "Home", FormMethod.Post, new { enctype = "multipart/form-data" }))
{%>
<input type="file" name="postedFile" multiple required />
<input type="submit" value="Import" />
<hr />
<table>
<tr>
<% foreach (DataColumn column in Model.Columns) {%>
<th><%=column.ColumnName%></th>
<% }%>
</tr>
<% foreach (DataRow row in Model.Rows) {%>
<tr>
<%foreach (DataColumn column in Model.Columns) {%>
<td><%=row[column.ColumnName]%></td>
<%} %>
</tr>
<% }%>
</table>
<% } %>
</div>
</body>
</html>
Screenshots
The Excel files
The Form
Database after Insert
