Post
Ask Question

Capture Logging Info Of Old And Recent Column Values Through Bulk Upload Excel Sheet

In this blog, a lot of things have been covered through a web application.
  1. Read Excel through EPPlus Library through MVC Application.
  2. Upload Bulk Excel Data into a database in single DB Call through ADO.NET.
  3. Capture a table's column values through a trigger in a different table. 
Here, I am providing a basic application which has the functionality to upload an Excel sheet and import that sheet's data into a database table. If the Excel data is having any new record, then it will insert that data into the table, otherwise it will update that existing data into the corresponding table and capture the log with new and old values in a different table through a trigger.
 
 
There are few things that need to be done before running the application.
  • Download the attached script files.
  • Run all scripts in given steps on your database server which is attached in the zip format.
  • Scripts object including (Table, Trigger, Table Type, Store Procedure).
  • Change the respective data source name, username, and password in the webconfig file.

    <add name="devConString" connectionString="Data Source=DataSourceName;Initial Catalog=Databasename;User ID=UserID;Password=Password" />
  • Upload the Excel in the given format.

    Capture Logging Info Of Columns Old And Recent Value Through Bulk Upload Excel Sheet

  • Read Excel file. I am using EPPLUS Library that you can download from NuGet.
  • Create a new fresh Web MVC application and copy and paste the below home controller code, index code, and change the connection string accordingly in the webconfig file.
