Ejemplo n.º 1
0
        /// <summary>
        /// Gets the primary key for a table.
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public virtual PrimaryKeyColumnSchema GetPrimaryKey(TableSchema table)
        {
            PrimaryKeyColumnSchema result = null;
            if (table == null)
                return result;

            string cmdtext = @"
            select
            columnproperty(object_id(c.table_name), c.column_name, 'IsIdentity') as is_identity,
            c.*
            from information_schema.table_constraints tc
            inner join information_schema.key_column_usage kcu
            on kcu.constraint_name = tc.constraint_name
            and kcu.table_name = tc.table_name
            inner join information_schema.columns c
            on c.table_name = kcu.table_name
            and c.column_name = kcu.column_name
            where tc.constraint_type = 'PRIMARY KEY'
            and c.table_name = @table_name
            ";
            using (SqlCommand cmd = new SqlCommand(cmdtext))
            {
                cmd.Parameters.Add("@table_name", SqlDbType.NVarChar, 128).Value = table.Name;
                using (IDataReader reader = this._helper.ExecuteReader(cmd))
                {
                    if (reader.Read())
                    {
                        SqlDbType sqlType = Enum<SqlDbType>.Parse(reader["DATA_TYPE"].ToString());
                        Type dataType = getDataType(reader["DATA_TYPE"].ToString());
                        string name = reader["COLUMN_NAME"].ToString();
                        Dictionary<string, object> props = new Dictionary<string, object>();
                        props.Add("is_identity", Convert.ToBoolean(reader["is_identity"]));
                        int length = reader["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value ? System.Convert.ToInt32(reader["CHARACTER_MAXIMUM_LENGTH"]) : -1;
                        props.Add("is_primary_key", true);
                        props.Add("is_foreign_key", false);
                        props.Add("is_nullable", (reader["is_nullable"].ToString().Equals("YES")));
                        result = new PrimaryKeyColumnSchema(this, table, sqlType, dataType, name, length, props);
                    }
                }

            }
            return result;
        }
Ejemplo n.º 2
0
 /// <summary>
 /// Gets all of the objects representing tables that have references to a primary key.
 /// </summary>
 /// <param name="primaryKey"></param>
 /// <returns></returns>
 public virtual ForeignKeyColumnSchemaList GetForeignKeyReferences(PrimaryKeyColumnSchema primaryKey)
 {
     ForeignKeyColumnSchemaList result = new ForeignKeyColumnSchemaList();
     string cmdtext = @"
     select distinct
     col_name(fc.parent_object_id, fc.parent_column_id) as foreign_key_column,
     object_name(fc.parent_object_id) as foreign_key_table,
     col_name(fc.referenced_object_id, fc.referenced_column_id) as primary_key_column,
     object_name(fc.referenced_object_id) as primary_key_table,
     c.data_type,
     c.character_maximum_length,
     c.is_nullable,
     c.table_name,
     c.column_name
     from sys.foreign_keys as f
     inner join sys.foreign_key_columns as fc
     on f.object_id = fc.constraint_object_id
     inner join information_schema.columns c
     on c.column_name = col_name(fc.parent_object_id, fc.parent_column_id)
     and c.table_name = object_name(f.parent_object_id)
     where object_name (f.referenced_object_id) =  @table_name
     ";
     using (SqlCommand cmd = new SqlCommand(cmdtext))
     {
         cmd.Parameters.Add("@table_name", SqlDbType.NVarChar, 128).Value = primaryKey.Table.Name;
         using (IDataReader reader = this._helper.ExecuteReader(cmd))
         {
             while (reader.Read())
             {
                 string tableName = reader["TABLE_NAME"].ToString();
                 SqlDbType sqlType = Enum<SqlDbType>.Parse(reader["DATA_TYPE"].ToString());
                 Type dataType = getDataType(reader["DATA_TYPE"].ToString());
                 string name = reader["COLUMN_NAME"].ToString();
                 Dictionary<string, object> props = new Dictionary<string, object>();
                 props.Add("is_identity", false);
                 int length = reader["CHARACTER_MAXIMUM_LENGTH"] != DBNull.Value ? System.Convert.ToInt32(reader["CHARACTER_MAXIMUM_LENGTH"]) : -1;
                 props.Add("is_primary_key", false);
                 props.Add("is_foreign_key", true);
                 props.Add("primary_key_table", reader["primary_key_table"]);
                 props.Add("primary_key_column", reader["primary_key_column"]);
                 props.Add("foreign_key_column", reader["foreign_key_column"]);
                 props.Add("foreign_key_table", reader["foreign_key_table"]);
                 props.Add("is_nullable", (reader["is_nullable"].ToString().Equals("YES")));
                 result.Add(new ForeignKeyColumnSchema(this, new TableSchema(this, tableName), sqlType, dataType, name, length, props));
             }
         }
     }
     return result;
 }