private List <ForeignKey> GetForeignKeys(string table, ISqlClientConnection connection)
{
_outputWriter.Verbose.WriteLine(
string.Format(StringResources.ReadingForeignKeys, table));
var foreignKeys = new List <ForeignKey>();
var command = connection.CreateCommand();
command.CommandText = string.Format("exec sp_fkeys '{0}'", table);
var reader = command.ExecuteReader();
while (reader.Read())
{
foreignKeys.Add(new ForeignKey()
{
PrimaryKeyEntity = reader["PKTABLE_NAME"].ToString(),
PrimaryKeyColumn = reader["PKCOLUMN_NAME"].ToString(),
ForeignKeyEntity = reader["FKTABLE_NAME"].ToString(),
ForeignKeyColumn = reader["FKCOLUMN_NAME"].ToString()
});
}
reader.Close();
foreignKeys.ForEach(fk =>
_outputWriter.Verbose.WriteLine(
string.Format($" [{fk.ForeignKeyEntity}].[{fk.ForeignKeyColumn}] -> [{fk.PrimaryKeyEntity}].[{fk.PrimaryKeyColumn}]")));
return(foreignKeys);
}
private List <PrimaryKey> GetPrimaryKeys(string table, ISqlClientConnection connection)
{
_outputWriter.Verbose.WriteLine(
string.Format(StringResources.ReadingPrimaryKeys, table));
var restrictions = new string[4];
restrictions[2] = table;
var indexTable = connection.GetSchema("IndexColumns", restrictions);
var primaryKeys = indexTable.AsEnumerable()
.Where(info => info["constraint_name"].ToString().StartsWith("PK_"))
.Select(info => new PrimaryKey
{
Entity = info["table_name"].ToString(),
Name = info["column_name"].ToString()
//TableSchema = info["table_schema"],
//TableName = info["table_name"],
//ColumnName = info["column_name"],
//ConstraintSchema = info["constraint_schema"],
//ConstraintName = info["constraint_name"],
//KeyType = info["KeyType"]
})
.ToList();
primaryKeys.ForEach(pk =>
_outputWriter.Verbose.WriteLine(
string.Format($" [{pk.Name}]")));
return(primaryKeys);
}
