/// <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(); } }
/// <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>(); } }
/// <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); } } } }
/// <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(); }