private void GetForeignKeys()
{
var command = _connection.CreateCommand();
command.CommandText = @"SELECT
f.name AS foreign_key_name,
schema_name(f.schema_id) AS [schema_name],
object_name(f.parent_object_id) AS table_name,
object_schema_name(f.referenced_object_id) AS principal_table_schema_name,
object_name(f.referenced_object_id) AS principal_table_name,
col_name(fc.parent_object_id, fc.parent_column_id) AS constraint_column_name,
col_name(fc.referenced_object_id, fc.referenced_column_id) AS referenced_column_name,
is_disabled,
delete_referential_action_desc,
update_referential_action_desc
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.object_id = fc.constraint_object_id
ORDER BY f.name, fc.constraint_column_id";
using (var reader = command.ExecuteReader())
{
var lastFkName = "";
ForeignKeyModel fkInfo = null;
while (reader.Read())
{
var fkName = reader.GetString(0);
var schemaName = reader.GetString(1);
var tableName = reader.GetString(2);
if (!_tableSelectionSet.Allows(schemaName, tableName))
{
continue;
}
if (fkInfo == null
|| lastFkName != fkName)
{
lastFkName = fkName;
var principalSchemaTableName = reader.GetString(3);
var principalTableName = reader.GetString(4);
var table = _tables[TableKey(tableName, schemaName)];
TableModel principalTable;
_tables.TryGetValue(TableKey(principalTableName, principalSchemaTableName), out principalTable);
fkInfo = new ForeignKeyModel
{
Table = table,
PrincipalTable = principalTable
};
table.ForeignKeys.Add(fkInfo);
}
var fromColumnName = reader.GetString(5);
var fromColumn = _tableColumns[ColumnKey(fkInfo.Table, fromColumnName)];
fkInfo.Columns.Add(fromColumn);
if (fkInfo.PrincipalTable != null)
{
var toColumnName = reader.GetString(6);
var toColumn = _tableColumns[ColumnKey(fkInfo.PrincipalTable, toColumnName)];
fkInfo.PrincipalColumns.Add(toColumn);
}
fkInfo.OnDelete = ConvertToReferentialAction(reader.GetString(8));
}
}
}
private void GetForeignKeys()
{
foreach (var dependentTable in _databaseModel.Tables)
{
var fkList = _connection.CreateCommand();
fkList.CommandText = $"PRAGMA foreign_key_list(\"{dependentTable.Name.Replace("\"", "\"\"")}\");";
var tableForeignKeys = new Dictionary<int, ForeignKeyModel>();
using (var reader = fkList.ExecuteReader())
{
while (reader.Read())
{
var id = reader.GetInt32((int)ForeignKeyList.Id);
var principalTableName = reader.GetString((int)ForeignKeyList.Table);
ForeignKeyModel foreignKey;
if (!tableForeignKeys.TryGetValue(id, out foreignKey))
{
TableModel principalTable;
_tables.TryGetValue(principalTableName, out principalTable);
foreignKey = new ForeignKeyModel
{
Table = dependentTable,
PrincipalTable = principalTable
};
tableForeignKeys.Add(id, foreignKey);
}
var fromColumnName = reader.GetString((int)ForeignKeyList.From);
foreignKey.Columns.Add(_tableColumns[ColumnKey(dependentTable, fromColumnName)]);
if (foreignKey.PrincipalTable != null)
{
var toColumnName = reader.GetString((int)ForeignKeyList.To);
ColumnModel toColumn;
if(!_tableColumns.TryGetValue(ColumnKey(foreignKey.PrincipalTable, toColumnName), out toColumn))
{
toColumn = new ColumnModel { Name = toColumnName };
}
foreignKey.PrincipalColumns.Add(toColumn);
}
foreignKey.OnDelete = ConvertToReferentialAction(
reader.GetString((int)ForeignKeyList.OnDelete));
}
}
foreach (var foreignKey in tableForeignKeys)
{
dependentTable.ForeignKeys.Add(foreignKey.Value);
}
}
}
