Post
Ask Question

Import Excel File To SQL Table In ASP.NET MVC

Introduction

 
Today we will learn how to import the Excel or CSV file to the SQL Server table through an ASP.Net MVC application. To insert or update the record we will use the stored procedure. Refere to my previous article SQL Bulk Insert And Update Records Using Stored Procedures to learn how to create the stored procedure for same. We will use the same stored procedure created in my previous article to import the file. 
 
Prerequisite
 
Basic knowledge of Asp.Net MVC controller, View and Entity Framework. 
 
Step 1
 
First, we will create Controller action to read the XLS file and convert the data to list. To read the Excel file we will use a packge named  Excel Data Reader.
 
Go to nuget package manager and install these two package,
  1. ExcelDataReader
  2. ExcelDataReader.DataSet.
Step 2
 
Create a model named Employee and paste the below code . 
  1. public class Employee  
  2. {  
  3.     public int Id { get; set; }  
  4.     public string EmpName { get; set; }  
  5.     public string Position { get; set; }  
  6.     public string Location { get; set; }  
  7.     public int Age { get; set; }  
  8.   
  9.     public int Salary { get; set; }  
  10. }  
Step 3
 
Create a controller named EmployeeController and Create action method named ImportFile() as below.
  1. public class EmployeeController : Controller  
  2. {  
  3.     private readonly EmployeeDbContext _dbContext;  
  4.   
  5.     public EmployeeController()  
  6.     {  
  7.         _dbContext = new EmployeeDbContext();  
  8.     }  
  9.   
  10.     // GET: Student  
  11.     public ActionResult Index()  
  12.     {  
  13.         return View();  
  14.     }  
  15.    
  16.     [HttpPost]  
  17.     public  async Task<ActionResult> ImportFile()  
  18.     {  
  19.       return View("Index");  
  20.     }  
  21. }  
Here we have created two actions named Index and ImportFile. And also I have created the instance of DbContext (Entity Framework Context) class. 
 
Step 4
 
Now we will create a method to read data from an Excel file and it will return the data in list format. Below is the sample image of CSV file that we are going to import.
 
Import Excel File To SQL Table In ASP.NET MVC
 
Note
We will use the first row of file as header or column name to map data to list. Value of first row should be same as mentioned in image because we are going to map the data based on that value only.
 
Now create a method in Employee Controller to read the CSV file and and return data into list format, paste the below code in Employee Controller.
  1. private List<Employee> GetDataFromCSVFile(Stream stream)    
  2. {    
  3.     var empList = new List<Employee>();    
  4.     try    
  5.     {    
  6.         using (var reader = ExcelReaderFactory.CreateCsvReader(stream))    
  7.         {    
  8.             var dataSet = reader.AsDataSet(new ExcelDataSetConfiguration    
  9.             {    
  10.                  ConfigureDataTable = _ => new ExcelDataTableConfiguration    
  11.                  {    
  12.                      UseHeaderRow = true // To set First Row As Column Names    
  13.                  }    
  14.             });    
  15.     
  16.             if (dataSet.Tables.Count > 0)    
  17.             {    
  18.                 var dataTable = dataSet.Tables[0];    
  19.                 foreach (DataRow objDataRow in dataTable.Rows)    
  20.                 {    
  21.                     if (objDataRow.ItemArray.All(x => string.IsNullOrEmpty(x?.ToString()))) continue;    
  22.                     empList.Add(new Employee()    
  23.                     {    
  24.                         Id = Convert.ToInt32(objDataRow["ID"].ToString()),    
  25.                         EmpName = objDataRow["Name"].ToString(),    
  26.                         Position = objDataRow["Position"].ToString(),    
  27.                         Location = objDataRow["Location"].ToString(),    
  28.                         Age = Convert.ToInt32(objDataRow["Age"].ToString()),    
  29.                         Salary = Convert.ToInt32(objDataRow["Salary"].ToString()),    
  30.                     });    
  31.                 }    
  32.             }    
  33.         }    
  34.     }    
  35.     catch (Exception)    
  36.     {    
  37.         throw;    
  38.     }         
  39.     return empList;    
  40. } 
The above method is using the nuget pacakge ExcelDataReader and reading the value from CSV file. ExcelDataReader will return the data in DataTable object. We have converted that DataTable object into List.
 
Step 5
 
We have created the method to read CSV file and get data in List format. Now we will create a file upload control to upload the file from webpage. Create a view for EmployeeController named as Index and paste the below code to create a file upload controller on webpage.
  1. @{    
  2.     ViewBag.Title = "Index";    
  3. }    
  4.     
  5. <h2>Index</h2>    
  6.     
  7. <div class="row">    
  8.     <div class="col-sm-12" style="padding-bottom:20px">    
  9.         <div class="col-sm-2">    
  10.             <span>Select File  :</span>    
  11.         </div>    
  12.         <div class="col-sm-3">    
  13.             <input class="form-control" type="file" name="importFile" id="importFile" />    
  14.         </div>    
  15.         <div class="col-sm-3">    
  16.             <input class="btn btn-primary" id="btnUpload" type="button" value="Upload" />    
  17.         </div>    
  18.     </div>    
  19. </div>    
