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));
        }
Beispiel #2
0
        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);
        }