C#

How To Upload Records From Excel File to Database Using Stored Procedure In MVC

My previous article was about how to download records from the database in Excel files.You can check this link .Today, in this article, I will explain how to upload records from Excel files to the database using Stored Procedure, and also check all types of validations. I will check required filed validation, Excel format validation, null validation, if the employee number is unique (here I used an example of employee records) etc.

Step1

First, let's create a table.

ASP.NET

Then, let's create stored procedure. In this stored procedure, we have created a procedure for inserting records and checking for duplicate employee numbers.

  1. Create proc usp_InsertNewEmployeeDetails  
  2. (  
  3. @EmployeeNo int = null,  
  4. @FirstName varchar(50) = null,  
  5. @LastName varchar(50) = null,  
  6. @DateOfBirth datetime = null,  
  7. @Address varchar(50) = null,  
  8. @MobileNo varchar(200) = null,  
  9. @PostelCode varchar(50) = null,  
  10. @EmailId  varchar(50) = null  
  11. )  
  12. as  
  13. begin  
  14.   
  15.   IF EXISTS(select EmployeeNo from EmployeeInfo where EmployeeNo=@EmployeeNo)  
  16.   return 0  
  17.   ELSE  
  18.   begin  
  19. Insert into EmployeeInfo(  
  20. EmployeeNo,FirstName,LastName,  
  21. DateOfBirth,Address,MobileNo,PostelCode,  
  22. EmailId  
  23. )  
  24. values  
  25. (  
  26. @EmployeeNo,@FirstName,@LastName,  
  27. @DateOfBirth,@Address,@MobileNo,@PostelCode,  
  28. @EmailId)  
  29. end  
  30. end  

Step2

Now, we have to add the table in an MVC application. I have used Entity Framework with LINQ query.

  • For that, I created an MVC application and went through, File->New ->Web application ->select MVC ->OK.
  • Go to Model folder ->Right click -> Add -> New item -> ADO.NET Entity Data Model -> click Add -> select database first approach->Click Next.
  • Select "New Connection" and give the connection details, then select database ->Click OK.
  • Choose tables and stored procedure and click OK.

ASP.NET
Step3

Now, we have to download LinqToExcel.dll file; so for this, right click on project file. After that, select 'Manage NueGet Package' and search for LinqToExcel. When found, download this dll.

ASP.NET

Step4

Now, we have to create a Controller class. First, put a namespace.

using LinqToExcel;

Then, create an action method for View.

  1. public ActionResult ExcelUpload()  
  2.      {  
  3.          return View();  
  4.      }  

Now, we have to create a View - right click the action name, select View, and click OK.

  1. <div align="center">  
  2.     @if (ViewBag.Message != null)  
  3.     {  
  4.         <span class="alert alert-warning"> @ViewBag.Message</span>  
  5.     }  
  6.   
  7.     <div align="right" class="btn btn-default">  
  8.         @using (Html.BeginForm("UploadExcel", "ExcelDemo", FormMethod.Post, new { @enctype = "multipart/form-data" }))  
  9.         {  
  10.             <input type="file" id="fileUpload" class="btn btn-primary" name="FileUpload" /><br />  
  11.             <input type="submit" class="btn btn-primary" name="UploadNewEmployee" id="fileUploadExcel" value="Upload New Employee Details" />  
  12.         }  
  13.     </div>  
  14. </div>  

Here, I have used sweetAlert to check the required field validation of file upload. So first, we have to download library for this and add the following code.

  1. <link href="~/Scripts/sweetalert.css" rel="stylesheet" />  
  2. <script src="~/Scripts/jquery-1.10.2.js"></script>  
  3. <script src="~/Scripts/sweetalert.js"></script>  
  4. <script>  
  5.   
  6.     $('#fileUploadExcel').click(function (e) {  
  7.         if ($('#fileUpload').val() === "")   
  8.         {  
  9.             sweetAlert("Oops...!!!", "Please select file", "error");  
  10.             return false;  
  11.         }  
  12.   
  13.     });  
  14.   
  15. </script>  

Click without selecting a file, then see the output.

ASP.NET

Step5

