public static List <string> ProcessFiles(string filePath) { int newRows = 0; int updatedRow = 0; List <string> result = new List <string>(); List <RetailComparison> list = new List <RetailComparison>(); #region Process File string file = filePath; string fileLine = string.Empty; if (File.Exists(file)) { Application excel = new Application(); Workbook wb = excel.Workbooks.Open(file); int _sheets = excel.ActiveWorkbook.Sheets.Count; //for (int s = 1; s < _sheets + 1; s++) for (int s = 1; s < 2; s++) { int _dealerid = 0; int _Year = DateTime.Now.Year; int _Month = DateTime.Now.Month; Worksheet excelSheet = wb.Sheets[1]; Range xlRange = excelSheet.UsedRange; for (int i = 1; i < xlRange.Columns[1].Rows.Count; i++) { if (excelSheet.Cells[i, 1].Value2 != null) { if (excelSheet.Cells[i, 1].Value.ToString() != string.Empty) { string val = "0" + excelSheet.Cells[i, 1].Value.ToString(); SDCManagementSystem_Test3Entities db = new SDCManagementSystem_Test3Entities(); DWT_Dealers d = db.DWT_Dealers.Where(x => x.Code == val).FirstOrDefault(); if (d != null) { _dealerid = d.DealerId; RetailComparison vhs = new RetailComparison(); vhs.Dealer_id = _dealerid; vhs.Year = _Year; vhs.Month = _Month - 1; if (excelSheet.Cells[i, 3].Value2 != null) { if (int.TryParse(excelSheet.Cells[i, 3].Value.ToString(), out int _district)) { vhs.district = _district; } } if (excelSheet.Cells[i, 4].Value2 != null) { if (int.TryParse(excelSheet.Cells[i, 4].Value.ToString(), out int _Whlse_currentMY)) { vhs.Whlse_currentMY = _Whlse_currentMY; } } if (excelSheet.Cells[i, 5].Value2 != null) { if (int.TryParse(excelSheet.Cells[i, 5].Value.ToString(), out int _Whlse_passMY)) { vhs.Whlse_passMY = _Whlse_passMY; } } if (excelSheet.Cells[i, 6].Value2 != null) { if (int.TryParse(excelSheet.Cells[i, 6].Value.ToString(), out int _Whlse_ChangeYOY)) { vhs.Whlse_ChangeYOY = _Whlse_ChangeYOY; } } if (excelSheet.Cells[i, 7].Value2 != null) { if (decimal.TryParse(excelSheet.Cells[i, 7].Value.ToString(), out decimal _Pct_WhlseYOY)) { vhs.Pct_WhlseYOY = _Pct_WhlseYOY * 100; } } if (excelSheet.Cells[i, 8].Value2 != null) { if (int.TryParse(excelSheet.Cells[i, 8].Value.ToString(), out int _Whlse_CurrenMonth)) { vhs.Whlse_CurrenMonth = _Whlse_CurrenMonth; } } if (excelSheet.Cells[i, 9].Value2 != null) { if (int.TryParse(excelSheet.Cells[i, 9].Value.ToString(), out int _RtlSls_currentMY)) { vhs.RtlSls_currentMY = _RtlSls_currentMY; } } if (excelSheet.Cells[i, 10].Value2 != null) { if (int.TryParse(excelSheet.Cells[i, 10].Value.ToString(), out int _RtlSls_passMY)) { vhs.RtlSls_passMY = _RtlSls_passMY; } } if (excelSheet.Cells[i, 11].Value2 != null) { if (int.TryParse(excelSheet.Cells[i, 11].Value.ToString(), out int _RtlSls_ChangeYOY)) { vhs.RtlSls_ChangeYOY = _RtlSls_ChangeYOY; } } if (excelSheet.Cells[i, 12].Value2 != null) { if (decimal.TryParse(excelSheet.Cells[i, 12].Value.ToString(), out decimal _Pct_RtlSlsYOY)) { vhs.Pct_RtlSlsYOY = _Pct_RtlSlsYOY * 100; } } if (excelSheet.Cells[i, 13].Value2 != null) { if (int.TryParse(excelSheet.Cells[i, 13].Value.ToString(), out int _RtlSls_CurrenMonth)) { vhs.RtlSls_CurrenMonth = _RtlSls_CurrenMonth; } } list.Add(vhs); } } } } } object misValue = System.Reflection.Missing.Value; wb.Close(false, misValue, misValue); excel.Quit(); using (SDCManagementSystem_Test3Entities db = new SDCManagementSystem_Test3Entities()) { foreach (RetailComparison dealer in list) { if (dealer.Dealer_id != 0) { RetailComparison vhs = db.RetailComparisons.Where( x => x.Dealer_id == dealer.Dealer_id && x.Year == dealer.Year && x.Month == dealer.Month).FirstOrDefault(); if (vhs != null) { //modify record vhs.Whlse_currentMY = dealer.Whlse_currentMY; vhs.Whlse_passMY = dealer.Whlse_passMY; vhs.Whlse_ChangeYOY = dealer.Whlse_ChangeYOY; vhs.Pct_WhlseYOY = dealer.Pct_WhlseYOY; vhs.Whlse_CurrenMonth = dealer.Whlse_CurrenMonth; vhs.RtlSls_currentMY = dealer.RtlSls_currentMY; vhs.RtlSls_passMY = dealer.RtlSls_passMY; vhs.RtlSls_ChangeYOY = dealer.RtlSls_ChangeYOY; vhs.Pct_RtlSlsYOY = dealer.Pct_RtlSlsYOY; vhs.RtlSls_CurrenMonth = dealer.RtlSls_CurrenMonth; updatedRow++; } else { db.RetailComparisons.Add(dealer); newRows++; } } else { db.RetailComparisons.Add(dealer); newRows++; } } try { db.SaveChanges(); } catch (Exception ex) { throw ex; } } //End of Entity result.Add("The " + filePath + " file was uploaded: New Row added: " + newRows.ToString() + " Row updated: " + updatedRow.ToString() + Environment.NewLine); } // End If File>exits #endregion return(result); }
public static List <string> ProcessFiles(string filePath) { int newRows = 0; int updatedRow = 0; List <string> result = new List <string>(); List <CrossSellZip> list = new List <CrossSellZip>(); #region Process File string file = filePath; string fileLine = string.Empty; if (File.Exists(file)) { Application excel = new Application(); Workbook wb = excel.Workbooks.Open(file); int _sheets = excel.ActiveWorkbook.Sheets.Count; for (int s = 1; s < _sheets + 1; s++) { int _dealerid = 0; DateTime _SalesFrom = DateTime.MinValue; DateTime _SalesThrough = DateTime.MinValue; Worksheet excelSheet = wb.Sheets[s]; Range xlRange = excelSheet.UsedRange; for (int i = 1; i < xlRange.Columns[1].Rows.Count; i++) { if (excelSheet.Cells[i, 1].Value2 != null) { if (excelSheet.Cells[i, 1].Value.ToString() != string.Empty) { string val = excelSheet.Cells[i, 1].Value.ToString(); if (val.Contains("Dealer")) { SDCManagementSystem_Test3Entities db = new SDCManagementSystem_Test3Entities(); DWT_Dealers d = db.DWT_Dealers.Where(x => x.Code == val.Substring(val.IndexOf(" ") + 1, 6)).FirstOrDefault(); if (d != null) { _dealerid = d.DealerId; } } if (val.StartsWith("For")) { _SalesFrom = DateTime.Parse(val.Substring(15, 10)); _SalesThrough = DateTime.Parse(val.Substring(34, 10)); } if (int.TryParse(val, out int saleszip)) { CrossSellZip vhs = new CrossSellZip(); vhs.dealer_id = _dealerid; vhs.SalesFrom = _SalesFrom; vhs.SalesThrough = _SalesThrough; if (excelSheet.Cells[i, 1].Value2 != null) { if (int.TryParse(excelSheet.Cells[i, 1].Value.ToString(), out int _SalesinZIP)) { vhs.SalesinZIP = _SalesinZIP; } } if (excelSheet.Cells[i, 2].Value2 != null) { if (decimal.TryParse(excelSheet.Cells[i, 2].Value.ToString(), out decimal _pctTotalSales)) { vhs.pctTotalSales = _pctTotalSales; } } if (excelSheet.Cells[i, 4].Value2 != null) { vhs.ZIP = excelSheet.Cells[i, 4].Value.ToString(); } if (excelSheet.Cells[i, 6].Value2 != null) { vhs.ZIPName = excelSheet.Cells[i, 6].Value.ToString(); } if (excelSheet.Cells[i, 9].Value2 != null) { if (int.TryParse(excelSheet.Cells[i, 9].Value.ToString(), out int _CurrentPopulation)) { vhs.CurrentPopulation = _CurrentPopulation; } } if (excelSheet.Cells[i, 10].Value2 != null) { if (int.TryParse(excelSheet.Cells[i, 10].Value.ToString(), out int _C5YrsProyectedPopulation)) { vhs.C5YrsProyectedPopulation = _C5YrsProyectedPopulation; } } if (excelSheet.Cells[i, 12].Value2 != null) { if (int.TryParse(excelSheet.Cells[i, 12].Value.ToString(), out int _CurrentHouseholds)) { vhs.CurrentHouseholds = _CurrentHouseholds; } } if (excelSheet.Cells[i, 13].Value2 != null) { if (int.TryParse(excelSheet.Cells[i, 13].Value.ToString(), out int _C5YrsProyectedHouseholds)) { vhs.C5YrsProyectedHouseholds = _C5YrsProyectedHouseholds; } } if (excelSheet.Cells[i, 15].Value2 != null) { if (decimal.TryParse(excelSheet.Cells[i, 15].Value.ToString(), out decimal _CurrentMediaHouseholdsIncome)) { vhs.CurrentMediaHouseholdsIncome = _CurrentMediaHouseholdsIncome; } } if (excelSheet.Cells[i, 15].Value2 != null) { if (decimal.TryParse(excelSheet.Cells[i, 16].Value.ToString(), out decimal _C5YrsMediaHouseholdsIncome)) { vhs.C5YrsMediaHouseholdsIncome = _C5YrsMediaHouseholdsIncome; } } if (excelSheet.Cells[i, 18].Value2 != null) { if (decimal.TryParse(excelSheet.Cells[i, 18].Value.ToString(), out decimal _CurrentAVGHouseholdsIncome)) { vhs.CurrentAVGHouseholdsIncome = _CurrentAVGHouseholdsIncome; } } if (excelSheet.Cells[i, 20].Value2 != null) { if (decimal.TryParse(excelSheet.Cells[i, 20].Value.ToString(), out decimal _C5YrsAVGHouseholdsIncome)) { vhs.C5YrsAVGHouseholdsIncome = _C5YrsAVGHouseholdsIncome; } } if (excelSheet.Cells[i, 22].Value2 != null) { if (int.TryParse(excelSheet.Cells[i, 22].Value.ToString(), out int _CurrentHouseholdsUnder50)) { vhs.CurrentHouseholdsUnder50 = _CurrentHouseholdsUnder50; } } if (excelSheet.Cells[i, 25].Value2 != null) { if (int.TryParse(excelSheet.Cells[i, 25].Value.ToString(), out int _C5YrsProyectedHouseholdsUnder50)) { vhs.C5YrsProyectedHouseholdsUnder50 = _C5YrsProyectedHouseholdsUnder50; } } if (excelSheet.Cells[i, 27].Value2 != null) { if (int.TryParse(excelSheet.Cells[i, 27].Value.ToString(), out int _CurrentHouseholds50to100)) { vhs.CurrentHouseholds50to100 = _CurrentHouseholds50to100; } } if (excelSheet.Cells[i, 29].Value2 != null) { if (int.TryParse(excelSheet.Cells[i, 29].Value.ToString(), out int _C5YrsProyectedHouseholds50to100)) { vhs.C5YrsProyectedHouseholds50to100 = _C5YrsProyectedHouseholds50to100; } } if (excelSheet.Cells[i, 31].Value2 != null) { if (int.TryParse(excelSheet.Cells[i, 31].Value.ToString(), out int _CurrentHouseholdsOver100)) { vhs.CurrentHouseholdsOver100 = _CurrentHouseholdsOver100; } } if (excelSheet.Cells[i, 33].Value2 != null) { if (int.TryParse(excelSheet.Cells[i, 33].Value.ToString(), out int _C5YrsProyectedHouseholdsOver100)) { vhs.C5YrsProyectedHouseholdsOver100 = _C5YrsProyectedHouseholdsOver100; } } list.Add(vhs); } } } } } object misValue = System.Reflection.Missing.Value; wb.Close(false, misValue, misValue); excel.Quit(); using (SDCManagementSystem_Test3Entities db = new SDCManagementSystem_Test3Entities()) { foreach (CrossSellZip dealer in list) { if (dealer.dealer_id != 0) { CrossSellZip vhs = db.CrossSellZips.Where( x => x.dealer_id == dealer.dealer_id && x.SalesFrom == dealer.SalesFrom && x.SalesThrough == dealer.SalesThrough).FirstOrDefault(); if (vhs != null) { //modify record vhs.SalesinZIP = dealer.SalesinZIP; vhs.pctTotalSales = dealer.pctTotalSales; vhs.ZIP = dealer.ZIP; vhs.ZIPName = dealer.ZIPName; vhs.CurrentPopulation = dealer.CurrentPopulation; vhs.C5YrsProyectedPopulation = dealer.C5YrsProyectedPopulation; vhs.CurrentHouseholds = dealer.CurrentHouseholds; vhs.C5YrsProyectedHouseholds = dealer.C5YrsProyectedHouseholds; vhs.CurrentMediaHouseholdsIncome = dealer.CurrentMediaHouseholdsIncome; vhs.C5YrsMediaHouseholdsIncome = dealer.C5YrsMediaHouseholdsIncome; vhs.CurrentAVGHouseholdsIncome = dealer.CurrentAVGHouseholdsIncome; vhs.C5YrsAVGHouseholdsIncome = dealer.C5YrsAVGHouseholdsIncome; vhs.CurrentHouseholdsUnder50 = dealer.CurrentHouseholdsUnder50; vhs.C5YrsProyectedHouseholdsUnder50 = dealer.C5YrsProyectedHouseholdsUnder50; vhs.CurrentHouseholds50to100 = dealer.CurrentHouseholds50to100; vhs.C5YrsProyectedHouseholds50to100 = dealer.C5YrsProyectedHouseholds50to100; vhs.CurrentHouseholdsOver100 = dealer.CurrentHouseholdsOver100; vhs.C5YrsProyectedHouseholdsOver100 = dealer.C5YrsProyectedHouseholdsOver100; updatedRow++; } else { db.CrossSellZips.Add(dealer); newRows++; } } else { db.CrossSellZips.Add(dealer); newRows++; } } try { db.SaveChanges(); } catch (Exception ex) { throw ex; } } //End of Entity result.Add("The " + filePath + " file was uploaded: New Row added: " + newRows.ToString() + " Row updated: " + updatedRow.ToString() + Environment.NewLine); } // End If File>exits #endregion return(result); }
public static List <string> ProcessFiles(string filePath) { int newRows = 0; int updatedRow = 0; List <string> result = new List <string>(); List <AOR> listAOR = new List <AOR>(); List <AORDealerHistory> listAORH = new List <AORDealerHistory>(); List <CrossSellDealerAOR> listAORCrossSale = new List <CrossSellDealerAOR>(); string valLineBefore = string.Empty; #region Process File string file = filePath; string fileLine = string.Empty; if (File.Exists(file)) { Application excel = new Application(); Workbook wb = excel.Workbooks.Open(file); int _dealerid = 0; DateTime _SalesFrom = DateTime.MinValue; DateTime _SalesThrough = DateTime.MinValue; int _ObjTotalSales = 0; Worksheet excelSheet = wb.ActiveSheet; Range xlRange = excelSheet.UsedRange; AOR vhsAOR = null; CrossSellDealerAOR vhsCrossSellDealerAOR = null; AORDealerHistory vhsAORDealerHistory = new AORDealerHistory(); for (int i = 1; i < xlRange.Columns[1].Rows.Count; i++) { if (excelSheet.Cells[i, 1].Value2 != null) { if (excelSheet.Cells[i, 1].Value.ToString() != string.Empty) { string val = excelSheet.Cells[i, 1].Value.ToString(); if (val != string.Empty) { if (val.StartsWith("AOR")) { vhsAOR = new AOR(); vhsAOR.AORName = excelSheet.Cells[i, 1].Value.ToString(); string valResponsable = excelSheet.Cells[i + 1, 1].Value.ToString(); SDCManagementSystem_Test3Entities db = new SDCManagementSystem_Test3Entities(); DWT_Dealers d = db.DWT_Dealers.Where(x => x.Code == valResponsable.Substring(0, 6)).FirstOrDefault(); if (d != null) { int _dealerResponsable = d.DealerId; vhsAOR.Dealer_IdResponsable = _dealerResponsable; } } if (valLineBefore == "Dealer Code and Name" && !val.StartsWith("AOR")) { vhsCrossSellDealerAOR = new CrossSellDealerAOR(); SDCManagementSystem_Test3Entities db = new SDCManagementSystem_Test3Entities(); DWT_Dealers d = db.DWT_Dealers.Where(x => x.Code == val.Substring(0, 6)).FirstOrDefault(); if (d != null) { _dealerid = d.DealerId; vhsCrossSellDealerAOR.Dealer_id = _dealerid; } vhsCrossSellDealerAOR.DealerCode = val.Substring(0, 6); int t = val.Length; vhsCrossSellDealerAOR.DealerName = val.Substring(7); vhsCrossSellDealerAOR.SalesFrom = _SalesFrom; vhsCrossSellDealerAOR.SalesThrough = _SalesThrough; vhsCrossSellDealerAOR.TotalSales = _ObjTotalSales; vhsCrossSellDealerAOR.AORName = vhsAOR.AORName; if (excelSheet.Cells[i, 2].Value2 != null) { vhsCrossSellDealerAOR.DealerCity = excelSheet.Cells[i, 2].Value.ToString(); } if (excelSheet.Cells[i, 3].Value2 != null) { if (int.TryParse(excelSheet.Cells[i, 3].Value.ToString(), out int _DealerSaler)) { vhsCrossSellDealerAOR.DealerSaler = _DealerSaler; } } if (excelSheet.Cells[i, 4].Value2 != null) { if (decimal.TryParse(excelSheet.Cells[i, 4].Value.ToString(), out decimal _pctAORSales)) { vhsCrossSellDealerAOR.pctAORSales = _pctAORSales * 100; } } } if (val.StartsWith("For")) { //vhsCrossSellDealerAOR.SalesFrom = DateTime.Parse(val.Substring(15, 10)); //vhsCrossSellDealerAOR.SalesThrough = DateTime.Parse(val.Substring(34, 10)); _SalesFrom = DateTime.Parse(val.Substring(15, 10)); _SalesThrough = DateTime.Parse(val.Substring(34, 10)); if (excelSheet.Cells[i, 5].Value2 != null) { if (int.TryParse(excelSheet.Cells[i, 5].Value.ToString(), out int _TotalSales)) { //vhsCrossSellDealerAOR.TotalSales = _TotalSales; _ObjTotalSales = _TotalSales; } } } if (val.StartsWith("Current Population")) { if (excelSheet.Cells[i, 2].Value2 != null) { if (int.TryParse(excelSheet.Cells[i, 2].Value.ToString(), out int _CurrentPopulation)) { vhsAOR.CurrentPopulation = _CurrentPopulation; } } if (excelSheet.Cells[i, 4].Value2 != null) { if (int.TryParse(excelSheet.Cells[i, 4].Value.ToString(), out int _C5YrsProyectedPopulation)) { vhsAOR.C5YrsProyectedPopulation = _C5YrsProyectedPopulation; } } } if (val.StartsWith("Current Households")) { if (excelSheet.Cells[i, 2].Value2 != null) { if (int.TryParse(excelSheet.Cells[i, 2].Value.ToString(), out int _CurrentHouseholds)) { vhsAOR.CurrentHouseholds = _CurrentHouseholds; } } if (excelSheet.Cells[i, 4].Value2 != null) { if (int.TryParse(excelSheet.Cells[i, 4].Value.ToString(), out int _C5YrsProyectedHouseholds)) { vhsAOR.C5YrsProyectedHouseholds = _C5YrsProyectedHouseholds; } } } if (val.StartsWith("Current HH Under $50,000")) { if (excelSheet.Cells[i, 2].Value2 != null) { if (int.TryParse(excelSheet.Cells[i, 2].Value.ToString(), out int _CurrentHouseholdsUnder50)) { vhsAOR.CurrentHouseholdsUnder50 = _CurrentHouseholdsUnder50; } } if (excelSheet.Cells[i, 4].Value2 != null) { if (int.TryParse(excelSheet.Cells[i, 4].Value.ToString(), out int C5YrsProyectedHouseholdsUnder50)) { vhsAOR.C5YrsProyectedHouseholdsUnder50 = C5YrsProyectedHouseholdsUnder50; } } } if (val.StartsWith("Current HH $50,000 - $100,000")) { if (excelSheet.Cells[i, 2].Value2 != null) { if (int.TryParse(excelSheet.Cells[i, 2].Value.ToString(), out int _CurrentHouseholds50to100)) { vhsAOR.CurrentHouseholds50to100 = _CurrentHouseholds50to100; } } if (excelSheet.Cells[i, 4].Value2 != null) { if (int.TryParse(excelSheet.Cells[i, 4].Value.ToString(), out int _C5YrsProyectedHouseholds50to100)) { vhsAOR.C5YrsProyectedHouseholds50to100 = _C5YrsProyectedHouseholds50to100; } } } if (val.StartsWith("Current HH Over $100,000")) { if (excelSheet.Cells[i, 2].Value2 != null) { if (int.TryParse(excelSheet.Cells[i, 2].Value.ToString(), out int _CurrentHouseholdsOver100)) { vhsAOR.CurrentHouseholdsOver100 = _CurrentHouseholdsOver100; } } if (excelSheet.Cells[i, 4].Value2 != null) { if (int.TryParse(excelSheet.Cells[i, 4].Value.ToString(), out int _C5YrsProyectedHouseholdsOver100)) { vhsAOR.C5YrsProyectedHouseholdsOver100 = _C5YrsProyectedHouseholdsOver100; } } } if (valLineBefore.Contains("DlrNum") && !val.StartsWith("Current Population")) { vhsAORDealerHistory = new AORDealerHistory(); vhsAORDealerHistory.AORName = vhsAOR.AORName; vhsAORDealerHistory.DealerCode = "0" + excelSheet.Cells[i, 1].Value.ToString(); vhsAORDealerHistory.DealerName = excelSheet.Cells[i, 2].Value.ToString(); vhsAORDealerHistory.DealerCity = excelSheet.Cells[i, 3].Value.ToString(); if (excelSheet.Cells[i, 4].Value2 != null) { if (DateTime.TryParse(excelSheet.Cells[i, 4].Value.ToString(), out DateTime _ApptDate)) { vhsAORDealerHistory.ApptDate = _ApptDate; } } if (excelSheet.Cells[i, 5].Value2 != null) { if (DateTime.TryParse(excelSheet.Cells[i, 5].Value.ToString(), out DateTime _TermDate)) { vhsAORDealerHistory.TermDate = _TermDate; } } } } if (val.StartsWith("DlrNum")) { valLineBefore = val; } if (valLineBefore.StartsWith("DlrNum") && val.StartsWith("Current Population")) { valLineBefore = string.Empty; } if (valLineBefore == string.Empty && val.StartsWith("Dealer Code and Name")) { valLineBefore = "Dealer Code and Name"; } if (valLineBefore == "Dealer Code and Name" && val.StartsWith("AOR")) { valLineBefore = string.Empty; } if (vhsAOR != null) { listAOR.Add(vhsAOR); } if (vhsAORDealerHistory != null) { listAORH.Add(vhsAORDealerHistory); vhsAORDealerHistory = null; } if (vhsCrossSellDealerAOR != null) { listAORCrossSale.Add(vhsCrossSellDealerAOR); } } } } object misValue = System.Reflection.Missing.Value; wb.Close(false, misValue, misValue); excel.Quit(); using (SDCManagementSystem_Test3Entities db = new SDCManagementSystem_Test3Entities()) { foreach (AOR dealer in listAOR) { if (dealer.AORName != string.Empty) { AOR vhs = db.AORs.Where( x => x.AORName == dealer.AORName).FirstOrDefault(); if (vhs != null) { //modify record vhs.CurrentPopulation = dealer.CurrentPopulation; vhs.C5YrsProyectedPopulation = dealer.C5YrsProyectedPopulation; vhs.CurrentHouseholds = dealer.CurrentHouseholds; vhs.C5YrsProyectedHouseholds = dealer.C5YrsProyectedHouseholds; vhs.CurrentHouseholdsUnder50 = dealer.CurrentHouseholdsUnder50; vhs.C5YrsProyectedHouseholdsUnder50 = dealer.C5YrsProyectedHouseholdsUnder50; vhs.CurrentHouseholds50to100 = dealer.CurrentHouseholds50to100; vhs.C5YrsProyectedHouseholds50to100 = dealer.C5YrsProyectedHouseholds50to100; vhs.CurrentHouseholdsOver100 = dealer.CurrentHouseholdsOver100; vhs.C5YrsProyectedHouseholdsOver100 = dealer.C5YrsProyectedHouseholdsOver100; updatedRow++; } else { db.AORs.Add(dealer); newRows++; } } else { db.AORs.Add(dealer); newRows++; } } try { db.SaveChanges(); } catch (Exception ex) { throw ex; } foreach (AORDealerHistory dealer in listAORH) { if (dealer.AORName != null && dealer.AORName != string.Empty) { AORDealerHistory vhs = db.AORDealerHistories.Where( x => x.AORName == dealer.AORName).FirstOrDefault(); if (vhs != null) { //modify record vhs.DealerCode = dealer.DealerCode; vhs.DealerName = dealer.DealerName; vhs.DealerCity = dealer.DealerCity; vhs.ApptDate = dealer.ApptDate; vhs.TermDate = dealer.TermDate; updatedRow++; } else { db.AORDealerHistories.Add(dealer); newRows++; } } } try { db.SaveChanges(); } catch (Exception ex) { throw ex; } foreach (CrossSellDealerAOR dealer in listAORCrossSale) { if (dealer.Dealer_id != 0) { CrossSellDealerAOR vhs = db.CrossSellDealerAORs.Where( x => x.Dealer_id == dealer.Dealer_id && x.SalesFrom == dealer.SalesFrom && x.SalesThrough == dealer.SalesThrough).FirstOrDefault(); if (vhs != null) { //modify record vhs.TotalSales = dealer.TotalSales; vhs.DealerCode = dealer.DealerCode; vhs.DealerName = dealer.DealerName; vhs.DealerCity = dealer.DealerCity; vhs.DealerSaler = dealer.DealerSaler; vhs.pctAORSales = dealer.pctAORSales; updatedRow++; } else { db.CrossSellDealerAORs.Add(dealer); newRows++; } } else { db.CrossSellDealerAORs.Add(dealer); newRows++; } } try { db.SaveChanges(); } catch (Exception ex) { throw ex; } } //End of Entity result.Add("The " + filePath + " file was uploaded: New Row added: " + newRows.ToString() + " Row updated: " + updatedRow.ToString() + Environment.NewLine); } // End If File>exits #endregion return(result); }