Import dữ liệu excel vào database MVC Asp.Net

Bước 1: Tạo file excel với student id, Student Name, Mark,Grade. 

Bước 2 :Tạo project mới

Bước 3 :Create Database 

Chúng ta sẽ tạo database để import dữ liệu có tên là Leedhar_Import. 

Step 4 : 

Tạo view bằng cách tạo một controller sau đó

Nhấp phải vào Import() -> AddView -> Chọn masterpage -> Nhấn Add

        public ActionResult Import()
        {
            return View();
        }

Ta có code View Import  như sau 

@{
    ViewBag.Title = "Import";
    Layout = "~/Views/Shared/_Layout.cshtml";
}

<h2>Import</h2>
 @using (Html.BeginForm("Importexcel", "Home", FormMethod.Post, new { enctype = "multipart/form-data" }))
 {
 <table>
 <tr><td>Excel file</td><td><input  type="file"  id="FileUpload1" name="FileUpload1"/></td></tr>
 <tr><td></td><td><input  type="submit" id="Submit" name="Submit" value="Submit"/></td></tr>
 </table>
 }

Sau đó bạn tạo 1 thư mục UploadedFolder để lưu trữ file upload. 

 Tiếp theo sử dụng code dưới đây để import database.

 public ActionResult Importexcel()
        {


            if (Request.Files[" fileupload1"].contentlength=""> 0)
            {
                string extension = System.IO.Path.GetExtension(Request.Files["FileUpload1"].FileName);
                string path1 = string.Format("{0}/{1}", Server.MapPath("~/Content/UploadedFolder"), Request.Files["FileUpload1"].FileName);
                if (System.IO.File.Exists(path1))
                    System.IO.File.Delete(path1);

                Request.Files["FileUpload1"].SaveAs(path1);
                string sqlConnectionString = @"Data Source=LEEDHAR2-PC\SQLEXPRESS;Database=Leedhar_Import;Trusted_Connection=true;Persist Security Info=True";


                //Create connection string to Excel work book
                string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path1 + ";Extended Properties=Excel 12.0;Persist Security Info=False";
                //Create Connection to Excel work book
                OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
                //Create OleDbCommand to fetch data from Excel
                OleDbCommand cmd = new OleDbCommand("Select [id],[Name],[Marks],[Grade] from [Sheet1$]", excelConnection);

                excelConnection.Open();
                OleDbDataReader dReader;
                dReader = cmd.ExecuteReader();

                SqlBulkCopy sqlBulk = new SqlBulkCopy(sqlConnectionString);
                //Give your Destination table name
                sqlBulk.DestinationTableName = "StudentRecord";
                sqlBulk.WriteToServer(dReader);
                excelConnection.Close();

                // SQL Server Connection String


            }

            return RedirectToAction("Import");
        }

Bước 5 :Debug 

                       DOWNLOAD SOURCE CODE 

Import excel to database.zip