Uploading Multiple Records Via Excel Upload in Database Using EntityFramework 6.0 in ASP.Net MVC4

This article explains how to insert multiple records into a database using an Excel upload. When working on a project there was a requirement for me to upload an Excel file, let's say with the number of records up to 50000 per sheet. But while uploading the Excel file there were certain validations that needs to be done and if any of the records fail then the error message needs to be displayed for that respective row of the Excel on the page and none of the records needs to be saved inside the database table.

Validations could be in any sense, for example:

1. The age of all employees needs to be >= 18 and <= 60
2. The age column should not have a non-numeric value
3. There were certain columns that were mandatory, if the value is not filled in that column of the Excel then the user should be notified of the error. Like name of the Employee should not be null.
4. Checking thar a date value is in the format of DD/MM/YYYY
5. and so on

When uploading this Excel file, if the preceding validations are successfully satisfied then the data needs to be saved in the database against a specific batch id that is basically a unique number to identify the records inserted within that specific batch and when they all were inserted. Also a special attention needs to be provided on the Excel column headings that we provided to the user for data entry purposes. Let's say there are 10 columns in the Excel and if the user changes the order of the Excel column heading or if they delete some column or changes the name of the column, this validation also needs to be performed against the uploaded file. For performing this validation we have made use of an ExcelStructure Table that will have Excel fields/columns stored with their respective sizes, types, parameters and whether these fields/columns are mandatory or not. The following is the structure of the ExcelStructure Table. Remember we are using EntityFramework with the Code First Approach. But just for demo/looking purposes our SQL table will have the following query. 

CREATE TABLE [dbo].[ExcelStructure]
(
    [Row_ID]          INT            IDENTITY (1, 1) NOT NULL,
    [File_ID]         INT            NOT NULL,
    [File_Name]       NVARCHAR (MAX) NULL,
    [Field_Name]      NVARCHAR (MAX) NULL,
    [Field_Data]      NVARCHAR (MAX) NULL,
    [Active_Flag]     INT            NOT NULL,
    [Row_No]          INT            NOT NULL,
    [Field_Delimeter] NVARCHAR (MAX) NULL,
    CONSTRAINT [PK_dbo.ExcelStructure] PRIMARY KEY CLUSTERED ([Row_ID] ASC)
);

Now let's move to the code. Here the technology that we are using is ASP.NET MVC4 and EntityFramework 6.0. I'm naming my solution name ImportFromExcel that will have two projects, one would be of type Class Library with the name ImportFromExcel.Domain and the other would be our MVC4 project with the name ImportFromExcel.WebUI with basic as its type. Our ImportFromExcel.Domain project will have the entire model and DbContext classes we are following here the separation of concerns between model and WebApplication. Install the EntityFramework Package inside the ImportFromExcel.Domain and ImportFromExcel.WebUI Project using the Package Manager Console by typing the command as:

Install-package EntityFramework

Note: Make sure you install it in both the projects otherwise a version issue might occur.

We need to add the reference to System.ComponentModel.DataAnnotations namespace. We have added creation folders to our Domain class library project and these folders contain several classes and interface that we'll require to query our database or manipulate the database. Here is the hierarchy of the ImportFromExcel.Domain project application:



Here are the definitions of the files.

Employee.cs

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace ImportFromExcel.Domain.Entities
{
    [Table("Employee")]
    public class Employee
    {
        [Key]
        public int EmployeeID { get; set; }//mvc convention by default it will make it as primary key with autogenerated feature turned on
        [Required(ErrorMessage = "Name is Required")]
        public string Name { get; set; }
        [Required(ErrorMessage = "Address is Required")]
        public string Address { get; set; }
        public string DOB { get; set; }
        public int Age { get; set; }
        public string Email { get; set; }
    }
}

ExcelStructure.cs

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace ImportFromExcel.Domain.Entities
{
    [Table("ExcelStructure")]
    public class ExcelStructure
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Row_ID { get; set; }
        public int File_ID { get; set; }
        public string File_Name { get; set; }
        public string Field_Name { get; set; }
        public string Field_Data { get; set; }
        public int Active_Flag { get; set; }
        public int Row_No { get; set; }
        public string Field_Delimeter { get; set; }
    }
}

