public static void ProcessClientChangeFile(Stream fileStream, List<BusinessDataColumnBuilder> extraColumns, BusinessDataTableMetaData clientMetaData, string uploadCode, string sheetName, string fileName) { try { var excelDataSet = ExcelProcessor.GetDataSetFromExcelStream(fileStream); if (excelDataSet == null) { return; } var excelDataTable = excelDataSet.Tables[sheetName]; if (excelDataTable == null) { return; } var clientChangeTable = (excelDataTable.Rows.Cast<DataRow>().Where(row => row.ItemArray.Any(field => !(field is DBNull)))).CopyToDataTable(); ExcelProcessor.DropEmptyColumns(clientChangeTable); ExcelProcessor.AddExtraColumns(clientChangeTable, extraColumns); ExcelProcessor.DropUnmatchedColumns(clientChangeTable, clientMetaData); var validationErrors = new List<BusinessExcelValidationRecord>(); validationErrors.AddRange(ExcelProcessor.ValidateRequiredColumns(clientChangeTable, clientMetaData)); validationErrors.AddRange(ExcelProcessor.ValidateDataTypes(clientChangeTable, clientMetaData)); validationErrors.AddRange(ExcelProcessor.ValidateRequiredColumns(clientChangeTable, clientMetaData)); if (validationErrors.Count > 0) { ExcelProcessor.WriteExcelValidationErrors(validationErrors, uploadCode, fileName); } else { DynamicEntity.ClearTable(clientMetaData.Name, DALISGDatabaseType.ISGInput); ExcelProcessor.BulkInsert(clientChangeTable, clientMetaData, clientMetaData.Name, BusinessISGDatabaseType.ISGInput); if (clientChangeTable.Columns.Contains("YearQuater") && clientChangeTable.Rows != null && clientChangeTable.Rows.Count > 0) { var yearQuarter = clientChangeTable.Rows[0]["YearQuater"]; DALClient.ClientMaintenance(clientMetaData.Name, (yearQuarter == null) ? string.Empty : yearQuarter.ToString()); } ExcelProcessor.CheckForExcelValidationSuccess(uploadCode); } excelDataSet.Dispose(); } catch (Exception ex) { ExcelProcessor.WriteExcelValidationSystemException(ex, uploadCode); } }
public static List<BusinessExcelValidationRecord> ValidateRequiredColumns(DataTable changeTable, BusinessDataTableMetaData metaData) { var rtrn = new List<BusinessExcelValidationRecord>(); // ReSharper disable once LoopCanBeConvertedToQuery foreach (var metaColumn in metaData.Columns.Where(x => x.IsNullable == false && x.IsReadonlyIdentity == false)) { if (changeTable.Columns[metaColumn.ExcelColumn] == null && changeTable.Columns[metaColumn.Name] == null) { rtrn.Add(new BusinessExcelValidationRecord(changeTable.TableName, metaColumn.FriendlyName, BusinessExcelValidationResultType.RequiredColumnMissing)); } } return rtrn; }
public static List<BusinessExcelValidationRecord> ValidateDataTypes(DataTable changeTable, BusinessDataTableMetaData metaData) { var rtrn = new List<BusinessExcelValidationRecord>(); for (var i = 0; i < changeTable.Rows.Count; i++) { var row = changeTable.Rows[i]; var excelRowIndex = i + 2; foreach (DataColumn column in changeTable.Columns) { var metaDataColumn = metaData.Columns.FirstOrDefault(x => string.Equals(x.ExcelColumn, column.ColumnName, StringComparison.CurrentCultureIgnoreCase) && x.StrongType != typeof(string)); if ((metaDataColumn == null) || ((string.IsNullOrEmpty(row[metaDataColumn.ExcelColumn].ToString()) && metaDataColumn.IsNullable))) { continue; } var valid = true; if (metaDataColumn.StrongType == typeof(DateTime)) { DateTime testValue; valid = DateTime.TryParse(row[metaDataColumn.ExcelColumn].ToString(), out testValue); } else if (metaDataColumn.StrongType == typeof(long)) { long testValue; row[metaDataColumn.ExcelColumn] = UpdateWholeNumberTemplates(row[metaDataColumn.ExcelColumn].ToString()); valid = long.TryParse(row[metaDataColumn.ExcelColumn].ToString().ToLower().Replace("%", ""), NumberStyles.Any, null, out testValue); if (valid && row[metaDataColumn.ExcelColumn].ToString().Contains("%")) { row[metaDataColumn.ExcelColumn] = testValue / 100; } } else if (metaDataColumn.StrongType == typeof(bool)) { bool testValue; valid = bool.TryParse(row[metaDataColumn.ExcelColumn].ToString(), out testValue); } else if (metaDataColumn.StrongType == typeof(DateTimeOffset)) { DateTimeOffset testValue; valid = DateTimeOffset.TryParse(row[metaDataColumn.ExcelColumn].ToString(), out testValue); } else if (metaDataColumn.StrongType == typeof(decimal)) { decimal testValue; row[metaDataColumn.ExcelColumn] = UpdateDecimalTemplates(row[metaDataColumn.ExcelColumn].ToString()); valid = decimal.TryParse(row[metaDataColumn.ExcelColumn].ToString().ToLower().Replace("%", ""), NumberStyles.Any, null, out testValue); if (valid && row[metaDataColumn.ExcelColumn].ToString().Contains("%")) { row[metaDataColumn.ExcelColumn] = testValue / 100; } } else if (metaDataColumn.StrongType == typeof(double)) { double testValue; row[metaDataColumn.ExcelColumn] = UpdateDecimalTemplates(row[metaDataColumn.ExcelColumn].ToString()); valid = double.TryParse(row[metaDataColumn.ExcelColumn].ToString().ToLower().Replace("%", ""), NumberStyles.Any, null, out testValue); if (valid && row[metaDataColumn.ExcelColumn].ToString().Contains("%")) { row[metaDataColumn.ExcelColumn] = testValue / 100; } } else if (metaDataColumn.StrongType == typeof(int)) { int testValue; row[metaDataColumn.ExcelColumn] = UpdateWholeNumberTemplates(row[metaDataColumn.ExcelColumn].ToString()); valid = int.TryParse(row[metaDataColumn.ExcelColumn].ToString().ToLower().Replace("%", ""), NumberStyles.Any, null, out testValue); if (valid && row[metaDataColumn.ExcelColumn].ToString().Contains("%")) { row[metaDataColumn.ExcelColumn] = testValue / 100; } } else if (metaDataColumn.StrongType == typeof(short)) { short testValue; row[metaDataColumn.ExcelColumn] = UpdateWholeNumberTemplates(row[metaDataColumn.ExcelColumn].ToString()); valid = short.TryParse(row[metaDataColumn.ExcelColumn].ToString().ToLower().Replace("%", ""), NumberStyles.Any, null, out testValue); if (valid && row[metaDataColumn.ExcelColumn].ToString().Contains("%")) { row[metaDataColumn.ExcelColumn] = testValue / 100; } } else if (metaDataColumn.StrongType == typeof(TimeSpan)) { TimeSpan testValue; valid = TimeSpan.TryParse(row[metaDataColumn.ExcelColumn].ToString(), out testValue); } else if (metaDataColumn.StrongType == typeof(Guid)) { Guid testValue; valid = Guid.TryParse(row[metaDataColumn.ExcelColumn].ToString(), out testValue); } if (!valid) { rtrn.Add( new BusinessExcelValidationRecord(changeTable.TableName, column.ColumnName, excelRowIndex,metaDataColumn.StrongType,BusinessExcelValidationResultType.DataTypeValidation) { BadValue = row[metaDataColumn.ExcelColumn].ToString() }); } } } return rtrn; }
public static void BulkInsert(DataTable changeTable, BusinessDataTableMetaData metaData, string tableName, BusinessISGDatabaseType databaseType) { var columns = new List<BusinessDataTableColumn>(); // ReSharper disable once LoopCanBeConvertedToQuery foreach (DataColumn column in changeTable.Columns) { var selectedColumn = metaData.Columns.FirstOrDefault(x => string.Equals(x.ExcelColumn, column.ColumnName, StringComparison.CurrentCultureIgnoreCase)); var isNullable = (selectedColumn == null || selectedColumn.IsNullable); var typeName = (selectedColumn != null) ? selectedColumn.StrongType.ToString() : column.DataType.ToString(); var length = (selectedColumn == null || selectedColumn.Length == 0) ? Convert.ToInt16(-1) : selectedColumn.Length; var columnName = (selectedColumn != null) ? selectedColumn.Name : column.ColumnName; columns.Add( new BusinessDataTableColumn { Name = columnName, IsNullable = isNullable, TypeName = typeName, Length = length }); } var rows = new List<BusinessDataTableRow>(); foreach (DataRow row in changeTable.Rows) { var cells = new List<BusinessDataTableCell>(); // ReSharper disable once LoopCanBeConvertedToQuery foreach (var column in columns) { var selectedColumn = metaData.Columns.FirstOrDefault(x => string.Equals(x.Name, column.Name, StringComparison.CurrentCultureIgnoreCase)); var columnName = (selectedColumn != null && !string.IsNullOrEmpty(selectedColumn.ExcelColumn)) ? selectedColumn.ExcelColumn : column.Name; cells.Add( new BusinessDataTableCell { ColumnName = column.Name, Value = row[columnName].ToString() }); } rows.Add( new BusinessDataTableRow { Cells = cells }); } var businessDataTable = new BusinessDataTable { Name = tableName, Columns = columns, Rows = rows }; var database = Mapper.Map<BusinessISGDatabaseType, DALISGDatabaseType>(databaseType); var dalDataTable = Mapper.Map<BusinessDataTable, DALDataTable>(businessDataTable); DynamicEntity.BulkInsert(dalDataTable, database); }
public static void DropUnmatchedColumns(DataTable changeTable, BusinessDataTableMetaData metaData) { // ReSharper disable once LoopCanBePartlyConvertedToQuery foreach (var column in changeTable.Columns.Cast<DataColumn>().ToArray()) { var metaDataColumnExists = metaData.Columns.Any(x => string.Equals(x.ExcelColumn, column.ColumnName, StringComparison.CurrentCultureIgnoreCase) || string.Equals(x.Name, column.ColumnName, StringComparison.CurrentCultureIgnoreCase)); if (!metaDataColumnExists) { changeTable.Columns.Remove(column); } } }
public static List<BusinessExcelValidationRecord> ValidateMaxLength(DataTable changeTable, BusinessDataTableMetaData metaData) { var rtrn = new List<BusinessExcelValidationRecord>(); for (var i = 0; i < changeTable.Rows.Count; i++) { var row = changeTable.Rows[i]; var excelRowIndex = i + 2; // ReSharper disable once LoopCanBeConvertedToQuery foreach (DataColumn column in changeTable.Columns) { var metaDataColumn = metaData.Columns.FirstOrDefault(x => string.Equals(x.ExcelColumn, column.ColumnName, StringComparison.CurrentCultureIgnoreCase) && x.IsNullable == false && x.IsReadonlyIdentity == false && x.StrongType == typeof(string)); if ((metaDataColumn != null) && (!string.IsNullOrEmpty(row[metaDataColumn.ExcelColumn].ToString())) && (row[metaDataColumn.ExcelColumn].ToString().Length > metaDataColumn.Length)) { rtrn.Add( new BusinessExcelValidationRecord(changeTable.TableName, column.ColumnName, excelRowIndex,BusinessExcelValidationResultType.MaximumStringLengthViolation) { BadValue = row[metaDataColumn.ExcelColumn].ToString() }); } } } return rtrn; }
private static void AddDropColumns(DataTable universeTable, BusinessDataTableMetaData metaData, List<BusinessDataColumnBuilder> extraColumns) { ExcelProcessor.DropEmptyColumns(universeTable); ExcelProcessor.AddExtraColumns(universeTable, extraColumns); ExcelProcessor.DropUnmatchedColumns(universeTable, metaData); }
private static void ValidateAndProcessColumns(DataTable universeTable, BusinessDataTableMetaData metaData, BusinessUniverseMap universeMap, string uploadCode, string fileName) { var validationErrors = new List<BusinessExcelValidationRecord>(); validationErrors.AddRange(ExcelProcessor.ValidateRequiredColumns(universeTable, metaData)); validationErrors.AddRange(ExcelProcessor.ValidateDataTypes(universeTable, metaData)); validationErrors.AddRange(ExcelProcessor.ValidateMaxLength(universeTable, metaData)); validationErrors.AddRange(ExcelProcessor.ValidateRequiredFields(universeTable, metaData)); if (validationErrors.Count > 0) { ExcelProcessor.WriteExcelValidationErrors(validationErrors, uploadCode, fileName); } else { ExcelProcessor.BulkInsert(universeTable, metaData, universeMap.InputTable, universeMap.LoadISGDatabase); } }