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);
}