In order to reduce the dependency between higher end modules and lower end modules we implemented an interface that will help us to do loose coupling between the various components.

IEmployeeRepository.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ImportFromExcel.Domain.Entities;
namespace ImportFromExcel.Domain.Abstract
{
    public interface IEmployeeRepository
    {
        IQueryable<Employee> Employee { get; }
        IQueryable<ExcelStructure> ExcelStructure { get; }
        void SaveEmployee(IList<Employee> lstEmployees);
    }
}

EFDBContext.cs: This is our DbContext class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ImportFromExcel.Domain.Entities;
namespace ImportFromExcel.Domain.Abstract
{
    public interface IEmployeeRepository
    {
        IQueryable<Employee> Employee { get; }
        IQueryable<ExcelStructure> ExcelStructure { get; }
        void SaveEmployee(IList<Employee> lstEmployees);
    }
}

EmployeeRepository.cs

using System;
using System.Collections.Generic;
using System.Linq;
using ImportFromExcel.Domain.Abstract;
namespace ImportFromExcel.Domain.Concrete
{
    public class EmployeeRepository : IEmployeeRepository
    {
        EFDbContext context = new EFDbContext();
        public IQueryable<Entities.Employee> Emp
        {
            get { return context.Employee; }
        }
        public IQueryable<Entities.ExcelStructure> ExcelStructure
        {
            get { return context.ExcelStructure; }
        }
        public void SaveEmployee(IList<Entities.Employee> lstEmployees)
        {
            try
            {
                context.Employee.AddRange(lstEmployees);
                context.SaveChanges();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}

Inside the Concrete folder we have a class named ExcelStructureInitializer.cs that basically contains the code for the records that needs to be stored in the Database ExcelStructure Table when we run the application for the first time or even when the model changes or the database is dropped and recreated by the entityFramework. This class inherits from DropCreateDatabaseIfModelChanges class in order to maintain the synchronization between the Model and the EntityFramework Database. The following is the definition of the class.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ImportFromExcel.Domain.Entities;
using System.Data.Entity;
namespace ImportFromExcel.Domain.Concrete
{
    public class ExcelStructureInitializer : DropCreateDatabaseIfModelChanges<EFDbContext>
    {
        protected override void Seed(EFDbContext context)
        {
            //base.Seed(context);
            var excelStructure = new List<ExcelStructure>()
            {
                new ExcelStructure(){ File_ID=1, Field_Name="Name", Field_Data="Name|Address|DOB|Age|Email", Active_Flag=1, Row_No=1, File_Name="EmployeeExcel", Field_Delimeter="|"},
                new ExcelStructure(){ File_ID=1, Field_Name="DataType", Field_Data="S|S|D|I|S", Active_Flag=1, Row_No=2, File_Name="EmployeeExcel", Field_Delimeter="|"},
                new ExcelStructure(){ File_ID=1, Field_Name="Size", Field_Data="50|100|20|10|100", Active_Flag=1, Row_No=3, File_Name="EmployeeExcel", Field_Delimeter="|"},
                new ExcelStructure(){ File_ID=1, Field_Name="Mandatory", Field_Data="Y|Y|N|N|N", Active_Flag=1, Row_No=4, File_Name="EmployeeExcel", Field_Delimeter="|"},
                new ExcelStructure(){ File_ID=1, Field_Name="Param", Field_Data="@name|@add|@dob|@age|@email", Active_Flag=1, Row_No=5, File_Name="EmployeeExcel", Field_Delimeter="|"},
            };
            context.ExcelStructure.AddRange(excelStructure);
            context.SaveChanges();
        }
    }
}

The one highlighted in the yellow color in the code above will be the Excel Column heading with their Type, size, and mandatory or not. And the one highlighted in the light blue color are the name through that we can refer to these fields in the code file. Every value is separated using the “|” character.

All these records will be added to your Excel structure table when you run the application. Here in this table we specified the Excel column hierarchy and the maximum value that the column can contain, also whether they are mandatory or not and their datatype. For Datatype here I'm using the convention of S for String, I for integers, D for DateTime, DO for Double and so on but you can follow your own conventions.

And we are setting the reference of this class inside the Global.asax file that is within the ImportFromExcel.WebUI application. Here is the line that we set up in the Global.asax file.

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Web;
using System.Web.Http;
using System.Web.Mvc;
using System.Web.Optimization;
using System.Web.Routing;
using ImportFromExcel.Domain.Concrete;
using ImportFromExcel.WebUI.Infrastructure;
namespace ImportFromExcel.WebUI
{
    // Note: For instructions on enabling IIS6 or IIS7 classic mode,
    // visit http://go.microsoft.com/?LinkId=9394801
    public class MvcApplication : System.Web.HttpApplication
    {
        protected void Application_Start()
        {
            AreaRegistration.RegisterAllAreas();
            Database.SetInitializer<EFDbContext>(new ImportFromExcel.Domain.Concrete.ExcelStructureInitializer());
            WebApiConfig.Register(GlobalConfiguration.Configuration);
            FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
            RouteConfig.RegisterRoutes(RouteTable.Routes);
            ControllerBuilder.Current.SetControllerFactory(new NinjectControllerFactory());
            BundleConfig.RegisterBundles(BundleTable.Bundles);
        }
    }
}

For the code of the preceding files kindly download the article.

Now we'll be moving towards creating our front end, in other words the ImportFromExcel.WebUI application where we'll create a view and controllers that will communicate with the preceding domain model that we created above.

Note:

1. Here for Dependency Injection we used Ninject. Also for the full explanation of Dependency Injection you can refer to the following URL:
   
   ASP.NET MVC 4 Dependency Injection
   
   We injected the Ninject controller using the Global.asax file. The one marked with light blue as background in the Global.asax file.
    
2. For Excel reading and writing I'm using Bytescout.Spreadsheet.dll
3. Also add the reference of the ImportFromExcel.Domain.dll to the ImportFromExcel.WebUI project. So that we can refer to the classes.

Here is the hierarchy of the ImportFromExcel.WebUI



Add a connection string to the Web.Config file of the ImportFromExcel.WebUI like the following:

<connectionStrings>
    <!--<add name="DefaultConnection" providerName="System.Data.SqlClient" connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=aspnet-ImportFromExcel.WebUI-20140408112057;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnet-ImportFromExcel.WebUI-  20140408112057.mdf" />-->
    <add name="EFDbContext" providerName="System.Data.SqlClient" connectionString="Data Source=(localDb)\v11.0;Initial Catalog=MemberUpload;Integrated Security=true;"/>
</connectionStrings>

Now to create a helper file that will allow us to validate and upload the Excel content. We'll specify our validation rules inside this file. Create a class with the name of APIHelper.cs inside the AppHelper folder of your WebUI project.

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Web;
using Bytescout.Spreadsheet;
using ImportFromExcel.Domain.Abstract;
using ImportFromExcel.Domain.Entities;
namespace ImportFromExcel.WebUI.AppHelper
{
    public class APIHelper
    {
        private IEmployeeRepository repository;
        public APIHelper(IEmployeeRepository repository)
        {
            this.repository = repository;
        }
        /// <summary>
        /// Read the Excel File
        /// </summary>
        /// <param name="filePath">File Path</param>
        /// <param name="isHDR">Header File</param>
        /// <returns>returns DataSet</returns>
        public DataSet ReadExcelFile(string filePath, out string msg, string isHDR = "Yes")
        {
            string details = string.Empty;
            //retrieve the Excel structure for a particular File. in our case EmployeeStructure was the file name whose data needs to copied
            List<ExcelStructure> lstEntityTable = repository.ExcelStructure.Where(
                         x => x.File_Name.Equals("EmployeeExcel", StringComparison.InvariantCultureIgnoreCase)).
                OrderBy(x => x.Row_No).ToList();
            List<string> lstFieldType = new List<string>();
            //getting the type of all field
            lstFieldType.AddRange(lstEntityTable[1].Field_Data.ToString().Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries));
            //Get the ExcelStructure Stored in Database
            DataTable dt = CreateDataTableFromList(lstEntityTable);
            //Get the Records Stored in ExcelFile Uploaded by user
            DataSet ds = GetDataFromMultipleSheets(filePath, lstFieldType);
            string fileName = string.Empty;
            //perform validation on every dataTable inside DataSet
            for (byte i = 0; i < ds.Tables.Count; i++)
            {
                if (ds.Tables[i].Rows.Count > 0)
                {
                    details = ValidateExcelHeadings(dt, ds.Tables[i]);
                    ds.DataSetName = filePath.Substring(filePath.LastIndexOf("\\") + 1);
                }
            }
            msg = details;
            return ds;
        }
        /// <summary>
        /// This function is used for copying the List Excel Structure  Data  to DataTable
        /// </summary>
        /// <param name="lstExcel">List<ExcelStructure></param>
        /// <returns>returns DataTable</returns>
        private DataTable CreateDataTableFromList(List<ExcelStructure> lstExcel)
        {
            DataTable dt = new DataTable();
            PropertyInfo[] props = typeof(ExcelStructure).GetProperties(BindingFlags.Public | BindingFlags.Instance);
            foreach (PropertyInfo prop in props)
            {
                if (!prop.Name.Equals("Row_ID"))
                    dt.Columns.Add(prop.Name);
            }
            for (int i = 0; i < lstExcel.Count; i++)
            {
                DataRow dr = dt.NewRow();
                dr[0] = lstExcel[i].File_ID;
                dr[1] = lstExcel[i].File_Name;
                dr[2] = lstExcel[i].Field_Name;
                dr[3] = lstExcel[i].Field_Data;
                dr[4] = lstExcel[i].Active_Flag;
                dr[5] = lstExcel[i].Row_No;
                dr[6] = lstExcel[i].Field_Delimeter;
                dt.Rows.Add(dr);
            }
            return dt;
        }
        /// <summary>
        /// This function is used for reading Data From Multiple sheet and adding those worksheet data as
        /// DataTable inside a DataSet
        /// </summary>
        /// <param name="filePath">FilePath</param>
        /// <param name="lstDateType">DataType of the Fields</param>
        /// <returns>returns DataSet</returns>
        private DataSet GetDataFromMultipleSheets(string filePath, List<string> lstDateType)
        {
            DataSet ds = new DataSet();
            Spreadsheet document = new Spreadsheet(filePath);
            int totalColumn = 0;
            try
            {
                //iterate over the workbook worksheets and add those to the DataSet Tables.
                for (byte i = 0; i < document.Workbook.Worksheets.Count; i++)
                {
                    Worksheet sheet = document.Workbook.Worksheets[i];
                    totalColumn = sheet.NotEmptyColumnMax + 1;
                    //set the DataTable Name to the SheetName specified
                    DataTable dtTable = ds.Tables.Add(sheet.Name);
                    //create the dataTable with the specified column of uploaded Excel
                    for (int col = 0; col < totalColumn; col++)
                    {
                        try
                        {
                            //Add the columns to the DataTable
                            dtTable.Columns.Add(sheet.Cell(0, col).Value.ToString().Trim());
                        }
                        catch (Exception ex)
                        {
                            throw ex;
                        }
                    }
                    //iterate over the worksheet records until the last used rows
                    //here we are iterating from row 1 because we are assuming row 0 is an header row
                    for (int row = 1; row <= sheet.UsedRangeRowMax; row++)
                    {
                        object[] data = new object[totalColumn];
                        for (int column = 0; column < totalColumn; column++)
                        {
                            try
                            {
                                //check whether the value is of DateTime
                                if (lstDateType[column].Equals("D"))
                                {
                                    try
                                    {
                                        data[column] = sheet.Cell(row, column).ValueAsDateTime.ToShortDateString().Trim();
                                    }
                                    catch (Exception)
                                    {
                                        data[column] = sheet.Cell(row, column).Value.ToString().Trim();
                                    }
                                }
                                else
                                {
                                    data[column] = sheet.Cell(row, column).Value.ToString().Trim();
                                }
                            }
                            catch (Exception ex)
                            {
                                data[column] = sheet.Cell(row, column).Value;
                            }
                        }
                        dtTable.Rows.Add(data);
                    }
                }
                document.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }
        /// <summary>
        /// This function is used for validating the Excel headings against those stored in the Database.
        /// </summary>
        /// <param name="dtUploadedExcel">Uploaded Excel Converted as DataTable</param>
        /// <param name="dtExcelStructure">Excel Structure Converted as DataTable</param>
        ///  /// <returns>returns string message</returns>
        private string ValidateExcelHeadings(DataTable dtExcelStructure, DataTable dtUploadedExcel)
        {
            string error = "";
            int number = 0;
            try
            {
                //read the TableHeadings from the ExcelStructure
                string strFieldValue = (string)dtExcelStructure.AsEnumerable().Where(x => x["Field_Name"].Equals("Name")).
                        Select(x => x["Field_Data"]).First();
                string[] strFieldHeadings = strFieldValue.Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
                string[] strFieldType = ((string)dtExcelStructure.AsEnumerable().Where(x => x["Field_Name"].Equals("DataType")).
                    Select(x => x["Field_Data"]).First()).Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
                string[] strFieldSize = ((string)dtExcelStructure.AsEnumerable().Where(x => x["Field_Name"].Equals("Size")).
                    Select(x => x["Field_Data"]).First()).Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
                string[] strFieldParam = ((string)dtExcelStructure.AsEnumerable().Where(x => x["Field_Name"].Equals("Param")).
                    Select(x => x["Field_Data"]).First()).Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
                string[] strFieldMandatory = ((string)dtExcelStructure.AsEnumerable().Where(x => x["Field_Name"].Equals("Mandatory")).
                    Select(x => x["Field_Data"]).First()).Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
                //Checking the upload Excel column count with that of the ExcelStructure Table Column Count
                if (strFieldHeadings.Length == dtUploadedExcel.Columns.Count)
                {
                    for (int i = 0; i < strFieldHeadings.Length; i++)
                    {
                        //Validate Excel Heading
                        if (strFieldHeadings[i].ToLower() != dtUploadedExcel.Columns[i].ToString().ToLower())
                        {
                            error += dtUploadedExcel.Columns[i].ToString() + " Mismach Column, it has to be " + strFieldHeadings[i] + "\\n";
                        }
                    }
                    if (error != "")
                        return error;
                }
                else
                {
                    error = "Mismach Count of Excel Column";
                    return error;
                }
                if (error == "")
                {
                    //add an extra column named Error inside the DataTable which contains the excelUploadedData
                    dtUploadedExcel.Columns.Add("Error");
                    for (int i = 0; i < dtUploadedExcel.Rows.Count; i++)
                    {
                        error = "";
                        for (int j = 0; j < strFieldHeadings.Length; j++)
                        {
                            //Validate Mandetory Fields/Columns
                            if (strFieldMandatory[j].ToUpper().Trim().Equals("Y", StringComparison.InvariantCultureIgnoreCase))
                            {
                                if (dtUploadedExcel.Rows[i][j].ToString() == null || dtUploadedExcel.Rows[i][j].ToString() == "")
                                {
                                    error += dtUploadedExcel.Columns[j].ToString() + " Should not be Null in line " + (i + 1) + ";";
                                }
                            }
                            //Adding custom validations like lets say the age of employee should be >18 less than or equal to 60 years
                            if (strFieldHeadings[j].ToUpper().Trim().Equals("Age", StringComparison.InvariantCultureIgnoreCase))
                            {
                                if (dtUploadedExcel.Rows[i][j].ToString() != null || dtUploadedExcel.Rows[i][j].ToString() != "")
                                {
                                    if (!int.TryParse(dtUploadedExcel.Rows[i][j].ToString(), out number))
                                    {
                                        error += "Age value should be numeric in row: " + (i + 1) + ";";
                                    }
                                    else
                                    {
                                        if (number < 18 || number > 60)
                                        {
                                            error += "Age value should be between 18 and 60 in row: " + (i + 1) + ";";
                                        }
                                    }
                                }
                            }
                            //Check for those column whose datatype is integer specified in ExcelStructure
                            if (strFieldType[j].ToUpper().Trim().Equals("I") && dtUploadedExcel.Rows[i][j].ToString() != "" &&
                                !strFieldHeadings[j].ToUpper().Trim().Equals("Age", StringComparison.InvariantCultureIgnoreCase))
                            {
                                if (!int.TryParse(dtUploadedExcel.Rows[i][j].ToString(), out number))
                                {
                                    error += dtUploadedExcel.Columns[j].ColumnName + " Should be Numeric value in line " + (i + 1) + ";";
                                }
                            }
                            //Checking the type of the Column if it is String then check the length of the value specified in the Excel File against the max value specified
                            //inside the ExcelStructure Table using the | operator
                            if (strFieldType[j].ToUpper().Trim().Equals("S") && dtUploadedExcel.Rows[i][j].ToString() != "")
                            {
                                //Check every field length compared with what we specified in the ExcelStructure Length For String Type
                                if (Convert.ToInt32(strFieldSize[j].Trim()) < dtUploadedExcel.Rows[i][j].ToString().Length)
                                {
                                    error += string.Format("Value Length: {0}. Reached Max Length Of {1}", dtUploadedExcel.Rows[i][j].ToString().Length, strFieldSize[j].Trim()) + ";";
                                }
                            }
                        }
                        //adding the error details to the Error coulmn of the DataTable which can be used for displaying the error details on the Page
                        dtUploadedExcel.Rows[i]["Error"] = error;
                    }
                }
                return "Success";
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}

Add a new controller with the name HomeController inside the controller folder of your application. Here is the code for that.

using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using ImportFromExcel.Domain.Abstract;

using ImportFromExcel.Domain.Entities;
using ImportFromExcel.WebUI.AppHelper;
namespace ImportFromExcel.WebUI.Controllers
{
    public class HomeController : Controller
    {
        private IEmployeeRepository repository;

        public HomeController(IEmployeeRepository repository)
        {
            this.repository = repository;
        }

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

        /// <summary>
       /// This function will be only called when there is a postback using Httppost method from the view. This function accepts an arguement
        /// named file which is basically the name of the input file control specified in the view. Since MVC4 follows the conventional approach by default.
        /// </summary>
        /// <param name="file">HttpPostedFileBase object</param>
        /// <returns>returns ActionResult</returns>

        [HttpPost]
        public ActionResult Index(HttpPostedFileBase file)
        {
            if (file != null)
            {
                string extension = Path.GetExtension(file.FileName);
                if (extension.Equals(".xls") || extension.Equals(".xlsx"))
                {
                    DateTime dt = DateTime.Now;
                    string filePath = "EmployeeData";
                    string format = dt.ToString();
                    format = format.Replace('/', '_');
                    format = format.Replace(':', '_');
                    format = format.Replace(' ', '_');
                    filePath += "_" + format + ".xlsx";
                    string finalFilePath = Server.MapPath("~/Uploads/" + filePath);
                    file.SaveAs(finalFilePath);
                    APIHelper objHelper = new APIHelper(repository);
                    string errorDetails = string.Empty;
                    DataSet ds = objHelper.ReadExcelFile(finalFilePath, out errorDetails);
                    if (errorDetails.Equals("success", StringComparison.InvariantCultureIgnoreCase))
                    {
                        int errorCount = ds.Tables[0].AsEnumerable().Where(x => x["Error"].ToString() != "").Count();
                        if (errorCount > 0)
                            ViewBag.UploadMsg = "Error Uploading Data";
                        else
                        {
                            ListConverter<Employee> converterObj = new ListConverter<Employee>(ds);
                            List<Employee> lstData = converterObj.ConvertDataSetToList();
                            repository.SaveEmployee(lstData);
                            ViewBag.UploadMsg = "Data Saved Successfully...";
                        }
                        return View("Index", ds);
                    }
                    else
                    {
                        ViewBag.UploadMsg = errorDetails;

                        return View("Index", null);
                    }
                }
                ViewBag.Error = "Invalid File Format " + extension + " Valid File Formats allowed are .xls or .xlsx";
                return View("Index");
            }
            ViewBag.Error = "Kindly Select an Excel File to upload";
            return View("Index");
            //to display alter box from Controller you can make use of the following line
            // return Content("<script type='text/javascript'>alert('Error Uploading Details');</script>");
        }
    }
}

Now create a strongly typed view from the controller. Here is the code for that.

@model System.Data.DataSet
@using System.Data;
@{
    ViewBag.Title = "Index";
    Layout = "~/Views/Shared/_Layout.cshtml";
}
<h3>Upload Excel File</h3>
@if (ViewBag.Error != null)
{
    <script type="text/javascript">
        alert("@ViewBag.Error.ToString()");
    </script>
}
@using (Html.BeginForm("Index", "Home", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
    <div>
        <label for="file">Filename:</label>
        <input type="file" name="file" id="file" />
        <input type="submit" value="Upload File" />
    </div>
}
<p>
    @if (ViewBag.UploadMsg != null)
    {
        if (ViewBag.UploadMsg == "Data Saved Successfully...")
        {
        <script type="text/javascript">
            alert("@ViewBag.UploadMsg.ToString()");
        </script>
        }
        else if (ViewBag.UploadMsg != "" && Model == null)
        {
        <script type="text/javascript">
            alert("@ViewBag.UploadMsg.ToString()");
        </script>
        }
        else
        {
            Html.RenderPartial("~/Views/Shared/_UploadResult.cshtml", Model);
        }
    }
</p>

PartialView: _UploadResult.cshtml

@model System.Data.DataSet
@using System.Data;
@{
    int counter = 1;
}
<div>
    <h3>Upload Errors.</h3>
</div>
<div class="CSSTableGenerator">
    <table>
        <tr>
            <td>Sr.No</td>
            <td>Sheet Name</td>
            <td>Emp Name</td>
            <td>Error Message</td>
        </tr>
        @foreach (DataTable table in Model.Tables)
        {
            if (table.Rows.Count > 0)
            {
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    if (table.Rows[i]["Error"] != "")
                    {
                        <tr>
                            <td>@counter</td>
                            <td>@table.TableName</td>
                            <td>@table.Rows[i]["Name"].ToString()</td>
                            <td>@table.Rows[i]["Error"].ToString()</td>
                        </tr>
                        counter++;
                    }
                }
            }
        }
    </table>
</div>

Note: You need to download the application for the smooth functioning

1. For NinjectControllerFactory that we created in WebUI
2. For App_Helper Files that are used for uploading and reading the Excel files content
3. Before running, kindly create an Excel file with some records that you can make use of to upload it.
4. The following is the dummy ExcelFile:
   
   
   

Here we explicitly did not specify the name of the Employee and age > 60 and non numeric value in the age column.

Now we can run the application and try to upload the Excel file. Here is the final output, since there are validation errors our screen will look like this:

Now let's try to update the Excel and remove all the validation errors.



Stop the application and open ServerExplorer and refersh your database and right-click on the Employee Table and select the option "Show Table Data".



You'll find that our data is stored that we uploaded using Excel.



Here within our application there is the small issue that if you continue to upload new files it will continue to increment the EmployeeID since it takes it as an auto-increment column and also it's a primary key as per EntityFramework Conventions and we didn't add a field named Batch_no so we won't be able to recognize the upload details. But nevertheless since we are learning it, for learning purposes our task has been done. Small and Simple Logic anyone can implement for adding the Batch_no as the primary key for the employee Table and making it an auto-generated column. You can also add the date and time or when the upload was made.