Import data from Excel file to SQL Server in ASP.Net MVC.

Description:

In this example we explain that how to Import data from Excel to SQL Server Database using MVC.or how to read data from excel file and push data into SQL Server database table in asp.net MVC.or import data from excel file to database table in asp.net MVC4.or import excel data to database using mvc entity framework. Or best way to import data from excel file to SQL Server database using asp.net MVC.or how to insert Excel data in SQL Server database in asp.net MVC.

Controller:

public class EmployeeController : Controller

    {

        public ActionResult Index()

        {

            return View();

        }

        [HttpPost]

        public ActionResult Index(HttpPostedFileBase postedFile)

        {

            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 dtEmployee = 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(dtEmployee);

                            connExcel.Close();

                        }

                    }

                }

                conString = ConfigurationManager.ConnectionStrings["Constring"].ConnectionString;

                using (SqlConnection con = new SqlConnection(conString))

                {

                    using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))

                    {

                        //Set the database table name.

                        sqlBulkCopy.DestinationTableName = "dbo.Employees";

                        //[OPTIONAL]: Map the Excel columns with that of the database table

                        sqlBulkCopy.ColumnMappings.Add("Id", "EmployeeId");

                        sqlBulkCopy.ColumnMappings.Add("Name", "Name");

                        sqlBulkCopy.ColumnMappings.Add("Country", "Country");

                        con.Open();

                        sqlBulkCopy.WriteToServer(dtEmployee);

                        con.Close();

                    }

                }

            }

            return View();

        }

     

    }

 View:

@{

    Layout = null;

}

<!DOCTYPE html>

<html>

<head>

    <title>Import data from Excel file to SQL Server in ASP.Net MVC</title>

</head>

<body>

    @using (Html.BeginForm("Index", "Employee", FormMethod.Post, new { enctype = "multipart/form-data" }))

    {

        <input type="file" name="postedFile"/>

        <input type="submit" value="Import"/>

    }

</body>

</html>