Ejemplo n.º 1
0
        /// <summary>
        /// Loads the tables from the database.
        /// </summary>
        /// <param name="dataProvider">The data provider.</param>
        /// <returns>
        /// The loaded collections.
        /// </returns>
        /// <exception cref="System.ArgumentNullException">dataProvider;dataProvider cannot be null</exception>
        public static Collection<Table> LoadFromDatabase(IObjectDataProvider dataProvider)
        {
            Collection<Table> result = new Collection<Table>();

            if (dataProvider == null)
            {
                throw new ArgumentNullException("dataProvider", "dataProvider cannot be null");
            }

            using (IDataReader reader = dataProvider.LoadTableData())
            {
                while (reader.Read())
                {
                    // Read the result data
                    string tableCatalog = reader.GetString(0);
                    string tableSchema = reader.GetString(1);
                    string tableName = reader.GetString(2);

                    // Build the new table
                    Table table = new Table(tableCatalog, tableSchema, tableName);

                    result.Add(table);
                }
            }

            // Populate additional schema objects
            foreach (Table table in result)
            {
                Column.PopulateColumns(table, dataProvider);
                Constraint.PopulateUniqueConstraints(table, dataProvider);
            }

            Constraint.PopulateReferentialConstraints(result, dataProvider);

            return result;
        }
        /// <summary>
        /// Load the data describing all unique constraints for a given table.
        /// </summary>
        /// <param name="table">The table.</param>
        /// <returns>
        /// The constraint data.
        /// </returns>
        /// <exception cref="System.ArgumentNullException">table;table cannot be null</exception>
        public IDataReader LoadUniqueConstraintDataForTable(Table table)
        {
            if (table == null)
            {
                throw new ArgumentNullException("table", "table cannot be null");
            }

            using (SqlCommand command = new SqlCommand(SqlObjectDataProvider.UniqueConstraintsQuery, this.sqlConnection))
            {
                command.Parameters.AddWithValue("TableCatalog", table.Catalog);
                command.Parameters.AddWithValue("TableSchema", table.Schema);
                command.Parameters.AddWithValue("TableName", table.Name);

                return command.ExecuteReader();
            }
        }
