public static string FromDataTable(string tableName, DataTable schema, int[] primaryKeys = null) { var sqlTable = new SqlTableDefinition { TableName = tableName }; // columns foreach (DataRow column in schema.Rows) { var sqlCol = new SqlColumnDefinition(); if (!(schema.Columns.Contains("IsHidden") && (bool)column["IsHidden"])) { sqlCol.ColumnName = column["ColumnName"].ToString(); sqlCol.DataType = CreateTableSqlInternal.SqlGetType(column); if (schema.Columns.Contains("AllowDBNull") && ((bool)column["AllowDBNull"] == false)) { sqlCol.IsNullable = false; } } sqlTable.ColumnDefinitions.Add(sqlCol); } // primary keys var hasKeys = (primaryKeys != null) && (primaryKeys.Length > 0); if (hasKeys) { // user defined keys foreach (var key in primaryKeys) { sqlTable.PrimaryKeyColumnNames.Add(schema.Rows[key]["ColumnName"].ToString()); } } else { // check schema for keys sqlTable.PrimaryKeyColumnNames.AddRange(schema.GetPrimaryKeys()); hasKeys = sqlTable.PrimaryKeyColumnNames.Count > 0; } return(CreateTableSqlInternal.FromSqlTableDefinition(sqlTable)); }
public static SqlColumnDefinition GetBestFitSqlColumnType(HashSet <string> vals, string colName) { var sqlCol = new SqlColumnDefinition { IsNullable = vals.Any(v => string.IsNullOrEmpty(v) || v.Equals("NULL", StringComparison.CurrentCultureIgnoreCase)), ColumnName = colName }; vals.RemoveWhere(string.IsNullOrWhiteSpace); var bestFitType = GetBestFitType(vals, colName); var sqlType = "dtype"; if (NumericTypes.Contains(bestFitType)) { var isMoney = vals .All(v => { var vt = v.Trim(); return(vt.StartsWith("$") || vt.EndsWith("$") || vt.StartsWith("-$") || (vt.StartsWith("($") && vt.EndsWith(")"))); }); // supports ($1200) and -$1000 if (isMoney) { sqlType = "MONEY"; } else if (bestFitType == AtomicDataType.Int) { sqlType = "INT"; } else { var decimalValues = vals .Select(v => { var pieces = v.Split('.'); return(new { DecimalPlaces = pieces.Length > 1 ? pieces[1].Trim().Length : 0, IntegerSize = pieces[0]?.Trim().Length ?? 0 }); }); var maxIntPart = decimalValues.Max(d => d.IntegerSize); var maxDecimalPlaces = decimalValues.Max(d => d.DecimalPlaces); sqlType = $"DECIMAL({maxIntPart + maxDecimalPlaces}, {maxDecimalPlaces})"; } } else if (CharTypes.Contains(bestFitType)) { var maxLength = vals.Select(v => v.Length).Max(); sqlType = maxLength > 900 ? "VARCHAR(MAX)" : $"VARCHAR({maxLength})"; } else if (bestFitType == AtomicDataType.Guid) { sqlType = "UNIQUEIDENTIFIER"; } else if (bestFitType == AtomicDataType.Bool) { sqlType = "BIT"; } else if (bestFitType == AtomicDataType.DateTime) { sqlType = "DATETIME"; } else if (bestFitType == AtomicDataType.Date) { sqlType = "DATE"; } if (sqlType == "dtype") { sqlType += $" /* First 8 distinct values: '{string.Join(",", vals.Take(8))}' */ "; } sqlCol.DataType = sqlType; return(sqlCol); }