Below are the code details for Home Controller, Index Page, and webconfig file.
  1. using CaptureLog.Models;    
  2. using Microsoft.SqlServer.Server;    
  3. using OfficeOpenXml;    
  4. using System;    
  5. using System.Collections.Generic;    
  6. using System.Configuration;    
  7. using System.Data;    
  8. using System.Data.SqlClient;    
  9. using System.IO;    
  10. using System.Linq;    
  11. using System.Web;    
  12. using System.Web.Mvc;    
  13.     
  14. namespace UploadReauthReport.Controllers    
  15. {       
  16.     public class HomeController : Controller    
  17.     {    
  18.         /// <summary>    
  19.         /// PROC_INSERT_Employee_REPORT_RECORDS    
  20.         /// </summary>    
  21.         public const string PROC_INSERT_Employee_REPORT_RECORDS = "Orc_Proc_HRReportData";    
  22.     
  23.         /// <summary>    
  24.         /// PARAM_Employee_REPORT_RECORDS    
  25.         /// </summary>    
  26.         public const string PARAM_Employee_REPORT_RECORDS = "@HRReportDataModel";    
  27.         public ActionResult Index()    
  28.         {    
  29.             return View();    
  30.         }    
  31.     
  32.     
  33.         /// <summary>    
  34.         /// ReadEmployeeExcelData    
  35.         /// </summary>    
  36.         /// <param name="FileUpload"></param>    
  37.         public List<EmployeeDataModel> ReadEmployeeExcelData(HttpPostedFileBase FileUpload)    
  38.         {    
  39.             List<EmployeeDataModel> employeeDataModel = new List<EmployeeDataModel>();    
  40.             if (FileUpload != null)    
  41.             {    
  42.                 string filename = FileUpload.FileName;    
  43.                 using (var package = new ExcelPackage(FileUpload.InputStream))    
  44.                 {    
  45.                     var currentSheet = package.Workbook.Worksheets;    
  46.                     var workSheet = currentSheet.First();    
  47.                     var noOfCol = workSheet.Dimension.End.Column;    
  48.                     var noOfRow = workSheet.Dimension.End.Row;    
  49.                     for (int rowIterator = 2; rowIterator <= noOfRow; rowIterator++)    
  50.                     {    
  51.     
  52.                         employeeDataModel.Add(new EmployeeDataModel    
  53.                         {    
  54.                             Name = Convert.ToString(workSheet.Cells[rowIterator, 1].Value).Trim(),    
  55.                             Address = Convert.ToString(workSheet.Cells[rowIterator, 2].Value).Trim(),    
  56.                             Manager = Convert.ToString(workSheet.Cells[rowIterator, 3].Value).Trim(),    
  57.                         });    
  58.                     }    
  59.                 }    
  60.     
  61.                 return employeeDataModel;    
  62.     
  63.             }    
  64.             return employeeDataModel;    
  65.         }    
  66.     
  67.         /// <summary>    
  68.         /// UploadEmployeeData    
  69.         /// </summary>    
  70.         /// <param name="file"></param>    
  71.         /// <returns></returns>    
  72.         public ActionResult UploadEmployeeData(HttpPostedFileBase file)    
  73.         {    
  74.             try    
  75.             {    
  76.                 if (file != null && file.ContentLength > 0)    
  77.                 {    
  78.                     string fileExt = string.Empty;    
  79.                     string _FileName = Path.GetFileName(file.FileName);    
  80.                     fileExt = Path.GetExtension(_FileName); //get the file extension     
  81.                     if (file.ContentType == "application/vnd.ms-excel" || file.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")    
  82.                     {    
  83.                         List<EmployeeDataModel> employeeDataModel = ReadEmployeeExcelData(file);    
  84.                         if (employeeDataModel != null && employeeDataModel.Count > 0)    
  85.                         {    
  86.                             InsertEmployeeData(employeeDataModel);    
  87.                         }    
  88.                         ViewBag.Message = string.Format("{0} records uploaded successfully.", employeeDataModel.Count);    
  89.                         ViewBag.IsSuccess = true;    
  90.                         return View("Index");    
  91.                     }    
  92.                     else    
  93.                     {    
  94.                         ViewBag.IsSuccess = false;    
  95.                         ViewBag.Message = "Please choose Excel file";    
  96.                         return View("Index");    
  97.                     }    
  98.                 }    
  99.                 else    
  100.                 {    
  101.                     ViewBag.IsSuccess = false;    
  102.                     ViewBag.Message = "No file to import in system";    
  103.                     return View("Index");    
  104.                 }    
  105.             }    
  106.             catch (Exception ex)    
  107.             {    
  108.                 ViewBag.IsSuccess = false;    
  109.                 ViewBag.Message = ex.Message; // "File upload failed!!";    
  110.                 return View("Index");    
  111.             }    
  112.         }    
  113.     
  114.     
  115.         /// <summary>    
  116.         /// CreateSqlConnection    
  117.         /// </summary>    
  118.         /// <returns></returns>    
  119.         private SqlConnection CreateSqlConnection()    
  120.         {    
  121. #if DEBUG    
  122.             string strConnString = ConfigurationManager.ConnectionStrings["devConString"].ConnectionString;    
  123.             return new SqlConnection(strConnString);    
  124. #else    
  125.                              
  126. #endif    
  127.         }    
  128.     
  129.         /// <summary>    
  130.         /// ConvertEmployeeResponseToUT    
  131.         /// </summary>    
  132.         /// <param name="records"></param>    
  133.         /// <param name="newServicelogId"></param>    
  134.         /// <returns></returns>    
  135.         private List<SqlDataRecord> ConvertEmployeeResponseToUT(List<EmployeeDataModel> records)    
  136.         {    
  137.             var columns = new SqlMetaData[3];    
  138.             columns[0] = new SqlMetaData("Name", SqlDbType.VarChar, 4000);    
  139.             columns[1] = new SqlMetaData("Address", SqlDbType.VarChar, 4000);    
  140.             columns[2] = new SqlMetaData("Manager", SqlDbType.VarChar, 4000);    
  141.             List<SqlDataRecord> dataRecords = new List<SqlDataRecord>();    
  142.             foreach (var item in records)    
  143.             {    
  144.                 SqlDataRecord record = new SqlDataRecord(columns);    
  145.                 record.SetString(0, item.Name);    
  146.                 record.SetString(1, item.Address);    
  147.                 record.SetString(2, item.Manager);    
  148.                 dataRecords.Add(record);    
  149.             }    
  150.             return dataRecords;    
  151.         }    
  152.     
  153.     
  154.     
  155.         /// <summary>    
  156.         /// InsertEmployeeData    
  157.         /// </summary>    
  158.         /// <param name="records"></param>    
  159.         private void InsertEmployeeData(List<EmployeeDataModel> records)    
  160.         {    
  161.     
  162.             if (records.Count > 0)    
  163.             {    
  164.                 List<SqlDataRecord> recordsDS = ConvertEmployeeResponseToUT(records);    
  165.                 SqlConnection conn = this.CreateSqlConnection();    
  166.                 SqlCommand cmd = new SqlCommand();    
  167.                 cmd.CommandType = CommandType.StoredProcedure;    
  168.                 cmd.CommandText = PROC_INSERT_Employee_REPORT_RECORDS;    
  169.                 cmd.Parameters.Add(PARAM_Employee_REPORT_RECORDS, SqlDbType.Structured).Value = recordsDS;    
  170.                 cmd.Connection = conn;    
  171.                 try    
  172.                 {    
  173.                     conn.Open();    
  174.                     cmd.ExecuteNonQuery();    
  175.                 }    
  176.                 finally    
  177.                 {    
  178.                     conn.Close();    
  179.                     conn.Dispose();    
  180.                 }    
  181.             }    
  182.         }    
  183.     }     
  184. }    
Employee Data Model Class
 
Read data from Excel and convert into this model class.
  1. using System;    
  2. using System.Collections.Generic;    
  3. using System.Linq;    
  4. using System.Web;    
  5.     
  6. namespace CaptureLog.Models    
  7. {    
  8.     public class EmployeeDataModel    
  9.     {    
  10.         public string Name { get; set; }        
  11.         public string Address { get; set; }         
  12.         public string Manager { get; set; }    
  13.     }    
  14. }    
Below is the Index page Razor Control code.
  1. @{    
  2.     ViewBag.Title = "Capture Logging";    
  3. }    
  4.     
  5. <link href="@Url.Content("~/Content/bootstrap.min.css")" rel="stylesheet" />    
  6. <br />    
  7. <div class="form-group">    
  8.     @{    
  9.         if (ViewBag.IsSuccess != null && ViewBag.IsSuccess)    
  10.         {    
  11.             <div class="alert alert-success">    
  12.                 <strong>Success !</strong> @ViewBag.Message    
  13.             </div>    
  14.         }    
  15.         else if (ViewBag.IsSuccess != null)    
  16.         {    
  17.             <div class="alert alert-danger" role="alert">    
  18.                 <strong>Error !</strong> @ViewBag.Message    
  19.             </div>    
  20.         }    
  21.     }    
  22.     
  23. </div>    
  24. <br />    
  25.     
  26. <div class="container">    
  27.     <div class="row">    
  28.         <div class="col-sm-4">    
  29.             <div class="form-group">    
  30.                 <div class="container">    
  31.                     <div class="panel-group">    
  32.                         <div class="panel panel-default">    
  33.                             <div class="panel-heading">Bulk Capture Log</div>    
  34.                             <div class="panel-body">    
  35.                                 <div class="row">    
  36.                                     @using (Html.BeginForm("UploadEmployeeData", "Home", FormMethod.Post, new { enctype = "multipart/form-data", @class = "width100 form-inline" }))    
  37.                                     {    
  38.                                         <div class="form-group required">    
  39.                                             <label class="control-label" for="email">Upload:</label>    
  40.                                             @Html.TextBox("file", "", new { type = "file", @class = "form-control" })    
  41.                                         </div>    
  42.     
  43.                                         <div class="form-group">    
  44.                                             <input type="submit" value="Import File" class="form-control  btn btn-primary disablebutton  btnSubmit" />    
  45.                                         </div>    
  46.     
  47.                                     }    
  48.                                 </div>    
  49.                             </div>    
  50.                         </div>    
  51.                     </div>    
  52.                 </div>    
  53.             </div>    
  54.         </div>    
  55.         <div class="col-sm-4">    
  56.     
  57.         </div>    
  58.         <div class="col-sm-4">    
  59.     
  60.         </div>    
  61.     </div>    
  62. </div>    
Web Config File Code
  1. <?xml version="1.0" encoding="utf-8"?>    
  2. <!--    
  3.   For more information on how to configure your ASP.NET application, please visit    
  4.   https://go.microsoft.com/fwlink/?LinkId=301880    
  5.   -->    
  6. <configuration>    
  7.   <appSettings>    
  8.     <add key="webpages:Version" value="3.0.0.0" />    
  9.     <add key="webpages:Enabled" value="false" />    
  10.     <add key="ClientValidationEnabled" value="true" />    
  11.     <add key="UnobtrusiveJavaScriptEnabled" value="true" />    
  12.   </appSettings>    
  13.   <system.web>    
  14.     <compilation debug="true" targetFramework="4.6.1" />    
  15.     <httpRuntime targetFramework="4.6.1" />    
  16.   </system.web>    
  17.   <runtime>    
  18.     <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">    
  19.       <dependentAssembly>    
  20.         <assemblyIdentity name="Antlr3.Runtime" publicKeyToken="eb42632606e9261f" />    
  21.         <bindingRedirect oldVersion="0.0.0.0-3.5.0.2" newVersion="3.5.0.2" />    
  22.       </dependentAssembly>    
  23.       <dependentAssembly>    
  24.         <assemblyIdentity name="System.Diagnostics.DiagnosticSource" publicKeyToken="cc7b13ffcd2ddd51" />    
  25.         <bindingRedirect oldVersion="0.0.0.0-4.0.2.1" newVersion="4.0.2.1" />    
  26.       </dependentAssembly>          
  27.       <dependentAssembly>    
  28.         <assemblyIdentity name="Newtonsoft.Json" publicKeyToken="30ad4fe6b2a6aeed" />    
  29.         <bindingRedirect oldVersion="0.0.0.0-11.0.0.0" newVersion="11.0.0.0" />    
  30.       </dependentAssembly>    
  31.       <dependentAssembly>    
  32.         <assemblyIdentity name="System.Web.Optimization" publicKeyToken="31bf3856ad364e35" />    
  33.         <bindingRedirect oldVersion="1.0.0.0-1.1.0.0" newVersion="1.1.0.0" />    
  34.       </dependentAssembly>    
  35.       <dependentAssembly>    
  36.         <assemblyIdentity name="WebGrease" publicKeyToken="31bf3856ad364e35" />    
  37.         <bindingRedirect oldVersion="0.0.0.0-1.6.5135.21930" newVersion="1.6.5135.21930" />    
  38.       </dependentAssembly>    
  39.       <dependentAssembly>    
  40.         <assemblyIdentity name="System.Web.Helpers" publicKeyToken="31bf3856ad364e35" />    
  41.         <bindingRedirect oldVersion="1.0.0.0-3.0.0.0" newVersion="3.0.0.0" />    
  42.       </dependentAssembly>    
  43.       <dependentAssembly>    
  44.         <assemblyIdentity name="System.Web.WebPages" publicKeyToken="31bf3856ad364e35" />    
  45.         <bindingRedirect oldVersion="1.0.0.0-3.0.0.0" newVersion="3.0.0.0" />    
  46.       </dependentAssembly>    
  47.       <dependentAssembly>    
  48.         <assemblyIdentity name="System.Web.Mvc" publicKeyToken="31bf3856ad364e35" />    
  49.         <bindingRedirect oldVersion="1.0.0.0-5.2.4.0" newVersion="5.2.4.0" />    
  50.       </dependentAssembly>    
  51.     </assemblyBinding>    
  52.   </runtime>    
  53.   <system.webServer>    
  54.     <modules>    
  55.       <remove name="TelemetryCorrelationHttpModule" />    
  56.       <add name="TelemetryCorrelationHttpModule" type="Microsoft.AspNet.TelemetryCorrelation.TelemetryCorrelationHttpModule, Microsoft.AspNet.TelemetryCorrelation" preCondition="integratedMode,managedHandler" />    
  57.     </modules>    
  58.   </system.webServer>    
  59.   <connectionStrings>    
  60.     <add name="devConString" connectionString="Data Source=ChangeTheDataSourceName;Initial Catalog=DatabaseName;User ID=userName;Password=Password" />    
  61.   </connectionStrings>    
  62.   <system.codedom>    
  63.     <compilers>    
  64.       <compiler language="c#;cs;csharp" extension=".cs" type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.CSharpCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=2.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" warningLevel="4" compilerOptions="/langversion:default /nowarn:1659;1699;1701" />    
  65.       <compiler language="vb;vbs;visualbasic;vbscript" extension=".vb" type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.VBCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=2.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" warningLevel="4" compilerOptions="/langversion:default /nowarn:41008 /define:_MYTYPE=\"Web\" /optionInfer+" />    
  66.     </compilers>    
  67.   </system.codedom>    
  68. </configuration>  
Next Recommended Reading How To Apply Cell Text & Background Color In An Excel Sheet Using EPPlus (C#) - Part Two