public virtual void AddForeignKey(ForeignKey key)
{
this.ForeignKeys.RemoveAll(x => x.ForeignKeyName == key.ForeignKeyName);
this.ForeignKeys.Add(key);
}
public List <ForeignKey> GetForeignKeysForDatabase()
{
//TODO:Add filtering by schema
List <ForeignKey> response = new List <ForeignKey>();
string query = @"SELECT
FK_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME,
FK.TABLE_SCHEMA as 'ForeignKeySchema',
PK.TABLE_SCHEMA as 'PrimaryKeySchema',
CU.ORDINAL_POSITION
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT
i1.TABLE_NAME,
i1.CONSTRAINT_SCHEMA,
i2.COLUMN_NAME,
i2.ORDINAL_POSITION
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE
i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT
ON PT.TABLE_NAME = PK.TABLE_NAME
AND PT.CONSTRAINT_SCHEMA = PK.CONSTRAINT_SCHEMA
AND PT.ORDINAL_POSITION = CU.ORDINAL_POSITION
AND PK.TABLE_SCHEMA <> 'SYS'
AND PK.TABLE_NAME <> '__EFMigrationsHistory'
ORDER BY ForeignKeySchema,FK_Table,ORDINAL_POSITION" ;
using (SqlConnection conn = new SqlConnection(this._connectionString))
{
conn.Open();
SqlCommand command = new SqlCommand(query, conn);
command.CommandTimeout = 300;
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
var column = new ForeignKeyColumn
{
ForeignKeyTableName = reader["FK_Table"].ToString(),
ForeignKeyColumnName = reader["FK_Column"].ToString(),
PrimaryKeyTableName = reader["PK_Table"].ToString(),
PrimaryKeyColumnName = reader["PK_Column"].ToString(),
ForeignKeySchemaName = reader["ForeignKeySchema"].ToString(),
PrimaryKeySchemaName = reader["PrimaryKeySchema"].ToString(),
Order = reader["ORDINAL_POSITION"].ToString().ToInt()
};
var key = new ForeignKey();
key.Columns.Add(column);
key.ForeignKeyName = reader["Constraint_Name"].ToString();
// we already have a key with this name. Add the column to its collection
if (response.Any(x => x.ForeignKeyName == key.ForeignKeyName))
{
response.First(x => x.ForeignKeyName == key.ForeignKeyName).Columns.Add(column);
}
else
{
response.Add(key);
}
}
}
return(response);
}