internal SqlColumnInfo(string columnName, string sqlTypeName, SqlPrimaryKey sqlKeyInfo, bool isNullable, int maxLength)
{
ColumnName = columnName;
SqlTypeName = sqlTypeName;
if (sqlKeyInfo != null)
{
IsPrimaryKey = true;
PrimaryKeyOrder = sqlKeyInfo.KeyOrder;
}
IsNullable = isNullable;
MaxLength = maxLength;
}
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 <SqlPrimaryKey> GetPrimaryKeysNames(string connectionString, string tableName)
{
var result = new Collection <SqlPrimaryKey>();
using (var sqlcon = new SqlConnection(connectionString))
{
var command = sqlcon.CreateCommand();
//see https://msdn.microsoft.com/en-us/library/ms190196.aspx
command.CommandText = @"sp_pkeys @TableName";
var param = new SqlParameter();
param.ParameterName = "@TableName";
param.Value = tableName;
command.Parameters.Add(param);
sqlcon.Open();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var row = new SqlPrimaryKey();
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.DatabaseName = reader.GetString(i++);
row.SchemaName = reader.GetString(i++);
row.TableName = reader.GetString(i++);
row.ColumnName = reader.GetString(i++);
row.KeyOrder = reader.GetInt16(i++);
row.PrimaryKeyConstraintName = reader.GetString(i++);
result.Add(row);
}
}
}
return(result);
}