Now we will call the ImportFile action of EmployeeController through ajax call from the webpage. And we will send the file as formdata through ajax call. Add the below code in your Index view.
  1. @section scripts{  
  2.     <script>  
  3.   
  4.         $(document).on("click", "#btnUpload", function () {  
  5.             var files = $("#importFile").get(0).files;  
  6.   
  7.             var formData = new FormData();  
  8.             formData.append('importFile', files[0]);  
  9.   
  10.             $.ajax({  
  11.                 url: '/Employee/ImportFile',  
  12.                 data: formData,  
  13.                 type: 'POST',  
  14.                 contentType: false,  
  15.                 processData: false,  
  16.                 success: function (data) {  
  17.                     if (data.Status === 1) {  
  18.                         alert(data.Message);  
  19.                     } else {  
  20.                         alert("Failed to Import");  
  21.                     }  
  22.                 }  
  23.             });  
  24.         });  
  25.    </script>  
  26. }  
On click of Upload button this code will get executed. It will read the file from file input control and will make he Ajax call file be sent as formdata to controller.
 
Step 6
 
Now we have to update our controller action to read the file and send from webpage and import it into database. Update the ImportFile action of Employee Controller as below.
  1. [HttpPost]    
  2.  public  async Task<ActionResult> ImportFile(HttpPostedFileBase importFile)    
  3.  {    
  4.      if (importFile == null) return Json(new { Status = 0, Message = "No File Selected" });    
  5.   
  6.      try    
  7.      {    
  8.          var fileData = GetDataFromCSVFile(importFile.InputStream);    
  9.   
  10.          var dtEmployee = fileData.ToDataTable();    
  11.          var tblEmployeeParameter = new SqlParameter("tblEmployeeTableType", SqlDbType.Structured)    
  12.          {    
  13.              TypeName = "dbo.tblTypeEmployee",    
  14.              Value = dtEmployee    
  15.          };    
  16.          await _dbContext.Database.ExecuteSqlCommandAsync("EXEC spBulkImportEmployee @tblEmployeeTableType", tblEmployeeParameter);    
  17.          return Json(new { Status = 1, Message = "File Imported Successfully " });    
  18.      }    
  19.      catch (Exception ex)    
  20.      {    
  21.          return Json(new { Status = 0, Message = ex.Message });    
  22.      }    
  23.  } 
Here we are getting the file in HttpPostedFileBase format sent from Ajax call. We have called the method to read CSV file on Line 8 that we have created in Step 4. In Line 10 we have called extension method to convert the list into DataTable so we can send DataTable object as Sql Parameter.

Note
Extension method ToDataTable is not available in C#, as it's my own extension method. You can find the code for that method in  Step 7 of the same article.
 
We have created Sql Parameter in Line 11 of type Structured. Type name isthe  type of our table type object that we have created in my previous article. And we have called the stored procedure at Line 17.
 
Step 7
 
Extension method to convert the List object into DataTable.
  1. public static class Extensions  
  2. {  
  3.     public static DataTable ToDataTable<T>(this List<T> items)  
  4.     {  
  5.         DataTable dataTable = new DataTable(typeof(T).Name);  
  6.   
  7.         //Get all the properties  
  8.         PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);  
  9.         foreach (PropertyInfo prop in Props)  
  10.         {  
  11.             //Defining type of data column gives proper data table   
  12.  var type = (prop.PropertyType.IsGenericType && prop.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>) ? Nullable.GetUnderlyingType(prop.PropertyType) : prop.PropertyType);
  13.             //Setting column names as Property names  
  14.             dataTable.Columns.Add(prop.Name, type);  
  15.        }  
  16.        foreach (T item in items)  
  17.        {  
  18.            var values = new object[Props.Length];  
  19.            for (int i = 0; i < Props.Length; i++)  
  20.            {  
  21.                //inserting property values to datatable rows  
  22.                values[i] = Props[i].GetValue(item, null);  
  23.            }  
  24.            dataTable.Rows.Add(values);  
  25.            }  
  26.        return dataTable;  
  27.    }  
  28. }  
Step 8
 
We have implemented the functionality, and now we will launch the application and see the output. After launching the application goes to the below URL:

https://localhost:<Port Number>/Employee/Index and you will be able to see the below output. 
 
Import Excel File To SQL Table In ASP.NET MVC
 
Now select the CSV file that we have created in Step 4 and click on upload. Once the file upload is completed you will get an alert message.
 
Thanks for reading this article. Let me know your feedback to enhance the quality of the article. 


Similar Articles