コード例 #1
0
 public virtual void AddForeignKey(ForeignKey key)
 {
     this.ForeignKeys.RemoveAll(x => x.ForeignKeyName == key.ForeignKeyName);
     this.ForeignKeys.Add(key);
 }
コード例 #2
0
        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);
        }