Ejemplo n.º 3
0
        /// <summary>
        /// Loads the columns from the database for a specific table.
        /// </summary>
        /// <param name="table">The table.</param>
        /// <param name="dataProvider">The data provider.</param>
        /// <exception cref="System.ArgumentNullException">
        /// table;table cannot be null
        /// or
        /// dataProvider;dataProvider cannot be null
        /// </exception>
        public static void PopulateColumns(Table table, IObjectDataProvider dataProvider)
        {
            if (table == null)
            {
                throw new ArgumentNullException("table", "table cannot be null");
            }

            if (dataProvider == null)
            {
                throw new ArgumentNullException("dataProvider", "dataProvider cannot be null");
            }

            using (IDataReader result = dataProvider.LoadColumnDataForTable(table))
            {
                while (result.Read())
                {
                    // Read the result data
                    string name = (string)result["COLUMN_NAME"];
                    int ordinalPosition = (int)result["ORDINAL_POSITION"];
                    string columnDefault = result.GetNullableString("COLUMN_DEFAULT");
                    bool isNullable = ((string)result["IS_NULLABLE"]).Equals("NO") ? false : true;

                    // Read the result data for the routine's return type
                    string dataType = (string)result["DATA_TYPE"];
                    int? characterMaximumLength = result.GetNullable<int>("CHARACTER_MAXIMUM_LENGTH");
                    int? numericPrecision = result.GetNullable<int>("NUMERIC_PRECISION");
                    int? numericPrecisionRadix = result.GetNullable<int>("NUMERIC_PRECISION_RADIX");
                    int? numericScale = result.GetNullable<int>("NUMERIC_SCALE");
                    int? dateTimePrecision = result.GetNullable<int>("DATETIME_PRECISION");
                    string characterSetName = result.GetNullableString("CHARACTER_SET_NAME");
                    string collationName = result.GetNullableString("COLLATION_NAME");

                    // Build the proper data structure for return type
                    SqlType type = new SqlType(dataType, characterMaximumLength, characterSetName, collationName, numericPrecision, numericPrecisionRadix, numericScale, dateTimePrecision);

                    // Build the new column
                    table.Columns.Add(new Column(name, type, ordinalPosition, columnDefault, isNullable));
                }
            }
        }
        /// <summary>
        /// Get the full tree of all tables referenced-by or referencing the given table via a
        /// foreign key relationship, directly or indirectly (i.e. via a third table).
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public List<TableRelationship> GenerateTableForeignKeyRelationshipTree(Table table)
        {
            List<TableRelationship> relationshipTree = new List<TableRelationship>();

            // Determine all of the 'forward' references, i.e. the tree of objects that *are referenced by* the given object.

            int currentReferenceLevel = 1;
            List<TableRelationship> currentLevelReferences = this.GetTableForeignKeyRelationshipReferences(table);
            List<TableRelationship> nextLevelReferences = new List<TableRelationship>();

            do
            {
                nextLevelReferences.Clear();

                foreach (TableRelationship reference in currentLevelReferences)
                {
                    // If the reference is new, add all of its references to the tree.

                    if ((!(table.Schema.Name == reference.ReferencedTable.Schema.Name && table.Name == reference.ReferencedTable.Name))
                        && (!relationshipTree.Exists(x => x.ReferencedTable.Schema.Name == reference.ReferencedTable.Schema.Name && x.ReferencedTable.Name == reference.ReferencedTable.Name))
                        && (!nextLevelReferences.Exists(x => x.BaseTable.Schema.Name == reference.ReferencedTable.Schema.Name && x.BaseTable.Name == reference.ReferencedTable.Name)))
                    {
                        nextLevelReferences.AddRange(this.GetTableForeignKeyRelationshipReferences(reference.ReferencedTable, currentReferenceLevel + 1));
                    }
                }

                relationshipTree.AddRange(currentLevelReferences);
                currentReferenceLevel++;
                currentLevelReferences.Clear();
                currentLevelReferences.AddRange(nextLevelReferences);
            } while (nextLevelReferences.Count > 1);

            // Determine all of the 'backward' references, i.e. the tree of objects that *reference* the given object.

            List<TableRelationship> backwardReferences = new List<TableRelationship>();
            currentReferenceLevel = -1;
            currentLevelReferences = this.GetTableForeignKeyRelationshipReferencers(table);

            // Remove self-references, as they're already covered by 'forward references'.
            currentLevelReferences.RemoveAll(r => table.Schema.Name == r.BaseTable.Schema.Name && table.Name == r.BaseTable.Name);

            do
            {
                nextLevelReferences.Clear();

                foreach (TableRelationship reference in currentLevelReferences)
                {
                    // If the referencer (backward reference) is new, add all of *its* referencers
                    // to the tree.

                    if ((!backwardReferences.Exists(x => x.BaseTable.Schema.Name == reference.BaseTable.Schema.Name && x.BaseTable.Name == reference.BaseTable.Name))
                        && (!nextLevelReferences.Exists(x => x.ReferencedTable.Schema.Name == reference.BaseTable.Schema.Name && x.ReferencedTable.Name == reference.BaseTable.Name)))
                    {
                        nextLevelReferences.AddRange(this.GetTableForeignKeyRelationshipReferencers(reference.BaseTable, currentReferenceLevel - 1));
                    }
                }

                backwardReferences.AddRange(currentLevelReferences);
                currentReferenceLevel--;
                currentLevelReferences.Clear();
                currentLevelReferences.AddRange(nextLevelReferences);
            } while (nextLevelReferences.Count > 1);

            relationshipTree.AddRange(backwardReferences);

            return relationshipTree;
        }
        /// <summary>
        /// Load the data describing all check constraints for a given table.
        /// </summary>
        /// <param name="table">The table.</param>
        /// <returns>
        /// The constraint data.
        /// </returns>
        /// <exception cref="System.ArgumentNullException">table;table cannot be null</exception>
        /// <exception cref="System.NotImplementedException">Check constrains are not yet supported</exception>
        public IDataReader LoadCheckConstraintsDataForTable(Table table)
        {
            if (table == null)
            {
                throw new ArgumentNullException("table", "table cannot be null");
            }

            throw new NotImplementedException("Check constrains are not yet supported");
        }
 public string GenerateTableForeignKeyRelationshipTreeAsDot(Table table)
 {
     return table.GenerateForeignKeyRelationshipsAsDot(GenerateTableForeignKeyRelationshipTree(table));
 }
 /// <summary>
 /// Get tables referenced by the given table via a foreign key relationship.
 /// </summary>
 /// <param name="table"></param>
 /// <param name="tableReferenceLevel">This is an optional parameter used to construct the
 /// table relationship objects returned by this method when generating multi-level 'trees'
 /// of relationships.</param>
 /// <returns></returns>
 public List<TableRelationship> GetTableForeignKeyRelationshipReferences(Table table, int tableReferenceLevel = 1)
 {
     using (DataTable data = _database.GetTableForeignKeyRelationshipReferences(table.Schema.Name, table.Name))
     {
         return (from row in data.AsEnumerable()
                 select new TableRelationship(
                     tableReferenceLevel,
                     table,
                     new Table(
                         (int)(row["ObjectId"]),
                         new Schema((string)(row["SchemaName"])),
                         (string)(row["ObjectName"]),
                         null))
             ).ToList<TableRelationship>();
     }
 }
