Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

ForeignKey.FKTableSchema Property

IIS 7.0

Gets or sets the schema table name for a foreign key.

Namespace:  Microsoft.Web.Management.DatabaseManager
Assembly:  Microsoft.Web.Management.DatabaseManager (in Microsoft.Web.Management.DatabaseManager.dll)

public string FKTableSchema { get; set; }

Property Value

Type: System.String
The schema table name for a foreign key.

The following code sample shows how to use the GetTableInfo method to retrieve the table information for the table name from a database.

NoteNote:

This example also provides helper methods that perform additional database tasks.



        // Retrieve the information about a table.
        public TableInfo GetTableInfo(string connectionString, string tableName, string schema)
        {
            // Create a new TableInfo object.
            TableInfo info = new TableInfo();
            // Create a connection to the database.
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                connection.Open();
                // Retrieve the table name.
                info.Name = tableName;
                // Retrieve the list of table columns.
                GetColumns(connection, tableName, schema, info.Columns);
                // Retrieve the list of the table's foreign keys.
                GetForeignKeys(connection, tableName, info.ForeignKeys);
                // Retrieve the list of the table's indices.
                GetIndexes(connection, tableName, info.Indexes);
            }
            // Return the table information.
            return info;
        }

        // Retrieve the list of table colums.
        private void GetColumns(OleDbConnection connection, string tableName, string tableSchema, ICollection<Column> columns)
        {
            String[] restrictions = new string[] { null, null, tableName, null };
            DataTable columnSchema;

            // Retrieve a list of primary keys for a table.
            string[] primaryKeys = GetPrimaryKey(
                connection.ConnectionString, tableName, tableSchema);
            // Open the schema information for the columns.
            columnSchema = connection.GetSchema(
                OleDbMetaDataCollectionNames.Columns, restrictions);
            // Enumerate the table's columns.
            foreach (DataRow row in columnSchema.Rows)
            {
                // Create a new column object.
                Column column = new Column();
                // Retrieve the column name.
                column.Name = row["COLUMN_NAME"].ToString();
                column.OriginalName = row["COLUMN_NAME"].ToString();
                // Test if the column allows nulls.
                column.AllowNulls = (bool)row["IS_NULLABLE"];
                // Retrieve the column type.
                column.TypeInfo = GetColumnType(connection,
                    OleDbTypeToString((OleDbType)
                    Int32.Parse(row["DATA_TYPE"].ToString())));
                // Retrieve the column's default value.
                column.DefaultValue = ((row["COLUMN_DEFAULT"] as DBNull)
                    != null) ? "Null" : row["COLUMN_DEFAULT"].ToString();
                // Retrieve the column's precision.
                column.Precision = ((row["NUMERIC_PRECISION"] as DBNull)
                    != null) ? 0 : Int32.Parse(row["NUMERIC_PRECISION"].ToString());
                // Retrieve the column's scale.
                column.Scale = ((row["NUMERIC_SCALE"] as DBNull) != null) ? 0 : Int32.Parse(row["NUMERIC_SCALE"].ToString());
                // Specify if the column is a primary key.
                column.InPrimaryKey = primaryKeys.Contains(row["COLUMN_NAME"].ToString());
                // Specify that the column is not an identity column.
                column.IsIdentity = false;
                // Retrieve the column length.
                column.Length = ((OleDbType)Int32.Parse(row["DATA_TYPE"].ToString()) != OleDbType.WChar) ? -1 : Int32.Parse(row["CHARACTER_MAXIMUM_LENGTH"].ToString());
                // Append the column to the list.
                columns.Add(column);
            }
        }

        // Determine the type of a column.
        private TypeInfo GetColumnType(OleDbConnection connection, string columnName)
        {
            // Retrieve the list of column types.
            ICollection<TypeInfo> serverTypes = GetServerTypes(connection.ConnectionString);
            // Enumerate the columns.
            foreach (TypeInfo type in serverTypes)
            {
                // Return the column type if it is found.
                if (type.Name.Equals(columnName))
                {
                    return type;
                }
            }
            // Raise an exception if the column type was not found.
            throw new ArgumentException("Unknown column type: " + columnName);
        }

        // Retrieve the list of a table's indices.
        private void GetIndexes(OleDbConnection connection, string tableName, IList<Index> indices)
        {
            String[] restrictions = new string[] { null, null, null, null, tableName };
            DataTable schema;
            // Open the schema information for the indices.
            schema = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Indexes, restrictions);
            // Enumerate the table's indices.
            foreach (DataRow row in schema.Rows)
            {
                // Create a new index.
                Index dbIndex = new Index();
                // Append the index name.
                dbIndex.Name = row["INDEX_NAME"].ToString();
                dbIndex.OriginalName = row["INDEX_NAME"].ToString();
                // Append the index's uniqueness.
                dbIndex.Unique = (bool)row["UNIQUE"];
                // Specify the index type. 
                dbIndex.IndexType = (bool)row["PRIMARY_KEY"] == true ? IndexType.PrimaryKey : IndexType.Index;
                // Create an index column object.
                IndexColumn column = new IndexColumn();
                column.Name = row["COLUMN_NAME"].ToString();
                // Specify whether the index is descending.
                column.Descending = (Int32.Parse(row["COLLATION"].ToString()) == 2) ? true : false;
                dbIndex.Columns.Add(column);
                // Append the index to the list.
                indices.Add(dbIndex);
            }
        }

        // Retrieve the list of a table's foreign keys.
        private void GetForeignKeys(OleDbConnection connection, string tableName, IList<ForeignKey> foreignKeys)
        {
            String[] restrictions = new string[] { null };
            DataTable schema;
            // Open the schema information for the foreign keys.
            schema = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys, restrictions);
            // Enumerate the table's foreign keys.
            foreach (DataRow row in schema.Rows)
            {
                ForeignKey dbForeignKey = new ForeignKey();
                dbForeignKey.Name = row["FK_NAME"].ToString();
                dbForeignKey.OriginalName = row["FK_NAME"].ToString();

                dbForeignKey.FKTableName = row["FK_TABLE_NAME"].ToString();
                ForeignKeyColumn fkc = new ForeignKeyColumn();
                fkc.Name = row["FK_COLUMN_NAME"].ToString();
                dbForeignKey.FKColumns.Add(fkc);
                dbForeignKey.FKTableSchema = schema.ToString();

                dbForeignKey.PKTableName = row["PK_TABLE_NAME"].ToString();
                ForeignKeyColumn pkc = new ForeignKeyColumn();
                pkc.Name = row["PK_COLUMN_NAME"].ToString();
                dbForeignKey.PKColumns.Add(pkc);
                dbForeignKey.PKTableSchema = schema.ToString();

                foreignKeys.Add(dbForeignKey);
            }
        }



...



        // Map OLEDB data types to their respective string descriptions.
        internal static string OleDbTypeToString(OleDbType type)
        {
            switch (type)
            {
                case OleDbType.Binary:
                    return "Binary";
                case OleDbType.Boolean:
                    return "Bit";
                case OleDbType.UnsignedTinyInt:
                    return "Byte";
                case OleDbType.TinyInt:
                    return "TinyInt";
                case OleDbType.Integer:
                    return "Integer";
                case OleDbType.Currency:
                    return "Currency";
                case OleDbType.Date:
                    return "DateTime";
                case OleDbType.Double:
                    return "Float";
                case OleDbType.Guid:
                    return "UniqueIdentifier";
                case OleDbType.Char:
                case OleDbType.WChar:
                    return "Text";
                case OleDbType.Single:
                    return "Real";
                case OleDbType.SmallInt:
                    return "SmallInt";
                case OleDbType.Numeric:
                case OleDbType.Decimal:
                    return "Decimal";
                default:
                    return "Unknown";
            }
        }



Show:
© 2015 Microsoft