Now, let's write code for insert recods from Excel file to Controller. In this, first I checked Excel file fomat.

  1. if (FileUpload.ContentType == "application/vnd.ms-excel" || FileUpload.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")  
  2. {  
  3.     string filename = FileUpload.FileName;  
  4.   
  5.     if (filename.EndsWith(".xlsx"))  
  6.     {  
  7.                    }  

Next I checked Uploaded Excel file

  1. string targetpath = Server.MapPath("~/DetailFormatInExcel/");  
  2. FileUpload.SaveAs(targetpath + filename);  
  3. string pathToExcelFile = targetpath + filename;  
  4. string sheetName = "Sheet1";  

ASP.NET

Then I checked if employee number is null or not

  1. if (a.EmployeeNo != null)  
  2. {  
  3.                     }  

 I created a separate method for postdata

  1. public int PostExcelData(int employeeNo,string firstName, string lastName, DateTime? dateOfBirth, string address,string mobileNo,string postelCode, string emailId)  
  2. {  
  3.     EmployeeDBEntities DbEntity = new EmployeeDBEntities();  
  4.     var InsertExcelData = DbEntity.usp_InsertNewEmployeeDetails(employeeNo,firstName, lastName, dateOfBirth,address,mobileNo,postelCode, emailId);  
  5.   
  6.     return InsertExcelData;  
  7. }  

I called the postdata method

  1. int resullt = PostExcelData(a.EmployeeNo, a.FirstName, a.LastName, myBirthdate, a.Address, a.MobileNo, a.PostelCode, a.EmailId);  
  2. if (resullt <= 0)  
  3. {  
  4.     data = "Hello User, Found some duplicate values! Only unique employee number has inserted and duplicate values(s) are not inserted";  
  5.     ViewBag.Message = data;  
  6.     continue;  

Then I checked for duplicate records also. It will automatically skip the duplicate records.

So now, our final code will be like below.

  1. [HttpPost]  
  2.      public ActionResult UploadExcel(EmployeeInfo objEmpDetail, HttpPostedFileBase FileUpload)  
  3.      {  
  4.   
  5.          EmployeeDBEntities objEntity = new EmployeeDBEntities();  
  6.          string data = "";  
  7.          if (FileUpload != null)  
  8.          {  
  9.              if (FileUpload.ContentType == "application/vnd.ms-excel" || FileUpload.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")  
  10.              {  
  11.                  string filename = FileUpload.FileName;  
  12.   
  13.                  if (filename.EndsWith(".xlsx"))  
  14.                  {  
  15.                      string targetpath = Server.MapPath("~/DetailFormatInExcel/");  
  16.                      FileUpload.SaveAs(targetpath + filename);  
  17.                      string pathToExcelFile = targetpath + filename;  
  18.   
  19.                      string sheetName = "Sheet1";  
  20.   
  21.                      var excelFile = new ExcelQueryFactory(pathToExcelFile);  
  22.                      var empDetails = from a in excelFile.Worksheet<EmployeeInfo>(sheetName) select a;  
  23.                      foreach (var a in empDetails)  
  24.                      {  
  25.                          if (a.EmployeeNo != null)  
  26.                          {  
  27.   
  28.                              DateTime? myBirthdate = null;  
  29.   
  30.   
  31.                              if (a.MobileNo.Length > 12)  
  32.                              {  
  33.                                  data = "Phone number should be 10 to 12 disit";  
  34.                                  ViewBag.Message = data;  
  35.   
  36.                              }  
  37.   
  38.                              myBirthdate = Convert.ToDateTime(a.DateOfBirth);  
  39.   
  40.   
  41.                              int resullt = PostExcelData(a.EmployeeNo, a.FirstName, a.LastName, myBirthdate, a.Address, a.MobileNo, a.PostelCode, a.EmailId);  
  42.                              if (resullt <= 0)  
  43.                              {  
  44.                                  data = "Hello User, Found some duplicate values! Only unique employee number has inserted and duplicate values(s) are not inserted";  
  45.                                  ViewBag.Message = data;  
  46.                                  continue;  
  47.   
  48.                              }  
  49.                              else  
  50.                              {  
  51.                                  data = "Successful upload records";  
  52.                                  ViewBag.Message = data;  
  53.                              }  
  54.                          }  
  55.   
  56.                          else  
  57.                          {  
  58.                              data = a.EmployeeNo + "Some fields are null, Please check your excel sheet";  
  59.                              ViewBag.Message = data;  
  60.                              return View("ExcelUpload");  
  61.                          }  
  62.   
  63.                      }  
  64.                  }  
  65.   
  66.                  else  
  67.                  {  
  68.                      data = "This file is not valid format";  
  69.                      ViewBag.Message = data;  
  70.                  }  
  71. urn View("ExcelUpload");  
  72.              }  
  73.              else  
  74.              {  
  75.   
  76.                  data = "Only Excel file format is allowed";  
  77.   
  78.                  ViewBag.Message = data;  
  79.                  return View("ExcelUpload");  
  80.   
  81.              }  
  82.   
  83.          }  
  84.          else  
  85.          {  
  86.   
  87.              if (FileUpload == null)  
  88.              {  
  89.                  data = "Please choose Excel file";  
  90.              }  
  91.   
  92.              ViewBag.Message = data;  
  93. urn View("ExcelUpload");  
  94.          }  
  95.      }  
  96.        
  97.      public int PostExcelData(int employeeNo,string firstName, string lastName, DateTime? dateOfBirth, string address,string mobileNo,string postelCode, string emailId)  
  98.      {  
  99.          EmployeeDBEntities DbEntity = new EmployeeDBEntities();  
  100.          var InsertExcelData = DbEntity.usp_InsertNewEmployeeDetails(employeeNo,firstName, lastName, dateOfBirth,address,mobileNo,postelCode, emailId);  
  101.   
  102.          return InsertExcelData;  
  103.      }  

So finally, the coding part is done. Now,  we will check if the  Excel file uploads or not.

Now, I have created an Excel file and filled in some records.

ASP.NET


ASP.NET

After uploading this Excel file, we can check in the database to see whether our records are inserted or not.

ASP.NET

That's it. Thanks for reading this article. I hope you enjoyed it.

Up Next
    Ebook Download
    View all
    Learn
    View all
    Membership not found