/// <summary>
/// Get columns for the specified table
/// </summary>
/// <param name="conn">Connection to database</param>
/// <param name="table">Table to retrieve columns from</param>
/// <returns>List of columns for table</returns>
private List <Column> GetColumns(IDbConnection conn, Table table)
{
List <Column> columns = new List <Column>();
// Retrieve columns associated with table
try
{
using (var cmd = conn.CreateCommand())
{
// Join statement from http://stackoverflow.com/a/30583879
cmd.CommandText =
$"SELECT c.name, c.column_id, c.max_length, c.[precision], c.scale, c.is_nullable, t.name AS datatype " +
$"FROM sys.columns c JOIN sys.types t ON c.system_type_id = t.user_type_id or(c.system_type_id = 240 and c.user_type_id = t.user_type_id) " +
$"WHERE object_id = object_id('{table.Schema}.{table.Name}');";
// Execute query and construct columns
using (var reader = cmd.ExecuteReader())
using (DataTable data = new DataTable())
{
data.Load(reader);
// Iterate through all columns
foreach (DataRow row in data.Rows)
{
Column column = new Column(row.Field <int>("column_id"), row.Field <string>("name"));
column.Table = table;
// Add name candidate to column
column.AddNameCandidate(row.Field <string>("name"), 1f);
// Try to map DB specific datatype into a common datatype
try
{
DataType dt = DataType.FromSQLDataType(row.Field <string>("datatype"), row.Field <short>("max_length"), row.Field <byte>("precision"), row.Field <byte>("scale"));
column.AddDatatypeCandidate(dt, 0.95f); // Because of the mapping, then we allow for some uncertainty
}
catch (DataType.DataTypeConversionException e)
{
Logger.Log(Logger.Level.Warning, $"{table.Name}.{column.Name}: {e.Message}");
// If no conversion for the datatype is found, then assume that the data can fit in a WChar datatype
// Because of this assumption, then we give it a low propability of being correct
column.AddDatatypeCandidate(new DataType(OleDbType.WChar), 0.1f);
}
// Add nullable constraint to table
if ((bool)row["is_nullable"])
{
table.AddNotNullableCandidate(new NotNullable(column, 1f));
}
columns.Add(column);
}
}
}
}
catch (Exception e)
{
Logger.Log(e);
throw;
}
return(columns);
}
