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