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