public ActionResult AllowanceImport(PayrollAllowanceDetailDTO Record, HttpPostedFileBase file) { Record.AllowanceMasterList = _PayrollAllowanceMasterService.GetAllowanceMasterList(); DropDownListViewmodelcs ddlvm = DropDownlist(); Record.OfficeList = ddlvm.OfficeList; try { string result; result = file.FileName; DataSet ds = new DataSet(); DataTable dts = new DataTable(); string fileExtension = System.IO.Path.GetExtension(result); if (fileExtension == ".xls" || fileExtension == ".xlsx") { string fileLocation = Server.MapPath("~/Content/AllowanceImport/") + result; if (!Directory.Exists(Server.MapPath("~/Content/AllowanceImport/"))) { Directory.CreateDirectory(Server.MapPath("~/Content/AllowanceImport/")); } if (System.IO.File.Exists(fileLocation)) { System.IO.File.Delete(fileLocation); } file.SaveAs(Path.Combine(Server.MapPath("~/Content/AllowanceImport/"), result)); string excelConnectionString = string.Empty; excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\""; //connection String for xls file format. if (fileExtension == ".xls") { excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\""; } //connection String for xlsx file format. else if (fileExtension == ".xlsx") { excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\""; } //Create Connection to Excel work book and add oledb namespace OleDbConnection excelConnection = new OleDbConnection(excelConnectionString); excelConnection.Open(); DataTable dt = new DataTable(); dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (dt == null) { TempData["Danger"] = "Corrupt file. Please check the file and try again"; return(RedirectToAction("Index")); } String[] excelSheets = new String[dt.Rows.Count]; int t = 0; //excel data saves in temp file here. foreach (DataRow row in dt.Rows) { excelSheets[t] = row["TABLE_NAME"].ToString(); t++; } OleDbConnection excelConnection1 = new OleDbConnection(excelConnectionString); string query = string.Format("Select * from [{0}]", excelSheets[0]); using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection1)) { dataAdapter.Fill(ds); dataAdapter.Fill(dts); } excelConnection.Close(); } List <PayrollAllowanceDetailDTO> modelrecord = new List <PayrollAllowanceDetailDTO>(); foreach (DataRow d in dts.Rows) { PayrollAllowanceDetailDTO single = new PayrollAllowanceDetailDTO() { AllowanceMasterId = Record.AllowanceMasterId, EmployeeCode = Convert.ToInt32(d["EmployeeCode"].ToString()), PercentageAmount = d["PercentageAmount"].ToString(), Value = Convert.ToDecimal(d["Value"].ToString()), }; single.EmployeeName = _EmployeeService.GetEmployeeByID(single.EmployeeCode).Name; modelrecord.Add(single); PayrollAllowanceDetail domainrecord = PayrollAllowanceDetailRequestFormatter.ConvertRespondentInfoFromDTO(single); PayrollAllowanceDetail pastrecord = _unitOfWork.PayrollAllowanceDetailRepository.Get(x => x.AllowanceMasterId == single.AllowanceMasterId && x.EmployeeCode == single.EmployeeCode).FirstOrDefault(); if (pastrecord != null) { _unitOfWork.PayrollAllowanceDetailRepository.Delete(pastrecord); } _unitOfWork.PayrollAllowanceDetailRepository.Create(domainrecord); } ViewBag.AllowanceImportedList = modelrecord; TempData["Success"] = "Allowance Data imported successfully"; } catch (Exception ex) { TempData["Danger"] = ex.Message; } return(View(Record)); }
public void InsertIntoPayrollAllowanceDetail(PayrollAllowanceDetailDTO Record) { PayrollAllowanceDetail Data = PayrollAllowanceDetailRequestFormatter.ConvertRespondentInfoFromDTO(Record); _unitOfWork.PayrollAllowanceDetailRepository.Create(Data); }