public static SqlAllInfo SqlAllInfoFactory(string connection)
{
var allTablesAndCol = SqlTableAndColumnData.GetSqlTablesAndColumns(connection);
var allForeignKeys = SqlForeignKey.GetForeignKeys(connection);
var tableInfos = from tableGroup in allTablesAndCol.GroupBy(x => x.TableName)
let schemaName = tableGroup.First().SchemaName
let primaryKey = SqlPrimaryKey.GetPrimaryKeysNames(connection, tableGroup.Key)
select(new SqlTableInfo(schemaName,
tableGroup.Key, tableGroup.Select(y => new SqlColumnInfo(y.ColumnName, y.SqlTypeName,
primaryKey.SingleOrDefault(z => z.ColumnName == y.ColumnName),
y.IsNullable, y.MaxLength)).ToList()));
var allIndexes = SqlIndex.GetAllIndexes(connection);
return(new SqlAllInfo(tableInfos.ToList(), allForeignKeys, allIndexes));
}
public static IList <SqlTableAndColumnData> GetSqlTablesAndColumns(string connectionString)
{
var result = new Collection <SqlTableAndColumnData>();
using (var sqlcon = new SqlConnection(connectionString))
{
var command = sqlcon.CreateCommand();
command.CommandText = @"SELECT t.name AS TableName,
SCHEMA_NAME(t.schema_id) AS SchemaName,
c.name AS ColumnName,
types.name AS SqlTypeName,
c.is_nullable AS IsNullable,
c.max_length AS MaxLength,
c.is_computed AS IsComputed
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
INNER JOIN sys.types types ON c.system_type_id = types.system_type_id
AND types.name <> 'sysname'
ORDER BY SchemaName, TableName";
sqlcon.Open();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var row = new SqlTableAndColumnData();
var i = 0;
//for (int j = 0; j < reader.FieldCount; j++)
//{
// object col = reader[j];
// Console.WriteLine("{0}: {1}, type = {2}", j, col, col.GetType());
//}
row.TableName = reader.GetString(i++);
row.SchemaName = reader.GetString(i++);
row.ColumnName = reader.GetString(i++);
row.SqlTypeName = reader.GetString(i++);
row.IsNullable = reader.GetBoolean(i++); // reader[i++] as bool? ?? false;
row.MaxLength = reader.GetInt16(i++);
row.IsComputed = reader.GetBoolean(i++);
result.Add(row);
}
}
}
return(result);
}