Hi mahesh213,
Check this example. Now please take its reference and correct your code.
Namespaces
using System.Collections.Generic;
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()
{
// Populate the Table list from Database.
List<SelectListItem> li = new List<SelectListItem>();
li.Add(new SelectListItem { Text = "Select", Value = "0" });
li.Add(new SelectListItem { Text = "Country", Value = "1" });
li.Add(new SelectListItem { Text = "Employee", Value = "2" });
ViewData["Tables"] = li;
return View();
}
[HttpPost]
public ActionResult Index(HttpPostedFileBase postedFile, FormCollection formCollection)
{
string tableName = formCollection["TableName"];
if (tableName.ToLower() != "select")
{
string filePath = string.Empty;
if (postedFile != null)
{
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;
}
DataTable dt = new DataTable();
conString = string.Format(conString, filePath);
using (OleDbConnection connExcel = new OleDbConnection(conString))
{
using (OleDbCommand cmdExcel = new OleDbCommand())
{
using (OleDbDataAdapter odaExcel = new OleDbDataAdapter())
{
cmdExcel.Connection = connExcel;
//Get the name of First Sheet.
connExcel.Open();
DataTable dtExcelSchema;
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
connExcel.Close();
//Read Data from First Sheet.
connExcel.Open();
cmdExcel.CommandText = "SELECT * From [" + sheetName + "]";
odaExcel.SelectCommand = cmdExcel;
odaExcel.Fill(dt);
connExcel.Close();
}
}
}
if (dt.Rows.Count > 0)
{
conString = ConfigurationManager.ConnectionStrings["Constring"].ConnectionString;
using (SqlConnection con = new SqlConnection(conString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
//Set the database table name based on the DropDownList selected value.
sqlBulkCopy.DestinationTableName = "dbo." + tableName;
con.Open();
//sqlBulkCopy.WriteToServer(dt);
con.Close();
}
}
}
}
}
return View();
}
}
View
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Index</title>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript">
$(function () {
$("#ddlTableId").on("change", function () {
$("#hfTableName").val($(this).find("option:selected").text());
});
});
</script>
</head>
<body>
<div>
<%using (Html.BeginForm("Index", "Home", FormMethod.Post, new { enctype = "multipart/form-data" }))
{ %>
<%:Html.DropDownList("ddlTableId", ViewData["Tables"] as List<SelectListItem>)%>
<%:Html.Hidden("TableName", null, new { @id = "hfTableName" })%>
<input type="file" name="postedFile" />
<input type="submit" value="Import" />
<%} %>
</div>
</body>
</html>
