public static List <TableDependency> GetParentDependenciesList(string tableSchema, string tableName, List <ForeignKeyConstraint> foreignKeyConstraints, bool recurse, List <TableDependency> processedDependencies = null) { List <TableDependency> tableDependencies = new List <TableDependency>(); // Get filtered list List <ForeignKeyConstraint> foreignKeyConstraintsFiltered = foreignKeyConstraints.Where( fkc => fkc.TableSchema.ToUpper() == tableSchema.ToUpper() && fkc.TableName.ToUpper() == tableName.ToUpper()).ToList(); foreach (IGrouping <string, ForeignKeyConstraint> foreignKeyGroup in foreignKeyConstraintsFiltered.GroupBy(fkc => fkc.ForeignKeyName)) { TableDependency tableDependency = new TableDependency(foreignKeyGroup.ToList()); // Check if current foreign key already processed if (!(processedDependencies?.Contains(tableDependency) ?? false)) { tableDependencies.Add(tableDependency); // Process all dependencies recursively if (recurse) { if (processedDependencies == null) { processedDependencies = new List <TableDependency>(); } processedDependencies.Add(tableDependency); List <TableDependency> result = GetParentDependenciesList(tableDependency.ParentTableName.Schema, tableDependency.ParentTableName.Name, foreignKeyConstraints, recurse, processedDependencies); if (result.Count > 0) { tableDependencies.AddRange(result); } } } } //// Iterate through keys //foreach (ForeignKeyConstraint foreignKeyConstraint in foreignKeyConstraintsFiltered) //{ // // Check if current foreign key already processed // if (!(processedForeignKeyConstraints?.Contains(foreignKeyConstraint) ?? false)) // { // tableDependencies.Add(new TableDependency(foreignKeyConstraint)); // // Process all parent foreign keys recursively // if (recurse) // { // if (processedForeignKeyConstraints == null) // { // processedForeignKeyConstraints = new List<ForeignKeyConstraint>(); // } // processedForeignKeyConstraints.Add(foreignKeyConstraint); // List<TableDependency> result = GetParentDependenciesList(foreignKeyConstraint.ReferencedTableSchema, foreignKeyConstraint.ReferencedTableName, foreignKeyConstraints, recurse, processedForeignKeyConstraints); // if (result.Count > 0) // { // tableDependencies.AddRange(result); // } // } // } //} return(tableDependencies); }
public void AddColumn(Column column) { if (Columns.Contains(column)) { throw new ArgumentException(); } Columns.Add(column); TableDependency.AddKey(); }
public static IgnoredDependencyLists GetIgnoredDependenciesFromDatabase(string server, string database) { var config = new MapperConfiguration(cfg => cfg.AddProfile(new IgnoredDependencyProfile())); var mapper = config.CreateMapper(); Mapper.Initialize(cfg => cfg.AddProfile(new IgnoredDependencyProfile())); IgnoredDependencyLists ignoredDependenciesCollection = new IgnoredDependencyLists(); string constraintsQuery = $@" select [DependencyName] ,[ParentTableSchema] ,[ParentTableName] ,[ParentColumnName] ,[ChildTableSchema] ,[ChildTableName] ,[ChildColumnName] ,[TableDependencyTypeId] from [Scripting].[IgnoredDependency]"; DataSet resultsDataSet = ExecuteQuery(server, database, constraintsQuery); if (resultsDataSet.Tables.Count > 0) { DataTable dataTable = resultsDataSet.Tables[0]; List <DataRow> rows = new List <DataRow>(dataTable.Rows.OfType <DataRow>()); List <IgnoredDependency> ignoredDependencies = mapper.Map <List <DataRow>, List <IgnoredDependency> >(rows); foreach (var group in ignoredDependencies.GroupBy(id => id.DependencyName)) { List <IgnoredDependency> ignoredDependenciesGroup = group.ToList(); TableDependency tableDependency = new TableDependency(ignoredDependenciesGroup); switch (ignoredDependenciesGroup[0].TableDependencyType) { case TableDependencyType.ParentDependency: { ignoredDependenciesCollection.ParentDependencies.Add(tableDependency); break; } case TableDependencyType.ChildDependency: { ignoredDependenciesCollection.ChildDependencies.Add(tableDependency); break; } } } } else { // todo write to log } return(ignoredDependenciesCollection); }
private static List <TableDependency> GetTableDependencies(string connectionString) { using (var con = new SqlConnection(connectionString)) { con.Open(); using (var cmd = new SqlCommand()) { cmd.Connection = con; cmd.CommandType = CommandType.Text; cmd.CommandText = "SELECT ObjectSchema.name AS [Schema], Object.name AS Name, ForeignSchema.name AS ForeignSchema, ForeignObject.name AS ForeignName " + "FROM sys.objects AS ForeignObject INNER JOIN " + "sys.foreign_key_columns ON ForeignObject.object_id = sys.foreign_key_columns.referenced_object_id " + "INNER JOIN sys.objects AS Object ON sys.foreign_key_columns.parent_object_id = Object.object_id " + "INNER JOIN sys.schemas AS ObjectSchema ON Object.schema_id = ObjectSchema.schema_id " + "INNER JOIN sys.schemas AS ForeignSchema ON ForeignObject.schema_id = ForeignSchema.schema_id"; var tableDependencies = new List <TableDependency>(); using (var dr = cmd.ExecuteReader()) { while (dr.Read()) { var schema = dr.GetString(0); var name = dr.GetString(1); var foreignSchema = dr.GetString(2); var foreignName = dr.GetString(3); // Add schema if needed var specifier = schema == "dbo" ? name : $"{schema}.{name}"; var foreignSpecifier = foreignSchema == "dbo" ? foreignName : $"{foreignSchema}.{foreignName}"; var foreignRecord = tableDependencies.SingleOrDefault(x => x.Foreign == foreignSpecifier); if (foreignRecord == null) { foreignRecord = new TableDependency { Foreign = foreignSpecifier }; tableDependencies.Add(foreignRecord); } foreignRecord.Related.Add(specifier); } } return(tableDependencies); } } }
public void RemoveColumn(int index) { if (index >= Columns.Count) { return; } var column = Columns[index]; for (var i = 0; i < PrimaryKey.Count; i++) { if (PrimaryKey[i].Equals(column)) { PrimaryKey.RemoveAt(i); i--; } } TableDependency.RemoveKey(index); Columns.RemoveAt(index); }
public Dictionary <string, ExtractedTable> ExportDataFromDatabase(string query, bool includeSoftDependencies, bool excludeUnwantedDependencies) { Dictionary <string, ExtractedTable> extractedTables = new Dictionary <string, ExtractedTable>(); //DataTable primaryKeyConstraints = DataReader.GetPrimaryKeyConstraintsFromDatabase(ServerName, DatabaseName); List <ForeignKeyConstraint> foreignKeyConstraints = new List <ForeignKeyConstraint>(ForeignKeyConstraints); //IgnoredDependencyLists ignoredDependencyLists = new IgnoredDependencyLists(); if (includeSoftDependencies) { foreignKeyConstraints.AddRange(SoftConstraints); } // Parse query into separate selects List <SelectStatement> selectStatementsFromQuery = SqlStatementParser.ParseSelectStatement(query); foreach (SelectStatement selectStatement in selectStatementsFromQuery) { // Execute unmodified select to pull primary keys for new clean select query DataSet result = DataReader.ExecuteQuery(ServerName, DatabaseName, selectStatement.SelectText); // Get primary key constraints for current table List <string> primaryKeyColumnNames = DataReader.GetListOfPrimaryKeyConstraints(selectStatement.PrimaryTableName, PrimaryKeyConstraints).Select(row => row["ColumnName"].ToString()).ToList(); // Get primary keys from query results Dictionary <string, List <string> > primaryKeysAndValues = DataReader.GetPrimaryKeyValues(selectStatement.PrimaryTableName, result.Tables[0], PrimaryKeyConstraints); // Create new select using primary keys string selectUsingPrimaryKeys = SqlStatementParser.NewSelectWhereKeysInValues(selectStatement.PrimaryTableName.FullNameDelimited, primaryKeysAndValues); // Execute new select ExtractedTable extractedTable = new ExtractedTable { TableName = selectStatement.PrimaryTableName, PrimaryKeyColumnNames = primaryKeyColumnNames, DataTable = DataReader.ExecuteQueryUsingForXml(ServerName, DatabaseName, selectUsingPrimaryKeys).Tables[0] }; // Set primary keys on DataTable DataParser.SetDataTablePrimaryKey(primaryKeyColumnNames, extractedTable.DataTable); extractedTable.DataTable.AcceptChanges(); // Add to extracted tables collection extractedTables.Add(extractedTable.TableName.FullName, extractedTable); // Get dependencies List <TableDependency> parentDependencies = DataParser.GetParentDependenciesList(selectStatement.PrimaryTableName, foreignKeyConstraints, true); List <TableDependency> childDependencies = DataParser.GetChildDependenciesList(selectStatement.PrimaryTableName, foreignKeyConstraints, true); // Get child dependencies for all parent dependencies //foreach (TableDependency tableDependency in parentDependencies) //{ // childDependencies.AddRange(DataParser.GetChildDependenciesList(tableDependency.ParentTableName.Schema, tableDependency.ParentTableName.Name, foreignKeyConstraints, true)); //} childDependencies = childDependencies.Distinct().ToList(); if (excludeUnwantedDependencies) { DataParser.RemoveUnwantedDependencies(childDependencies, IgnoredDependencyLists.ChildDependencies); } // Get parent dependencies for all child dependencies foreach (TableDependency tableDependency in childDependencies) { parentDependencies.AddRange(DataParser.GetParentDependenciesList(tableDependency.ChildTableName.Schema, tableDependency.ChildTableName.Name, foreignKeyConstraints, true)); } parentDependencies = parentDependencies.Distinct().ToList(); if (excludeUnwantedDependencies) { DataParser.RemoveUnwantedDependencies(parentDependencies, IgnoredDependencyLists.ParentDependencies); } Queue <TableDependency> parentDependencyQueue = new Queue <TableDependency>(parentDependencies); Queue <TableDependency> childDependencyQueue = new Queue <TableDependency>(childDependencies); List <TableDependency> failedParentDependencies = new List <TableDependency>(); List <TableDependency> failedChildDependencies = new List <TableDependency>(); while (parentDependencyQueue.Count > 0 || childDependencyQueue.Count > 0) { while (parentDependencyQueue.Count > 0) { TableDependency tableDependency = parentDependencyQueue.Dequeue(); if (extractedTables.ContainsKey(tableDependency.ChildTableName.FullName)) { //Write-Host "Retrieving [{foreignKeyConstraint.ConstraintColumnName)] foreign key values" todo // Get foreign key values, exclude null values Dictionary <string, List <string> > keyValuesList = DataParser.GetColumnValues( extractedTables[tableDependency.ChildTableName.FullName].DataTable, tableDependency.ColumnDependencies, TableDependencyType.ChildDependency, TableDependencyType.ParentDependency, true, true); if (keyValuesList.Count > 0) { DataTable dataTable = RetrieveTable(ServerName, DatabaseName, tableDependency.ParentTableName, keyValuesList, PrimaryKeyConstraints); int originalRowCount = 0; // Add parent table to overall results if (extractedTables.ContainsKey(tableDependency.ParentTableName.FullName)) { originalRowCount = extractedTables[tableDependency.ParentTableName.FullName].DataTable.Rows.Count; extractedTables[tableDependency.ParentTableName.FullName].DataTable.Merge(dataTable); } else { extractedTables.Add( tableDependency.ParentTableName.FullName, new ExtractedTable { TableName = tableDependency.ParentTableName, PrimaryKeyColumnNames = primaryKeyColumnNames, DataTable = dataTable } ); failedParentDependencies.Remove(tableDependency); } //if (originalRowCount < extractedTables[tableDependency.ParentTableName.FullName].DataTable.Rows.Count) //{ // List<TableDependency> tmp = childDependencyQueue.ToList(); // foreach (TableDependency dependency in DataParser.GetChildDependenciesList(tableDependency.ParentTableName.Schema, tableDependency.ParentTableName.Name, foreignKeyConstraints, true)) // { // tmp.Add(dependency); // } // tmp = tmp.Distinct().ToList(); // DataParser.RemoveUnwantedDependencies(tmp, IgnoredDependencyLists.ChildDependencies); // childDependencyQueue = new Queue<TableDependency>(tmp); //} } else { //Write-Host "Table $parentTableNameDelimited not retrieved. $childTableNameDelimited.[$(foreignKeyConstraint.ConstraintColumnName)] values are all DBNull"; todo } } else { if (!failedParentDependencies.Contains(tableDependency)) { failedParentDependencies.Add(tableDependency); parentDependencyQueue.Enqueue(tableDependency); } } } while (childDependencyQueue.Count > 0) { TableDependency tableDependency = childDependencyQueue.Dequeue(); if (extractedTables.ContainsKey(tableDependency.ParentTableName.FullName)) { //Write-Host "Retrieving [{foreignKeyConstraint.ConstraintColumnName)] foreign key values" todo // Get foreign key values, exclude null values Dictionary <string, List <string> > keyValuesList = DataParser.GetColumnValues( extractedTables[tableDependency.ParentTableName.FullName].DataTable, tableDependency.ColumnDependencies, TableDependencyType.ParentDependency, TableDependencyType.ChildDependency, true, true); if (keyValuesList.Count > 0) { DataTable dataTable = RetrieveTable(ServerName, DatabaseName, tableDependency.ChildTableName, keyValuesList, PrimaryKeyConstraints); if (dataTable.Rows.Count > 0) { int originalRowCount = 0; // Add parent table to overall results if (extractedTables.ContainsKey(tableDependency.ChildTableName.FullName)) { originalRowCount = extractedTables[tableDependency.ChildTableName.FullName].DataTable.Rows.Count; extractedTables[tableDependency.ChildTableName.FullName].DataTable.Merge(dataTable); } else { extractedTables.Add( tableDependency.ChildTableName.FullName, new ExtractedTable { TableName = tableDependency.ChildTableName, PrimaryKeyColumnNames = primaryKeyColumnNames, DataTable = dataTable } ); } if (originalRowCount < extractedTables[tableDependency.ChildTableName.FullName].DataTable.Rows.Count) { List <TableDependency> tmp = parentDependencyQueue.ToList(); foreach (TableDependency dependency in DataParser.GetParentDependenciesList(tableDependency.ChildTableName.Schema, tableDependency.ChildTableName.Name, foreignKeyConstraints, true)) { tmp.Add(dependency); } tmp = tmp.Distinct().ToList(); DataParser.RemoveUnwantedDependencies(tmp, IgnoredDependencyLists.ParentDependencies); parentDependencyQueue = new Queue <TableDependency>(tmp); } } else { // todo write warning in log } } else { //Write-Host "Table $parentTableNameDelimited not retrieved. $childTableNameDelimited.[$(foreignKeyConstraint.ConstraintColumnName)] values are all DBNull"; todo } } else { if (!failedChildDependencies.Contains(tableDependency)) { failedChildDependencies.Add(tableDependency); childDependencyQueue.Enqueue(tableDependency); } } } } } return(extractedTables); }