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