private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e) { List <PriceErrorList> list = new List <PriceErrorList>(); try { CallParameter cp3 = (CallParameter)e.Argument; ExcelTable = cr.readExcelFileToDataTable(cp3.FileName, "QuantitySheet$"); if (ExcelTable != null && ExcelTable.Columns.Count == 3) { if (ExcelTable.Columns[0].ColumnName.Trim().ToUpper() != "HOMEPRODUCTID" || ExcelTable.Columns[1].ColumnName.Trim().ToUpper() != "PRODUCTAREAGROUPID" || ExcelTable.Columns[2].ColumnName.Trim().ToUpper() != "QUANTITY" ) { PriceErrorList l = new PriceErrorList(); l.MessageText = "Column name or number does not match standard format."; list.Add(l); e.Result = list; } else { e.Result = IsSourceValidate(ExcelTable); } } else { imagebox.Visibility = Visibility.Collapsed; MessageBox.Show("Can not find correct format sheet to import."); btnContinue.IsEnabled = false; } } catch (Exception ex) { MessageBox.Show(ex.Message); btnContinue.IsEnabled = false; } }
private List <PriceErrorList> IsSourceValidate(System.Data.DataTable dtable) { List <PriceErrorList> listerror = new List <PriceErrorList>(); // step 1 validate product id duplication if (dtable.Rows.Count > 0) { foreach (DataRow dr in dtable.Rows) { if (dr["HomeProductID"] == null || dr["HomeProductID"].ToString() == "" //dr["ProductAreaGroupID"] == null || dr["ProductAreaGroupID"].ToString() == "" || //dr["Quantity"] == null || dr["Quantity"].ToString() == "" ) { PriceErrorList em = new PriceErrorList(); em.ProductID = dr["HomeProductID"].ToString(); em.MessageText = @"There are some missing value for this home."; em.goAhead = false; listerror.Add(em); } try { int eff = int.Parse(dr["productareagroupid"].ToString()); } catch (Exception ex) { PriceErrorList em = new PriceErrorList(); em.ProductID = dr["HomeProductID"].ToString(); em.ProductAreaGroupID = dr["productareagroupid"].ToString(); em.goAhead = false; em.MessageText = dr["productareagroupid"].ToString() + @" is an invalid PAG. It should be an integer."; listerror.Add(em); } try { double eff2 = double.Parse(dr["Quantity"].ToString()); } catch (Exception ex) { PriceErrorList em = new PriceErrorList(); em.ProductID = dr["HomeProductID"].ToString(); em.ProductAreaGroupID = dr["productareagroupid"].ToString(); em.MessageText = dr["Quantity"].ToString() + @" is an invalid quantity. It should be a decimal."; em.goAhead = false; listerror.Add(em); } } DataSet ds = cr.ValidateQuantity(dtable, usercode); if (ds.Tables[0].Rows.Count > 0) { foreach (DataRow dr in ds.Tables[0].Rows) { PriceErrorList em = new PriceErrorList(); em.ProductID = dr["HomeProductID"].ToString(); em.ProductAreaGroupID = dr["productareagroupid"].ToString(); em.goAhead = true; em.MessageText = "This product (" + dr["ProductID"].ToString() + ") was not configured to home. If you process it will be ignored."; listerror.Add(em); } } } else { PriceErrorList em = new PriceErrorList(); em.ProductID = ""; em.MessageText = @"The excel file is blank."; em.goAhead = false; listerror.Add(em); } return(listerror); }
private List <PriceErrorList> IsHorizontalSourceValidate(System.Data.DataTable dtable) { List <PriceErrorList> listerror = new List <PriceErrorList>(); // step 1 validate product id duplication if (dtable.Rows.Count > 0) { if (stateid == 3 || stateid == 2) { foreach (DataRow dr in dtable.Rows) { #region validate productid if (dr["productid"] == null || dr["productid"].ToString() == "") { PriceErrorList em = new PriceErrorList(); em.ProductID = ""; em.MessageText = @"ProductID is missing."; DateTime tempdt; try { tempdt = DateTime.Parse(dr["effectivedate"].ToString()); em.EffectiveDateString = tempdt.ToString("dd/MM/yyyy"); } catch (Exception exx2) { tempdt = DateTime.MinValue; em.EffectiveDateString = ""; } em.ProductID = dr["productid"].ToString(); em.EffectiveDate = tempdt; em.EffectiveDate = tempdt; listerror.Add(em); } if (dr["productid"] != null && dr["productid"].ToString() != "" && dr["productid"].ToString().Length > 15) { PriceErrorList em = new PriceErrorList(); em.ProductID = dr["productid"].ToString(); em.MessageText = @"Product ID length over 15 characters."; DateTime tempdt; try { tempdt = DateTime.Parse(dr["effectivedate"].ToString()); em.EffectiveDateString = tempdt.ToString("dd/MM/yyyy"); } catch (Exception exx2) { tempdt = DateTime.MinValue; em.EffectiveDateString = ""; } em.EffectiveDate = tempdt; listerror.Add(em); } #endregion #region validate effective date try { DateTime eff = DateTime.Parse(dr["effectivedate"].ToString()); } catch (Exception ex) { PriceErrorList em = new PriceErrorList(); em.ProductID = dr["productid"].ToString(); em.Region = ""; DateTime tempdt; try { tempdt = DateTime.Parse(dr["effectivedate"].ToString()); em.EffectiveDateString = tempdt.ToString("dd/MM/yyyy"); } catch (Exception exx2) { tempdt = DateTime.MinValue; em.EffectiveDateString = ""; } em.EffectiveDate = tempdt; em.MessageText = "Effective date is invalid."; listerror.Add(em); } #endregion #region validate derivedcost value if (dr["derivedcost"].ToString().ToUpper() != "Y" && dr["derivedcost"].ToString().ToUpper() != "N") { PriceErrorList em = new PriceErrorList(); em.ProductID = dr["productid"].ToString(); em.Region = ""; DateTime tempdt; try { tempdt = DateTime.Parse(dr["effectivedate"].ToString()); em.EffectiveDateString = tempdt.ToString("dd/MM/yyyy"); } catch (Exception exx2) { tempdt = DateTime.MinValue; em.EffectiveDateString = ""; } em.ProductID = dr["productid"].ToString(); em.EffectiveDate = tempdt; em.MessageText = "Derived cost '" + dr["derivedcost"].ToString() + "' is invalid. The value should be Y or N."; listerror.Add(em); } #endregion } #region validate productid string tempname = ""; foreach (DataRow dr in dtable.Rows) { #endregion foreach (CommonResource.Region s in cr.SQSRegion) { #region validate direct build cost tempname = s.RegionCode.TrimEnd().TrimStart() + "_DirectBuildCost"; try { double cost = double.Parse(dr[tempname].ToString().Replace("$", "").Replace(",", "").TrimEnd().TrimStart()); } catch (Exception ex) { PriceErrorList em = new PriceErrorList(); em.ProductID = dr["productid"].ToString(); em.Region = s.RegionName; DateTime tempdt; try { tempdt = DateTime.Parse(dr["effectivedate"].ToString()); em.EffectiveDateString = tempdt.ToString("dd/MM/yyyy"); } catch (Exception exx2) { tempdt = DateTime.MinValue; em.EffectiveDateString = ""; } em.ProductID = dr["productid"].ToString(); em.EffectiveDate = tempdt; em.EffectiveDate = tempdt; em.MessageText = @"Direct Build Cost " + dr[tempname].ToString() + @" is invalid. Please enter a number."; listerror.Add(em); } #endregion #region validate BTP cost //tempname = s.RegionCode.TrimEnd().TrimStart() + "_CMAPercent"; //try //{ // double cmapercent = double.Parse(dr[tempname].ToString().TrimEnd().TrimStart()); //} //catch (Exception ex) //{ // PriceErrorList em = new PriceErrorList(); // em.ProductID = dr["productid"].ToString(); // em.Region = s.RegionName; // DateTime tempdt; // try // { // tempdt = DateTime.Parse(dr["effectivedate"].ToString()); // em.EffectiveDateString = tempdt.ToString("dd/MM/yyyy"); // } // catch (Exception exx2) // { // tempdt = DateTime.MinValue; // em.EffectiveDateString = ""; // } // em.ProductID = dr["productid"].ToString(); // em.EffectiveDate = tempdt; // em.EffectiveDate = tempdt; // em.MessageText = @"CMA percent " + dr[tempname].ToString() + @" is invalid. Please enter a number."; // listerror.Add(em); //} tempname = s.RegionCode.TrimEnd().TrimStart() + "_BuildingTransferCost"; try { double cost = double.Parse(dr[tempname].ToString().Replace("$", "").Replace(",", "").TrimEnd().TrimStart()); } catch (Exception ex) { PriceErrorList em = new PriceErrorList(); em.ProductID = dr["productid"].ToString(); em.Region = s.RegionName; DateTime tempdt; try { tempdt = DateTime.Parse(dr["effectivedate"].ToString()); em.EffectiveDateString = tempdt.ToString("dd/MM/yyyy"); } catch (Exception exx2) { tempdt = DateTime.MinValue; em.EffectiveDateString = ""; } em.ProductID = dr["productid"].ToString(); em.EffectiveDate = tempdt; em.EffectiveDate = tempdt; em.MessageText = @"Building Transfer Cost " + dr[tempname].ToString() + @" is invalid. Please enter a number."; listerror.Add(em); } #endregion #region validate target margin percent tempname = s.RegionCode.TrimEnd().TrimStart() + "_TargetMarginPercent"; try { double targetmarginpercent = double.Parse(dr[tempname].ToString().TrimEnd().TrimStart()); } catch (Exception ex) { PriceErrorList em = new PriceErrorList(); em.ProductID = dr["productid"].ToString(); em.Region = s.RegionName; DateTime tempdt; try { tempdt = DateTime.Parse(dr["effectivedate"].ToString()); em.EffectiveDateString = tempdt.ToString("dd/MM/yyyy"); } catch (Exception exx2) { tempdt = DateTime.MinValue; em.EffectiveDateString = ""; } em.ProductID = dr["productid"].ToString(); em.EffectiveDate = tempdt; em.EffectiveDate = tempdt; em.MessageText = @"Target Margin Percent " + dr[tempname].ToString() + @" is invalid. Please enter a number."; listerror.Add(em); } #endregion #region validate sell price tempname = s.RegionCode.TrimEnd().TrimStart() + "_SellPrice"; try { double sell = double.Parse(dr[tempname].ToString().Replace("$", "").Replace(",", "").TrimEnd().TrimStart()); } catch (Exception ex) { PriceErrorList em = new PriceErrorList(); em.ProductID = dr["productid"].ToString(); em.Region = s.RegionName; DateTime tempdt; try { tempdt = DateTime.Parse(dr["effectivedate"].ToString()); em.EffectiveDateString = tempdt.ToString("dd/MM/yyyy"); } catch (Exception exx2) { tempdt = DateTime.MinValue; em.EffectiveDateString = ""; } em.ProductID = dr["productid"].ToString(); em.EffectiveDate = tempdt; em.EffectiveDate = tempdt; em.MessageText = @"Sell price " + dr[tempname].ToString() + @" is invalid. Please enter a number."; listerror.Add(em); } #endregion } } #region validate duplication based on productid, effectivedate and regionid var ss = from c in dtable.AsEnumerable() group c by new { productid = c["productid"], effectivedate = c["effectivedate"] } into newdr where newdr.Count() > 1 select new { productid = newdr.Key.productid, effectivedate = newdr.Key.effectivedate, totalcout = newdr.Count() }; foreach (var row in ss) { PriceErrorList em = new PriceErrorList(); em.ProductID = row.productid.ToString(); DateTime tempdt; try { tempdt = (DateTime)row.effectivedate; em.EffectiveDateString = tempdt.ToString("dd/MM/yyyy"); } catch (Exception exx2) { tempdt = DateTime.MinValue; em.EffectiveDateString = ""; } em.EffectiveDate = tempdt; em.MessageText = row.totalcout.ToString() + " Duplicate records found."; listerror.Add(em); } #endregion #region validate against database to check duplication if (listerror.Count == 0) { DataTable newformat = cr.ConvertToImportFormatTable(dtable); DataSet ds = ValidatePriceSheetWithDatabase(newformat); if (ds.Tables[0].Rows.Count > 0) { foreach (DataRow dr in ds.Tables[0].Rows) { PriceErrorList em = new PriceErrorList(); em.ProductID = dr["productid"].ToString(); em.Region = dr["regionname"].ToString(); DateTime tempdt; try { tempdt = DateTime.Parse(dr["effectivedate"].ToString()); em.EffectiveDateString = tempdt.ToString("dd/MM/yyyy"); } catch (Exception exx2) { tempdt = DateTime.MinValue; em.EffectiveDateString = ""; } em.EffectiveDate = tempdt; em.MessageText = "The price exists based on product, region and effective date. Please either deactivate current price or remove this price from import file and try again."; listerror.Add(em); } } //else //{ // PriceErrorList em = new PriceErrorList(); // em.ProductID = ""; // em.MessageText = @"Please log on as QLD or NSW user to import this file."; // listerror.Add(em); //} } #endregion } else { PriceErrorList em = new PriceErrorList(); em.ProductID = ""; em.MessageText = @"The excel file is blank."; listerror.Add(em); } } return(listerror); }
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e) { List <PriceErrorList> list = new List <PriceErrorList>(); SQSAdminServiceClient client = new SQSAdminServiceClient(); client.Endpoint.Address = new System.ServiceModel.EndpointAddress(CommonVariables.WcfEndpoint); try { CallParameter cp3 = (CallParameter)e.Argument; ExcelTable = cr.readExcelFileToDataTable(cp3.FileName, "PriceSheet$"); if (ExcelTable != null && (ExcelTable.Columns.Count == 8 || ExcelTable.Columns.Count == 68 || ExcelTable.Columns.Count == 16)) { if (ExcelTable.Columns.Count == 8)// vertical format { if (ExcelTable.Columns[0].ColumnName.Trim().ToUpper() != "PRODUCTID" || ExcelTable.Columns[1].ColumnName.Trim().ToUpper() != "REGIONNAME" || ExcelTable.Columns[2].ColumnName.Trim().ToUpper() != "EFFECTIVEDATE" || ExcelTable.Columns[3].ColumnName.Trim().ToUpper() != "DIRECTBUILDCOST" || ExcelTable.Columns[4].ColumnName.Trim().ToUpper() != "BUILDINGTRANSFERCOST" || ExcelTable.Columns[5].ColumnName.Trim().ToUpper() != "SELLPRICE" || ExcelTable.Columns[6].ColumnName.Trim().ToUpper() != "TARGETMARGINPERCENT" || ExcelTable.Columns[7].ColumnName.Trim().ToUpper() != "DERIVEDCOST" ) { PriceErrorList l = new PriceErrorList(); l.MessageText = "Column name or number does not match standard format. Please follow the sample data sheet format."; list.Add(l); e.Result = list; } else { e.Result = IsSourceValidate(ExcelTable); } } else if ((ExcelTable.Columns.Count == 16 && stateid == 2) || (ExcelTable.Columns.Count == 68 && stateid == 3))// NSW/QLD horizontal format { DataSet dsTemp = client.SQSAdmin_Generic_GetRegionList(stateid); string[] colnamearray = new string[dsTemp.Tables[0].Rows.Count * 4 + 4]; int idx = 3; foreach (DataRow dr in dsTemp.Tables[0].Rows) { colnamearray[idx] = dr["BCCode"].ToString().TrimStart().TrimEnd() + "_DIRECTBUILDCOST"; colnamearray[idx + dsTemp.Tables[0].Rows.Count] = dr["BCCode"].ToString().TrimStart().TrimEnd() + "_BUILDINGTRANSFERCOST"; colnamearray[idx + dsTemp.Tables[0].Rows.Count * 2] = dr["BCCode"].ToString().TrimStart().TrimEnd() + "_TARGETMARGINPERCENT"; colnamearray[idx + dsTemp.Tables[0].Rows.Count * 3] = dr["BCCode"].ToString().TrimStart().TrimEnd() + "_SELLPRICE"; idx++; } if (ExcelTable.Columns[0].ColumnName.Trim().ToUpper() != "PRODUCTID" || ExcelTable.Columns[1].ColumnName.Trim().ToUpper() != "HOUSETYPE" || ExcelTable.Columns[2].ColumnName.Trim().ToUpper() != "EFFECTIVEDATE" || ExcelTable.Columns[ExcelTable.Columns.Count - 1].ColumnName.Trim().ToUpper() != "DERIVEDCOST") { PriceErrorList l = new PriceErrorList(); l.MessageText = "Column name or number does not match standard format."; list.Add(l); e.Result = list; } else { for (int i = 3; i < ExcelTable.Columns.Count - 2; i++) { if (ExcelTable.Columns[i].ColumnName.Trim().ToUpper() != colnamearray[i]) { PriceErrorList l = new PriceErrorList(); l.MessageText = "Column name or number does not match standard format."; list.Add(l); e.Result = list; break; } } } e.Result = IsHorizontalSourceValidate(ExcelTable); } else { PriceErrorList l = new PriceErrorList(); l.MessageText = "Column name or number does not match standard format."; list.Add(l); e.Result = list; } } else { PriceErrorList l = new PriceErrorList(); l.MessageText = "Excel provided does not match the standard format."; list.Add(l); e.Result = list; } } catch (Exception ex) { PriceErrorList l = new PriceErrorList(); l.MessageText = ex.Message; list.Add(l); e.Result = list; } }
private List <PriceErrorList> IsSourceValidate(System.Data.DataTable dtable) { List <PriceErrorList> listerror = new List <PriceErrorList>(); // step 1 validate product id duplication if (dtable.Rows.Count > 0) { // step 1 validate region value var regionList = from c in cr.SQSRegion select c.RegionName; var sourceregionlist = from dr in dtable.AsEnumerable() select dr.Field <string>("regionname"); var nonexist = sourceregionlist.Except(regionList); var ps = from dr in dtable.AsEnumerable() join nx in nonexist on dr["regionname"] equals nx.ToString() select new { productid = dr["productid"], region = dr["regionname"], effectivedate = dr["effectivedate"] }; foreach (var row in ps) { PriceErrorList em = new PriceErrorList(); em.ProductID = row.productid.ToString(); em.Region = row.region.ToString(); DateTime tempdt; try { tempdt = DateTime.Parse(row.effectivedate.ToString()); em.EffectiveDateString = tempdt.ToString("dd/MM/yyyy"); } catch (Exception exx2) { tempdt = DateTime.MinValue; em.EffectiveDateString = ""; } em.EffectiveDate = tempdt; em.EffectiveDateString = (DateTime.Parse(row.effectivedate.ToString())).ToString("dd/MM/yyyy"); em.MessageText = row.region.ToString() + @" is an invalid price region name. Please check sample import file for valid value."; listerror.Add(em); } // step 2 validate costprice foreach (DataRow dr in dtable.Rows) { if (dr["productid"] == null || dr["productid"].ToString() == "" || dr["regionname"] == null || dr["regionname"].ToString() == "" || dr["DerivedCost"] == null || dr["DerivedCost"].ToString() == "" ) { PriceErrorList em = new PriceErrorList(); DateTime tempdt; try { tempdt = DateTime.Parse(dr["effectivedate"].ToString()); em.EffectiveDateString = tempdt.ToString("dd/MM/yyyy"); } catch (Exception exx2) { tempdt = DateTime.MinValue; em.EffectiveDateString = ""; } em.ProductID = dr["productid"].ToString(); em.EffectiveDate = tempdt; em.MessageText = @"There are some missing value for this product."; listerror.Add(em); } try { double cost = double.Parse(dr["DirectBuildCost"].ToString().Replace("$", "").Replace(",", "")); } catch (Exception ex) { PriceErrorList em = new PriceErrorList(); em.ProductID = dr["productid"].ToString(); em.Region = dr["regionname"].ToString(); DateTime tempdt; try { tempdt = DateTime.Parse(dr["effectivedate"].ToString()); em.EffectiveDateString = tempdt.ToString("dd/MM/yyyy"); } catch (Exception exx2) { tempdt = DateTime.MinValue; em.EffectiveDateString = ""; } em.EffectiveDate = tempdt; em.MessageText = @"Direct Build Cost " + dr["DirectBuildCost"].ToString() + @" is invalid. Please enter a number."; listerror.Add(em); } } // step 3 validate sell price foreach (DataRow dr in dtable.Rows) { try { double sellprice = double.Parse(dr["SellPrice"].ToString().Replace("$", "").Replace(",", "")); } catch (Exception ex) { PriceErrorList em = new PriceErrorList(); em.ProductID = dr["productid"].ToString(); em.Region = dr["regionname"].ToString(); DateTime tempdt; try { tempdt = DateTime.Parse(dr["effectivedate"].ToString()); em.EffectiveDateString = tempdt.ToString("dd/MM/yyyy"); } catch (Exception exx2) { tempdt = DateTime.MinValue; em.EffectiveDateString = ""; } em.EffectiveDate = tempdt; em.MessageText = @"Sell price " + dr["sellprice"].ToString() + @" is invalid. Please enter a number."; listerror.Add(em); } } // step 4 validate derived cost foreach (DataRow dr in dtable.Rows) { if (dr["derivedcost"].ToString().ToUpper() != "Y" && dr["derivedcost"].ToString().ToUpper() != "N") { PriceErrorList em = new PriceErrorList(); em.ProductID = dr["productid"].ToString(); em.Region = dr["regionname"].ToString(); DateTime tempdt; try { tempdt = DateTime.Parse(dr["effectivedate"].ToString()); em.EffectiveDateString = tempdt.ToString("dd/MM/yyyy"); } catch (Exception exx2) { tempdt = DateTime.MinValue; em.EffectiveDateString = ""; } em.EffectiveDate = tempdt; em.MessageText = @"Derived cost " + dr["derivedcost"].ToString() + @" is invalid. it should be Y or N."; listerror.Add(em); } } // step 5 validate effective date foreach (DataRow dr in dtable.Rows) { try { DateTime eff = DateTime.Parse(dr["effectivedate"].ToString()); } catch (Exception ex) { PriceErrorList em = new PriceErrorList(); em.ProductID = dr["productid"].ToString(); em.Region = dr["regionname"].ToString(); DateTime tempdt; try { tempdt = DateTime.Parse(dr["effectivedate"].ToString()); em.EffectiveDateString = tempdt.ToString("dd/MM/yyyy"); } catch (Exception exx2) { tempdt = DateTime.MinValue; em.EffectiveDateString = ""; } em.EffectiveDate = tempdt; em.MessageText = "Effective date is invalid."; listerror.Add(em); } } // step 5.5 validate btp margin foreach (DataRow dr in dtable.Rows) { if (dr["productid"] == null || dr["productid"].ToString() == "" || dr["DerivedCost"] == null || dr["DerivedCost"].ToString() == "" ) { PriceErrorList em = new PriceErrorList(); DateTime tempdt; try { tempdt = DateTime.Parse(dr["effectivedate"].ToString()); em.EffectiveDateString = tempdt.ToString("dd/MM/yyyy"); } catch (Exception exx2) { tempdt = DateTime.MinValue; em.EffectiveDateString = ""; } em.ProductID = dr["productid"].ToString(); em.EffectiveDate = tempdt; em.MessageText = @"There are some missing value for this product."; listerror.Add(em); } try { double cost = double.Parse(dr["BuildingTransferCost"].ToString().Replace("$", "").Replace(",", "")); } catch (Exception ex) { PriceErrorList em = new PriceErrorList(); em.ProductID = dr["productid"].ToString(); em.Region = dr["regionname"].ToString(); DateTime tempdt; try { tempdt = DateTime.Parse(dr["effectivedate"].ToString()); em.EffectiveDateString = tempdt.ToString("dd/MM/yyyy"); } catch (Exception exx2) { tempdt = DateTime.MinValue; em.EffectiveDateString = ""; } em.EffectiveDate = tempdt; em.MessageText = @"Building Transfer Cost " + dr["BuildingTransferCost"].ToString() + @" is invalid. Please enter a number."; listerror.Add(em); } } // step 5.5 validate target margin foreach (DataRow dr in dtable.Rows) { try { double targetmarginpercent = double.Parse(dr["TargetMarginPercent"].ToString()); } catch (Exception ex) { PriceErrorList em = new PriceErrorList(); em.ProductID = dr["productid"].ToString(); em.Region = dr["regionname"].ToString(); DateTime tempdt; try { tempdt = DateTime.Parse(dr["effectivedate"].ToString()); em.EffectiveDateString = tempdt.ToString("dd/MM/yyyy"); } catch (Exception ex5) { tempdt = DateTime.MinValue; em.EffectiveDateString = ""; } em.EffectiveDate = tempdt; em.MessageText = @"TargetMarginPercent " + dr["TargetMarginPercent"].ToString() + @" is invalid. Please enter a number."; listerror.Add(em); } } //step 6 validate duplication based on productid, effectivedate and regionid on excel var ss = from c in dtable.AsEnumerable() group c by new { productid = c["productid"], regionname = c["regionname"], effectivedate = c["effectivedate"] } into newdr where newdr.Count() > 1 select new { productid = newdr.Key.productid, regionname = newdr.Key.regionname, effectivedate = newdr.Key.effectivedate, totalcout = newdr.Count() }; foreach (var row in ss) { PriceErrorList em = new PriceErrorList(); em.ProductID = row.productid.ToString(); DateTime tempdt; try { tempdt = (DateTime)row.effectivedate; em.EffectiveDateString = tempdt.ToString("dd/MM/yyyy"); } catch (Exception exx2) { tempdt = DateTime.MinValue; em.EffectiveDateString = ""; } em.EffectiveDate = tempdt; em.Region = row.regionname.ToString(); em.MessageText = row.totalcout.ToString() + " Duplicate records found."; listerror.Add(em); } // step 7 validate against database to check duplication if (listerror.Count == 0) { DataSet ds = ValidatePriceSheetWithDatabase(dtable); if (ds.Tables[0].Rows.Count > 0) { foreach (DataRow dr in ds.Tables[0].Rows) { PriceErrorList em = new PriceErrorList(); em.ProductID = dr["productid"].ToString(); em.Region = dr["regionname"].ToString(); DateTime tempdt; try { tempdt = DateTime.Parse(dr["effectivedate"].ToString()); em.EffectiveDateString = tempdt.ToString("dd/MM/yyyy"); } catch (Exception exx2) { tempdt = DateTime.MinValue; em.EffectiveDateString = ""; } em.EffectiveDate = tempdt; em.MessageText = "The price exists based on product, region and effective date. Please either deactivate current price or remove this price from import file and try again."; listerror.Add(em); } } } } else { PriceErrorList em = new PriceErrorList(); em.ProductID = ""; em.MessageText = @"The excel file is blank."; listerror.Add(em); } return(listerror); }