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); } } }