private async Task<DatabaseStatusViewModel> ProcessDataFile(string filePath) { var startTime = DateTime.Now; if (filePath == null || !File.Exists(filePath)) return new DatabaseStatusViewModel() { Message = "File does not exist or No file was uploaded" }; var status = new DatabaseStatusViewModel() { Success = false, RecordsInFile = 0, RecordsLoaded = 0, }; var config = new CsvConfiguration() { IsHeaderCaseSensitive = false, WillThrowOnMissingField = false, IgnoreReadingExceptions = true, ThrowOnBadData = false, SkipEmptyRecords = true, }; var csv = new CsvReader(new StreamReader(filePath, Encoding.Default, true), config); csv.Configuration.RegisterClassMap<CsvMap>(); var csvTaxRecords = csv.GetRecords<CsvTaxRecordViewModel>().ToList(); var csvConstituents = csvTaxRecords.DistinctBy(m => m.LookupId).AsQueryable().ProjectTo<ConstituentViewModel>().ToList(); var dbConstituents = db.Constituents.ProjectTo<ConstituentViewModel>().ToList(); var newConstituentList = csvConstituents.Except(dbConstituents, new ConstituentIdComparer()).ToList(); var existingConstituentList = csvConstituents.Except(newConstituentList, new ConstituentIdComparer()); var constituentChangeList = existingConstituentList.Except(dbConstituents, new ConstituentComparer()); // Update existing constituents that differ from database foreach (var vm in constituentChangeList) { ConstituentViewModel cvm = dbConstituents.FirstOrDefault(x => x.LookupId == vm.LookupId); if (cvm == null) continue; vm.Id = cvm.Id; vm.UpdatedBy = "system"; vm.UpdatedDate = DateTime.Now; cvm.CopyPropertiesFrom(vm); var constituent = Mapper.Map<ConstituentViewModel, Constituent>(cvm); db.Constituents.AddOrUpdate(constituent); } status.ConstituentsUpdated = db.SaveChanges(); // Add new Constituents missing from database // Bulk copy new Constituent records if (newConstituentList.Count > 0) { foreach (var vm in newConstituentList) { vm.CreatedBy = "system"; vm.UpdatedBy = "system"; vm.CreatedDate = DateTime.Now; vm.UpdatedDate = DateTime.Now; } var missingTbl = newConstituentList.ToDataTable(); using (var sbc = new SqlBulkCopy(db.Database.Connection.ConnectionString)) { sbc.DestinationTableName = db.GetTableName<Constituent>(); sbc.BatchSize = 10000; sbc.BulkCopyTimeout = 0; foreach (var col in missingTbl.Columns) { sbc.ColumnMappings.Add(col.ToString(), col.ToString()); } try { await sbc.WriteToServerAsync(missingTbl); status.ConstituentsCreated = sbc.RowsCopiedCount(); } catch (Exception e) { status.Message = e.Message; } } } // Update constituents because of new bulk copy constituents //TODO: Change Created and Updated user to logged in user dbConstituents = db.Constituents.ProjectTo<ConstituentViewModel>().ToList(); // Build dictionary to map database key to csv records LookupId var dic = new Dictionary<int, string>(); dbConstituents.ForEach(x => dic.Add(x.Id, x.LookupId)); // Update parent key for each tax record //csvTaxRecords.ForEach(x => x.ConstituentId = dic.FirstOrDefault(d => d.Value == x.LookupId).Key); csvTaxRecords.ForEach((s) => { s.ConstituentId = dic.FirstOrDefault(d => d.Value == s.LookupId).Key; s.CreatedBy = "system"; s.UpdatedBy = "system"; s.CreatedDate = DateTime.Now; s.UpdatedDate = DateTime.Now; }); // Bulk insert new tax records using (var sbc = new SqlBulkCopy(db.Database.Connection.ConnectionString)) { sbc.DestinationTableName = db.GetTableName<TaxItem>(); sbc.BatchSize = 10000; sbc.BulkCopyTimeout = 0; var dt = Mapper.Map<List<CsvTaxRecordViewModel>, List<TaxItem>>(csvTaxRecords).ToDataTable(); foreach (var col in dt.Columns) { sbc.ColumnMappings.Add(col.ToString(), col.ToString()); } try { await sbc.WriteToServerAsync(dt); status.RecordsLoaded = sbc.RowsCopiedCount(); } catch (Exception ex) { status.Message = ex.Message; } } status.RecordsInFile = csvTaxRecords.Count; status.Success = true; if (csvTaxRecords.Count != csv.Row - 2) { status.Message = "Error in file header mappings. Check file headers and try again."; status.Success = false; } else { status.Success = true; status.Message = "Successfully loaded tax records."; } status.TotalTime = DateTime.Now.Subtract(startTime).ToString(@"hh\:mm\:ss"); csv.Dispose(); return status; }