public override string GetColumnDataLength(TableColumn column) { string dataType = column.DataType; string dataLength = string.Empty; DataTypeInfo dataTypeInfo = DataTypeHelper.GetDataTypeInfo(this, dataType); bool isChar = DataTypeHelper.IsCharType(dataType); bool isBinary = DataTypeHelper.IsBinaryType(dataType); DataTypeSpecification dataTypeSpec = this.GetDataTypeSpecification(dataTypeInfo.DataType); if (dataTypeSpec != null) { if (!string.IsNullOrEmpty(dataTypeSpec.Args)) { if (string.IsNullOrEmpty(dataTypeInfo.Args)) { if (isChar || isBinary) { dataLength = column.MaxLength.ToString(); } else if (!this.IsNoLengthDataType(dataType)) { dataLength = this.GetDataTypePrecisionScale(column, dataTypeInfo.DataType); } } else { dataLength = dataTypeInfo.Args; } } } return(dataLength); }
public string GetDataTypePrecisionScale(TableColumn column, string dataType) { DataTypeSpecification dataTypeSpecification = this.GetDataTypeSpecification(dataType); if (dataTypeSpecification != null) { long precision = column.Precision.HasValue ? column.Precision.Value : 0; int scale = column.Scale.HasValue ? column.Scale.Value : 0; if (dataTypeSpecification.Args.Contains(",")) { if (precision > 0) { return($"{precision},{scale}"); } } else if (dataTypeSpecification.Args == "scale") { ArgumentRange?range = DataTypeManager.GetArgumentRange(dataTypeSpecification, "scale"); if (range.HasValue) { if (scale > range.Value.Max) { scale = range.Value.Max; } } return($"{scale}"); } } return(string.Empty); }
public static ArgumentRange?GetArgumentRange(DataTypeSpecification dataTypeSpecification, string argumentName) { ArgumentRange?range = default(ArgumentRange?); if (dataTypeSpecification.Arugments.Any(item => item.Name.ToLower() == argumentName.ToLower())) { return(dataTypeSpecification.Arugments.FirstOrDefault(item => item.Name.ToLower() == argumentName.ToLower()).Range); } return(range); }
private void SetColumnCellsReadonly(DataGridViewRow row) { DataGridViewCell lengthCell = row.Cells[this.colLength.Name]; DataGridViewCell primaryCell = row.Cells[this.colPrimary.Name]; DataGridViewCell identityCell = row.Cells[this.colIdentity.Name]; string dataType = DataGridViewHelper.GetCellStringValue(row, this.colDataType.Name); if (!string.IsNullOrEmpty(dataType)) { UserDefinedType userDefindedType = this.GetUserDefinedType(dataType); if (userDefindedType != null) { dataType = userDefindedType.Type; } DataTypeSpecification dataTypeSpec = this.dataTypeSpecifications.FirstOrDefault(item => item.Name == dataType); if (dataTypeSpec != null) { bool isLengthReadOnly = userDefindedType != null || string.IsNullOrEmpty(dataTypeSpec.Args); bool isPrimaryReadOnly = dataTypeSpec.IndexForbidden; bool isIdentityReadOnly = !dataTypeSpec.AllowIdentity; lengthCell.ReadOnly = isLengthReadOnly; primaryCell.ReadOnly = isPrimaryReadOnly; identityCell.ReadOnly = isIdentityReadOnly; if (isLengthReadOnly) { lengthCell.Value = null; } if (isPrimaryReadOnly) { primaryCell.Value = false; } if (isIdentityReadOnly) { identityCell.Value = false; } } } else { lengthCell.ReadOnly = true; primaryCell.ReadOnly = true; identityCell.ReadOnly = true; } }
public static DataTypeSpecification ParseArgument(DataTypeSpecification dataTypeSpecification) { if (string.IsNullOrEmpty(dataTypeSpecification.Args) || dataTypeSpecification.Arugments.Count > 0) { return(dataTypeSpecification); } if (!string.IsNullOrEmpty(dataTypeSpecification.Range)) { string[] argItems = dataTypeSpecification.Args.Split(ArugumentRangeItemDelimiter); string[] rangeItems = dataTypeSpecification.Range.Split(ArugumentRangeItemDelimiter); int i = 0; foreach (string argItem in argItems) { DataTypeArgument argument = new DataTypeArgument() { Name = argItem }; if (i < rangeItems.Length) { ArgumentRange range = new ArgumentRange(); string[] rangeValues = rangeItems[i].Split(ArugumentRangeValueDelimiter); range.Min = int.Parse(rangeValues[0]); if (rangeValues.Length > 1) { range.Max = int.Parse(rangeValues[1]); } else { range.Max = range.Min; } argument.Range = range; } dataTypeSpecification.Arugments.Add(argument); i++; } } return(dataTypeSpecification); }
private void ShowColumnSelector(DatabaseObjectType databaseObjectType, IEnumerable <IndexColumn> values, bool columnIsReadonly) { frmColumSelect columnSelect = new frmColumSelect() { ColumnIsReadOnly = columnIsReadonly }; IEnumerable <TableColumnDesingerInfo> columns = this.ucColumns.GetColumns().Where(item => !string.IsNullOrEmpty(item.Name)); List <IndexColumn> columnInfos = new List <IndexColumn>(); foreach (TableColumnDesingerInfo column in columns) { if (databaseObjectType == DatabaseObjectType.TableIndex) { if (!string.IsNullOrEmpty(column.DataType) && string.IsNullOrEmpty(column.ExtraPropertyInfo?.Expression)) { DataTypeSpecification dataTypeSpec = DataTypeManager.GetDataTypeSpecification(this.displayInfo.DatabaseType, column.DataType); if (dataTypeSpec != null && !dataTypeSpec.IndexForbidden) { columnInfos.Add(new IndexColumn() { ColumnName = column.Name }); } } } } columnSelect.InitControls(columnInfos, this.displayInfo.DatabaseType == DatabaseType.SqlServer); columnSelect.LoadColumns(values); if (columnSelect.ShowDialog() == DialogResult.OK) { if (databaseObjectType == DatabaseObjectType.TableIndex) { this.ucIndexes.SetRowColumns(columnSelect.SelectedColumns); } } }
public async Task <DataTable> GetPagedDataTableAsync(DbConnection connection, Table table, List <TableColumn> columns, string orderColumns, long total, int pageSize, long pageNumber, string whereClause = "") { string quotedTableName = this.GetQuotedObjectName(table); List <string> columnNames = new List <string>(); foreach (TableColumn column in columns) { string columnName = this.GetQuotedString(column.Name); #region Convert MySql float to decimal, avoid scientific notation if (this.DatabaseType == DatabaseType.MySql && column.DataType.ToLower().Contains("float")) { DataTypeInfo dataTypeInfo = DataTypeHelper.GetDataTypeInfo(column.DataType); if (!string.IsNullOrEmpty(dataTypeInfo.Args)) { string strPrecision = dataTypeInfo.Args.Split(',')[0].Trim(); int precision; DataTypeSpecification dataTypeSpec = this.GetDataTypeSpecification("decimal"); if (dataTypeSpec != null) { ArgumentRange?precisionRange = DataTypeManager.GetArgumentRange(dataTypeSpec, "precision"); if (precisionRange.HasValue) { if (int.TryParse(strPrecision, out precision) && precision > 0 && precision <= precisionRange.Value.Max) { columnName = $"CONVERT({columnName},DECIMAL({dataTypeInfo.Args})) AS {columnName}"; } } } } } #endregion #region Convert Oracle number to char else if (this.DatabaseType == DatabaseType.Oracle && column.DataType.ToLower().Contains("number")) { columnName = $"TO_CHAR({columnName}) AS {columnName}"; } #endregion columnNames.Add(columnName); } string strColumnNames = string.Join(",", columnNames); string pagedSql = this.GetSqlForPagination(quotedTableName, strColumnNames, orderColumns, whereClause, pageNumber, pageSize); DataTable dt = await this.GetDataTableAsync(connection, pagedSql); if (dt.Columns.OfType <DataColumn>().Any(item => item.ColumnName == RowNumberColumnName)) { dt.Columns.Remove(RowNumberColumnName); } return(dt); }
private static bool IsTableColumnDataTypeAndLengthEquals(DatabaseType databaseType, TableColumn column1, TableColumn column2) { if (column1.IsUserDefined != column2.IsUserDefined) { return(false); } else if (column1.IsUserDefined && column2.IsUserDefined) { return(column1.DataType == column2.DataType); } DataTypeInfo dataTypeInfo1 = DataTypeHelper.GetDataTypeInfo(column1.DataType); DataTypeInfo dataTypeInfo2 = DataTypeHelper.GetDataTypeInfo(column2.DataType); var dataTypeSpecs1 = DataTypeManager.GetDataTypeSpecifications(databaseType); var dataTypeSpecs2 = DataTypeManager.GetDataTypeSpecifications(databaseType); string dataType1 = dataTypeInfo1.DataType; string dataType2 = dataTypeInfo2.DataType; if (!dataTypeSpecs1.Any(item => item.Name == dataType1)) { dataTypeInfo1 = DataTypeHelper.GetSpecialDataTypeInfo(column1.DataType.ToLower()); dataType1 = dataTypeInfo1.DataType; } if (!dataTypeSpecs2.Any(item => item.Name == dataType2)) { dataTypeInfo2 = DataTypeHelper.GetSpecialDataTypeInfo(column2.DataType.ToLower()); dataType2 = dataTypeInfo2.DataType; } if (dataType1.ToLower() != dataType2.ToLower()) { return(false); } DataTypeSpecification dataTypeSpec1 = dataTypeSpecs1.FirstOrDefault(item => item.Name == dataType1); DataTypeSpecification dataTypeSpec2 = dataTypeSpecs2.FirstOrDefault(item => item.Name == dataType2); if (dataTypeInfo1.DataType == dataTypeInfo2.DataType && string.IsNullOrEmpty(dataTypeSpec1.Args) && string.IsNullOrEmpty(dataTypeSpec2.Args)) { return(true); } bool isChar1 = DataTypeHelper.IsCharType(dataType1); bool isChar2 = DataTypeHelper.IsCharType(dataType2); bool isBytes1 = DataTypeHelper.IsCharType(dataType1); bool isBytes2 = DataTypeHelper.IsCharType(dataType2); if (isBytes1 && isBytes2) { return(column1.MaxLength == column2.MaxLength); } else if (isChar1 && isChar2 && DataTypeHelper.StartWithN(dataType1) && DataTypeHelper.StartWithN(dataType2)) { return(column1.MaxLength == column2.MaxLength); } else if ((column1.Precision == null && column1.Scale == null && column1.MaxLength == column2.Precision) || (column2.Precision == null && column2.Scale == null && column2.MaxLength == column1.Precision)) { return(true); } if (dataTypeSpec1.Name == dataTypeSpec2.Name && dataTypeSpec1.Args?.Contains("length") == false) { if (dataTypeSpec1.Args == "scale") { return(IsPrecisionScaleEquals(column1.Scale, column2.Scale)); } else if (dataTypeSpec1.Args == "precision") { return(IsPrecisionScaleEquals(column1.Precision, column2.Precision)); } else if (dataTypeSpec1.Args?.Contains("scale") == true || dataTypeSpec1.Args?.Contains("precision") == true) { return(IsPrecisionScaleEquals(column1.Precision, column2.Precision) && IsPrecisionScaleEquals(column1.Scale, column2.Scale)); } } return(column1.MaxLength == column2.MaxLength && IsPrecisionScaleEquals(column1.Precision, column2.Precision) && IsPrecisionScaleEquals(column1.Scale, column2.Scale)); }
public static bool ValidateDataType(DatabaseType databaseType, TableColumnDesingerInfo columnDesingerInfo, out string message) { message = ""; if (columnDesingerInfo.IsUserDefined) { return(true); } string columName = columnDesingerInfo.Name; string dataType = columnDesingerInfo.DataType; DataTypeSpecification dataTypeSpec = DataTypeManager.GetDataTypeSpecification(databaseType, dataType); if (dataTypeSpec == null) { message = $"Invalid data type:{dataType}"; return(false); } if (!string.IsNullOrEmpty(dataTypeSpec.Args)) { string length = columnDesingerInfo?.Length?.Trim(); if (string.IsNullOrEmpty(length) && dataTypeSpec.Optional) { return(true); } if (dataTypeSpec.AllowMax && !string.IsNullOrEmpty(length) && length.ToLower() == "max") { return(true); } string args = dataTypeSpec.Args; string[] argsNames = args.Split(','); string[] lengthItems = length?.Split(','); if (argsNames.Length != lengthItems?.Length) { if (argsNames.Length == 2 && lengthItems?.Length == 1) { lengthItems = new string[] { lengthItems[0], "0" }; } else { message = $"Length is invalid for column \"{columName}\", it's format should be:{args}"; return(false); } } int i = 0; foreach (string argName in argsNames) { string lengthItem = lengthItems[i]; ArgumentRange?range = DataTypeManager.GetArgumentRange(dataTypeSpec, argName); if (range.HasValue) { int lenValue; if (!int.TryParse(lengthItem, out lenValue)) { message = $"\"{lengthItem}\" is't a valid integer value"; return(false); } if (lenValue < range.Value.Min || lenValue > range.Value.Max) { message = $"The \"{argName}\"'s range of column \"{columName}\" should be between {range.Value.Min} and {range.Value.Max}"; return(false); } } i++; } } return(true); }
public static void SetColumnLength(DatabaseType databaseType, TableColumn column, string length) { string dataType = column.DataType; DataTypeSpecification dataTypeSpec = DataTypeManager.GetDataTypeSpecification(databaseType, dataType); if (!string.IsNullOrEmpty(dataTypeSpec.MapTo)) { column.DataType = dataTypeSpec.MapTo; return; } string args = dataTypeSpec.Args; if (string.IsNullOrEmpty(args)) { return; } string[] argsNames = args.Split(','); string[] lengthItems = length?.Split(','); int i = 0; foreach (string argName in argsNames) { if (lengthItems == null || i > lengthItems.Length - 1) { continue; } string lengthItem = lengthItems[i]; if (argName == "length") { bool isChar = DataTypeHelper.IsCharType(dataType); if (isChar) { if (dataTypeSpec.AllowMax && lengthItem.ToLower() == "max") { column.MaxLength = -1; } else { column.MaxLength = long.Parse(lengthItem) * (DataTypeHelper.StartWithN(dataType) ? 2 : 1); } } else { column.MaxLength = long.Parse(lengthItem); } } else if (argName == "precision" || argName == "dayScale") { column.Precision = int.Parse(lengthItem); } else if (argName == "scale") { column.Scale = int.Parse(lengthItem); } i++; } }
public void ConvertDataType(TableColumn column) { string originalDataType = column.DataType; DataTypeInfo dataTypeInfo = DataTypeHelper.GetDataTypeInfo(this.sourceDbInterpreter, originalDataType); string sourceDataType = dataTypeInfo.DataType; column.DataType = sourceDataType; DataTypeSpecification sourceDataTypeSpec = this.GetDataTypeSpecification(this.sourceDataTypeSpecs, sourceDataType); if (!string.IsNullOrEmpty(dataTypeInfo.Args)) { if (sourceDataTypeSpec.Args == "scale") { column.Scale = int.Parse(dataTypeInfo.Args); } else if (sourceDataTypeSpec.Args == "length") { if (column.MaxLength == null) { column.MaxLength = int.Parse(dataTypeInfo.Args); } } } DataTypeMapping dataTypeMapping = this.dataTypeMappings.FirstOrDefault(item => item.Source.Type?.ToLower() == column.DataType?.ToLower() || (item.Source.IsExpression && Regex.IsMatch(column.DataType, item.Source.Type, RegexOptions.IgnoreCase)) ); if (dataTypeMapping != null) { DataTypeMappingSource sourceMapping = dataTypeMapping.Source; DataTypeMappingTarget targetMapping = dataTypeMapping.Target; string targetDataType = targetMapping.Type; DataTypeSpecification targetDataTypeSpec = this.GetDataTypeSpecification(this.targetDataTypeSpecs, targetDataType); column.DataType = targetDataType; bool isChar = DataTypeHelper.IsCharType(column.DataType); bool isBinary = DataTypeHelper.IsBinaryType(column.DataType); if (isChar || isBinary) { if (isChar) { if (!string.IsNullOrEmpty(targetMapping.Length)) { column.MaxLength = int.Parse(targetMapping.Length); if (DataTypeHelper.StartWithN(targetDataType) && !DataTypeHelper.StartWithN(sourceDataType)) { column.MaxLength *= 2; } } } if (dataTypeMapping.Specials != null && dataTypeMapping.Specials.Count > 0) { DataTypeMappingSpecial special = dataTypeMapping.Specials.FirstOrDefault(item => this.IsSpecialMaxLengthMatched(item, column)); if (special != null) { column.DataType = special.Type; if (!string.IsNullOrEmpty(special.TargetMaxLength)) { column.MaxLength = int.Parse(special.TargetMaxLength); } } } if (column.MaxLength == -1) { ArgumentRange?sourceLengthRange = DataTypeManager.GetArgumentRange(sourceDataTypeSpec, "length"); if (sourceLengthRange.HasValue) { column.MaxLength = sourceLengthRange.Value.Max; } } ArgumentRange?targetLengthRange = DataTypeManager.GetArgumentRange(targetDataTypeSpec, "length"); if (targetLengthRange.HasValue) { int targetMaxLength = targetLengthRange.Value.Max; if (column.MaxLength > targetMaxLength) { if (!string.IsNullOrEmpty(targetMapping.Substitute)) { string[] substitutes = targetMapping.Substitute.Split(','); foreach (string substitute in substitutes) { DataTypeSpecification dataTypeSpec = this.GetDataTypeSpecification(this.targetDataTypeSpecs, substitute.Trim()); if (dataTypeSpec != null) { if (string.IsNullOrEmpty(dataTypeSpec.Args)) { column.DataType = substitute; break; } else { ArgumentRange?range = DataTypeManager.GetArgumentRange(dataTypeSpec, "length"); if (range.HasValue && range.Value.Max >= column.MaxLength) { column.DataType = substitute; break; } } } } } } } } else { if (dataTypeMapping.Specials != null && dataTypeMapping.Specials.Count > 0) { foreach (DataTypeMappingSpecial special in dataTypeMapping.Specials) { string name = special.Name; bool matched = false; if (name == "maxLength") { matched = this.IsSpecialMaxLengthMatched(special, column); } else if (name.Contains("precision") || name.Contains("scale")) { matched = this.IsSpecialPrecisionOrScaleMatched(special, column); } else if (name == "expression") { matched = this.IsSpecialExpressionMatched(special, originalDataType); } if (matched) { column.DataType = special.Type; } if (!string.IsNullOrEmpty(special.TargetMaxLength)) { column.MaxLength = int.Parse(special.TargetMaxLength); } } } if (string.IsNullOrEmpty(targetDataTypeSpec.Format)) { bool useConfigPrecisionScale = false; if (!string.IsNullOrEmpty(targetMapping.Precision)) { column.Precision = int.Parse(targetMapping.Precision); useConfigPrecisionScale = true; } if (!string.IsNullOrEmpty(targetMapping.Scale)) { column.Scale = int.Parse(targetMapping.Scale); useConfigPrecisionScale = true; } if (!useConfigPrecisionScale) { if (sourceDataTypeSpec.Args == "scale") { column.Precision = default(int?); } else if (sourceDataTypeSpec.Args == "precision,scale" && sourceDataTypeSpec.Args == targetDataTypeSpec.Args) { ArgumentRange?precisionRange = DataTypeManager.GetArgumentRange(targetDataTypeSpec, "precision"); ArgumentRange?scaleRange = DataTypeManager.GetArgumentRange(targetDataTypeSpec, "scale"); if (precisionRange.HasValue && column.Precision > precisionRange.Value.Max) { column.Precision = precisionRange.Value.Max; } if (scaleRange.HasValue && column.Scale > scaleRange.Value.Max) { column.Scale = scaleRange.Value.Max; } if (column.Precision.HasValue) { if (column.DataType.ToLower() == "int") { if (column.Precision.Value > 10) { column.DataType = "bigint"; } } } } } } else { string format = targetDataTypeSpec.Format; string dataType = format; string[] defaultValues = targetDataTypeSpec.Default?.Split(','); string targetMappingArgs = targetMapping.Args; int i = 0; foreach (DataTypeArgument arg in targetDataTypeSpec.Arugments) { if (arg.Name.ToLower() == "scale") { ArgumentRange?targetScaleRange = DataTypeManager.GetArgumentRange(targetDataTypeSpec, "scale"); int scale = column.Scale == null ? 0 : column.Scale.Value; if (targetScaleRange.HasValue && scale > targetScaleRange.Value.Max) { scale = targetScaleRange.Value.Max; } dataType = dataType.Replace("$scale$", scale.ToString()); } else { string defaultValue = defaultValues != null && defaultValues.Length > i ? defaultValues[i] : ""; string value = defaultValue; if (targetMapping.Arguments.Any(item => item.Name == arg.Name)) { value = targetMapping.Arguments.FirstOrDefault(item => item.Name == arg.Name).Value; } dataType = dataType.Replace($"${arg.Name}$", value); } i++; } column.DataType = dataType; } } } }