Ejemplo n.º 8
0
        /// <summary>
        /// Loads unique and primary key constraints from the database.
        /// </summary>
        /// <param name="table">The table.</param>
        /// <param name="dataProvider">The data provider.</param>
        /// <exception cref="System.ArgumentNullException">
        /// table;table cannot be null
        /// or
        /// dataProvider;dataProvider cannot be null
        /// </exception>
        public static void PopulateUniqueConstraints(Table table, IObjectDataProvider dataProvider)
        {
            if (table == null)
            {
                throw new ArgumentNullException("table", "table cannot be null");
            }

            if (dataProvider == null)
            {
                throw new ArgumentNullException("dataProvider", "dataProvider cannot be null");
            }

            using (IDataReader result = dataProvider.LoadUniqueConstraintDataForTable(table))
            {
                while (result.Read())
                {
                    // Read the result data
                    string name = (string)result["ConstraintName"];
                    string typeAsText = (string)result["Type"];
                    string columnName = (string)result["ColumnName"];
                    bool isDeferrable = ((string)result["IsDeferrable"]).Equals("NO") ? false : true;
                    bool initiallyDeferred = ((string)result["InitiallyDeferred"]).Equals("NO") ? false : true;

                    // Convert the constraint type
                    ConstraintType type = (ConstraintType)Enum.Parse(typeof(ConstraintType), typeAsText.Replace(" ", string.Empty), true);

                    // If the constraint has already been read, then this is just an additional column
                    // Otherwise it is a new constraint
                    if (table.Constraints.Any(constraint => constraint.Name == name))
                    {
                        // Add the column to the constraint
                        table.Constraints.Single(constraint => constraint.Name == name).AddColumn(table.GetColumn(columnName));
                    }
                    else
                    {
                        // Build the new constraint
                        Constraint newConstraint = new Constraint(name, type, isDeferrable, initiallyDeferred);
                        newConstraint.AddColumn(table.GetColumn(columnName));

                        table.Constraints.Add(newConstraint);
                    }
                }
            }
        }
Ejemplo n.º 9
0
        /// <summary>
        /// Loads check constraints from the database.
        /// </summary>
        /// <param name="table">The table.</param>
        /// <param name="dataProvider">The data provider.</param>
        /// <exception cref="System.ArgumentNullException">table;table cannot be null
        /// or
        /// dataProvider;dataProvider cannot be null</exception>
        public static void PopulateCheckConstraints(Table table, IObjectDataProvider dataProvider)
        {
            if (table == null)
            {
                throw new ArgumentNullException("table", "table cannot be null");
            }

            if (dataProvider == null)
            {
                throw new ArgumentNullException("dataProvider", "dataProvider cannot be null");
            }
        }
 public IDataReader LoadUniqueConstraintDataForTable(Table table)
 {
     DataTable mockTableData = new DataTable();
     return mockTableData.CreateDataReader();
 }
 public IDataReader LoadReferentialConstraintsDataForTable(Table table)
 {
     DataTable mockTableData = new DataTable();
     return mockTableData.CreateDataReader();
 }
 public IDataReader LoadColumnDataForTable(Table table)
 {
     DataTable mockTableData = new DataTable();
     return mockTableData.CreateDataReader();
 }