#region License // Copyright 2005-2019 Paul Kohler (https://github.com/paulkohler/minisqlquery). All rights reserved. // This source code is made available under the terms of the GNU Lesser General Public License v3.0 // https://github.com/paulkohler/minisqlquery/blob/master/LICENSE #endregion using System.Collections.Generic; using System.Data; using System.Data.Common; using System.Data.SqlClient; namespace MiniSqlQuery.Core.DbModel { /// The sql client schema service. public class SqlClientSchemaService : GenericSchemaService { /// The get db types. /// The connection. /// public override Dictionary GetDbTypes(DbConnection connection) { var types = base.GetDbTypes(connection); var date = types["datetime"]; date.LiteralPrefix = "'"; date.LiteralSuffix = "'"; return types; } /// The get foreign key references for table. /// The db conn. /// The db table. protected override void GetForeignKeyReferencesForTable(DbConnection dbConn, DbModelTable dbTable) { ForeignKeyInformationAvailable = true; try { using (var cmd = dbConn.CreateCommand()) { cmd.CommandText = string.Format( @"SELECT OBJECT_SCHEMA_NAME(f.parent_object_id) AS TableSchemaName, OBJECT_NAME(f.parent_object_id) AS TableName, COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName, f.name AS ForeignKeyName, OBJECT_SCHEMA_NAME(f.referenced_object_id) AS ReferenceTableSchemaName, OBJECT_NAME(f.referenced_object_id) AS ReferenceTableName, COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName, f.update_referential_action_desc, f.delete_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 WHERE OBJECT_SCHEMA_NAME(f.parent_object_id) = '{0}' AND OBJECT_NAME(f.parent_object_id) = '{1}' ", dbTable.Schema, dbTable.Name); cmd.CommandType = CommandType.Text; using (var dr = cmd.ExecuteReader()) { while (dr.Read()) { dbTable.Constraints.Add(new DbModelConstraint { ConstraintTableSchema = (string)dr["TableSchemaName"], ConstraintTableName = (string)dr["TableName"], ColumnName = (string)dr["ColumnName"], ConstraintName = (string)dr["ForeignKeyName"], UniqueConstraintTableSchema = (string)dr["ReferenceTableSchemaName"], UniqueConstraintTableName = (string)dr["ReferenceTableName"], UniqueColumnName = (string)dr["ReferenceColumnName"], UpdateRule = (string)dr["update_referential_action_desc"], DeleteRule = (string)dr["delete_referential_action_desc"], }); } } } } catch (SqlException) { ForeignKeyInformationAvailable = false; } } /// The process foreign key references for table. /// The db conn. /// The db table. protected override void ProcessForeignKeyReferencesForTable(DbConnection dbConn, DbModelTable dbTable) { // todo - check GetGroupForeingKeys foreach (DbModelConstraint constraint in dbTable.Constraints) { var column = dbTable.Columns.Find(c => c.Name == constraint.ColumnName); var refTable = dbTable.ParentDb.FindTable( Utility.RenderSafeSchemaObjectName(constraint.UniqueConstraintTableSchema, constraint.UniqueConstraintTableName)); var refColumn = refTable.Columns.Find(c => c.Name == constraint.UniqueColumnName); DbModelForeignKeyReference fk = new DbModelForeignKeyReference(column, refTable, refColumn); fk.UpdateRule = constraint.UpdateRule; fk.DeleteRule = constraint.DeleteRule; column.ForeignKeyReference = fk; } } } }