public static (TableDto[], ForeignKeyDto[]) ReadTablesAndForeignKeysFromDb(string connectionString)
{
List <ColumnDto> columns = new List <ColumnDto>();
List <ForeignKeyDto> foreignKeys = new List <ForeignKeyDto>();
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(ColumnsQuery, connection))
{
connection.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string tableName = (string)reader["table_name"];
string name = (string)reader["column_name"];
int ordinalPosition = int.Parse(reader["ordinal_position"].ToString(), CultureInfo.InvariantCulture);
string sqlType = (string)reader["DATA_TYPE"];
ColumnDto columnDto = new ColumnDto()
{
Name = name,
OrdinalPosition = ordinalPosition,
SqlType = sqlType,
TableName = tableName
};
columns.Add(columnDto);
}
}
}
using (SqlCommand cmd = new SqlCommand(ForeignKeysQuery, connection))
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string sourceTableName = (string)reader["source_table"];
string sourceColumnName = (string)reader["source_column"];
string destinationTableName = (string)reader["destination_table"];
string destinationColumnName = (string)reader["destination_column"];
ForeignKeyDto fk = new ForeignKeyDto(sourceTableName, sourceColumnName, destinationTableName, destinationColumnName);
foreignKeys.Add(fk);
}
}
}
}
Dictionary <string, TableDto> name2table = new Dictionary <string, TableDto>();
List <TableDto> tables = new List <TableDto>();
foreach (ColumnDto column in columns)
{
if (!name2table.ContainsKey(column.TableName))
{
name2table.Add(column.TableName, new TableDto(column.TableName));
}
TableDto table = name2table[column.TableName];
table.Add(column);
tables.Add(table);
}
return(tables.ToArray(), foreignKeys.ToArray());
}
internal void Add(ColumnDto column)
{
this.columns.Add(column.OrdinalPosition, column);
}
