public static DataTable RetrieveTableWhereKeysInValues(string serverName, string databaseName, TableName tableName, Dictionary <string, List <string> > keysAndValues, List <string> primaryKeyColumnNameList)
{
DataTable dataTable = new DataTable();
if (keysAndValues.Count > 0)
{
string tableQuery = SqlStatementParser.NewSelectWhereKeysInValues(tableName.FullNameDelimited, keysAndValues);
//Write-Host "Retrieving parent table $parentTableNameDelimited"
//Write-Host "Where $(foreignKeyConstraint.ReferencedColumnName) in ('$foreignKeyValues')" - ForegroundColor DarkGray
// Retrieve parent table using foreign key values
dataTable = ExecuteQueryUsingForXml(serverName, databaseName, tableQuery).Tables[tableName.FullName];
// Set primary keys on parent DataTable
DataParser.SetDataTablePrimaryKey(primaryKeyColumnNameList, dataTable);
dataTable.AcceptChanges();
}
else
{
//Write-Host "Table $parentTableNameDelimited not retrieved. $childTableNameDelimited.[$(foreignKeyConstraint.ConstraintColumnName)] values are all DBNull";
}
return(dataTable);
}
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);
}
public static DataTable ParseTableCreate(string statement)
{
try
{
List <string> warningMessages = new List <string>();
// Get first create statement
Match createRegexMatch = Regex.Match(statement, @"(?i)create[^;]+;");
if (!createRegexMatch.Success)
{
throw new Exception("Unable to parse create statement.");
}
// Get create statement parts
Match tableCreateRegexMatch = Regex.Match(createRegexMatch.Value, @"(?i)create[\s]+table[\s]+(?<TableSchema>[^.]+).(?<TableName>[^\s]+)\s*\((?<ColumnDefinitions>(?:.|\s)+)\);");
if (!tableCreateRegexMatch.Success)
{
throw new SqlScriptParsingException("Unable to parse table create statement.");
}
// Get table schema
Group matchSearch = tableCreateRegexMatch.Groups["TableSchema"];
if (!matchSearch.Success)
{
throw new SqlScriptParsingException("Unable to parse table create statement - TableSchema.");
}
string tableSchema = matchSearch.Value;
tableSchema = tableSchema.Replace("[", "").Replace("]", "");
// Get table name
matchSearch = tableCreateRegexMatch.Groups["TableName"];
if (!matchSearch.Success)
{
throw new SqlScriptParsingException("Unable to parse table create statement - TableName.");
}
string tableName = matchSearch.Value;
tableName = tableName.Replace("[", "").Replace("]", "");
// Get field definitions
matchSearch = tableCreateRegexMatch.Groups["ColumnDefinitions"];
if (!matchSearch.Success)
{
throw new SqlScriptParsingException("Unable to parse table create statement - ColumnDefinitions.");
}
string columnDefinitionsReplaceNewLine = matchSearch.Value.Replace("\r\n", "\n");
// Get individual column definitions
//MatchCollection columnDefinitionRegexMatch = Regex.Matches(matchSearch.Value, @"(?i)(?:(?<ColumnDefinition>[\s]*(?<ColumnName>[\S]+)[\s]+(?<DataType>[a-z2_]+)[\s]*(?<Precision>\((?:max|[0-9]+|[0-9]+,[\s]+[0-9])\))?[\s]+(?<Nullability>null|not[\s]null)?,?(?<ColumnConstraint>primary[\s]key|unique)?,?)|(?<ColumnConstraintDefinition>[\s]*constraint[\s]+[\S]+[\s]+(?:(?:(?:primary[\s]+key|unique|)[\s]+(?:clustered|non[\s]clustered)[\s]*(?:\([^\)]+\))?)|(?:foreign[\s]+key[\s]+[\S]+[\s]+references[\s]+[\S]+[\s]+\([^\)]+\))),?))");
//MatchCollection columnDefinitionRegexMatch = Regex.Matches(matchSearch.Value, @"(?i)(?:(?<ColumnDefinition>[\s]*(?<ColumnName>(?!constraint)[\S]+)[\s]+(?<DataType>[\[a-z2_\]]+)[\s]*(?<Precision>\((?:max|[0-9]+|[0-9]+,[\s]+[0-9])\))?[\s]+(?<Nullability>null|not[\s]null)?,?(?<ColumnConstraint>primary[\s]key|unique)?,?)|(?<ColumnConstraintDefinition>[\s]*constraint[\s]+(?<ConstraintName>[\S]+)[\s]+(?:(?:(?:(?<ConstraintType>primary[\s]+key)|unique|)[\s]+(?:clustered|non[\s]clustered)[\s]*(?:\((?<ConstraintColumnNames>[^\)]+)\))?)|(?:foreign[\s]+key[\s]+[\S]+[\s]+references[\s]+[\S]+[\s]+\([^\)]+\))),?))");
//MatchCollection columnDefinitionRegexMatch = Regex.Matches(matchSearch.Value, @"(?i)(?:(?<ColumnDefinition>\[?(?<ColumnName>(?!constraint)[^\s,\]]+)\]?[\s]+(?:\[?(?<ColumnDataType>[a-z2_]+)\]?)[\s]*(?:\((?<ColumnDataTypePrecision>(?:max|[0-9]+|[0-9]+,[\s]+[0-9]))\))?[\s]+(?<ColumnConstraint>(?:constraint[\s]+[\S]+[\s]+(?:default[\s]+[\S]+)?))?[\s]*(?<ColumnIdentity>identity[\s]*(?:\([0-9]+,[\s]*[0-9]+\))?)?[\s]*(?<ColumnNotForReplication>not[\s]+for[\s]+replication)?[\s]*(?<ColumnNullability>null|not[\s]null)?,?(?<TableConstraint>primary[\s]key|unique)?,?)|(?<TableConstraintDefinition>constraint[\s]+\[?(?<TableConstraintName>[^\s\]]+)\]?[\s]+(?:(?<TableConstraintUnique>(?<TableConstraintUniqueType>primary[\s]+key|unique)[\s]+(?:clustered|non[\s]clustered)[\s]*(?:\((?<TableConstraintColumnNames>[^\)]+)\))?)|(?<TableConstraintForeignKey>foreign[\s]+key[\s]+[\S]+[\s]+references[\s]+[\S]+[\s]+\([^\)]+\))),?))");
MatchCollection columnDefinitionRegexMatch = Regex.Matches(columnDefinitionsReplaceNewLine, @"(?im)(?:(?<TableConstraintDefinition>^\s*(?:constraint\s+\[?(?<TableConstraintName>[^\s\]]+)\]?\s+)?(?:(?<TableConstraintUnique>(?<TableConstraintUniqueType>primary\s+key|unique)\s+(?:clustered|non\s*clustered)\s*(?:\((?<TableConstraintColumnNames>[^\)]+)\))?)|(?<TableConstraintForeignKey>foreign[\s]+key[\s]+[\S]+[\s]+references[\s]+[\S]+[\s]+\([^\)]+\))),?)|(?<ColumnDefinition>^\s*\[?(?<ColumnName>(?!constraint)[^\s,\]]+)\]?[\s]+(?:\[?(?<ColumnDataType>[a-z2_]+)\]?)[\s]*(?:\((?<ColumnDataTypePrecision>(?:max|[0-9]+|[0-9]+,[\s]+[0-9]))\))?[\s]+(?<ColumnConstraint>(?:constraint[\s]+[\S]+[\s]+(?:default[\s]+[\S]+)?))?[\s]*(?<ColumnIdentity>identity[\s]*(?:\([0-9]+,[\s]*[0-9]+\))?)?[\s]*(?<ColumnNotForReplication>not[\s]+for[\s]+replication)?[\s]*(?<ColumnNullability>null|not[\s]null)?,?\s*$(?<TableConstraint>primary[\s]key|unique)?,?))");
if (columnDefinitionRegexMatch.Count < 0)
{
throw new SqlScriptParsingException("Unable to parse table create statement - individual column definitions.");
}
DataTable dataTable = new DataTable($"{tableSchema}.{tableName}");
DataColumn dataColumn;
// Iterate through individual column definitions and create new data column objects
foreach (Match definition in columnDefinitionRegexMatch)
{
// Check whether definition is table constraint
if (definition.Groups["TableConstraintDefinition"].Success)
{
// Check if primary key
matchSearch = definition.Groups["TableConstraintUniqueType"];
if (matchSearch.Success && matchSearch.Value.ToLower().Contains("primary"))
{
matchSearch = definition.Groups["TableConstraintColumnNames"];
if (matchSearch.Success)
{
List <DataColumn> primaryKeyColumns = new List <DataColumn>();
MatchCollection primaryKeyColumnNameMatchCollection = Regex.Matches(matchSearch.Value, @"(?i)\[?(?<PrimaryKeyColumnName>[^\s\]]+)\]?[\s]+(?:asc|desc)");
foreach (Match primaryKeyColumnNameMatch in primaryKeyColumnNameMatchCollection)
{
// Get primary key column name
matchSearch = primaryKeyColumnNameMatch.Groups["PrimaryKeyColumnName"];
if (!matchSearch.Success)
{
throw new SqlScriptParsingException("Unable to parse table primary key - ColumnName.");
}
string primaryKeyColumnName = matchSearch.Value;
primaryKeyColumns.Add(dataTable.Columns[primaryKeyColumnName]);
}
dataTable.PrimaryKey = primaryKeyColumns.ToArray();
}
}
}
else
{
// Get column name
matchSearch = definition.Groups["ColumnName"];
if (!matchSearch.Success)
{
throw new SqlScriptParsingException("Unable to parse table create statement - individual column definitions - ColumnName.");
}
string columnName = matchSearch.Value;
// Get data type
matchSearch = definition.Groups["ColumnDataType"];
if (!matchSearch.Success)
{
throw new SqlScriptParsingException("Unable to parse table create statement - individual column definitions - DataType.");
}
string dataType = matchSearch.Value;
// Get precision
string precision = string.Empty;
matchSearch = definition.Groups["ColumnDataTypePrecision"];
if (matchSearch.Success)
{
precision = matchSearch.Value;
precision = precision.Replace("(", "").Replace(")", "");
}
else
{
warningMessages.Add($"Column definition parsing warning - Could not determine Precision for: \"{definition.Value}\".");
}
// Get nullability
string nullability = string.Empty;
matchSearch = definition.Groups["ColumnNullability"];
if (matchSearch.Success)
{
nullability = matchSearch.Value;
}
else
{
warningMessages.Add($"Could not determine - Nullability.");
}
Type nativeDataType = SqlStatementParser.SqlDbEngineTypeToNativeType(dataType);
// Create new data column
dataColumn = new DataColumn(columnName, nativeDataType);
dataColumn.ExtendedProperties.Add("SqlDbEngineType", $"{dataType}{(string.IsNullOrWhiteSpace(precision) ? string.Empty : $"({precision})")}");
if (nativeDataType == typeof(string) && precision.ToLower() != "max")
{
dataColumn.MaxLength = int.Parse(precision);
}
dataColumn.AllowDBNull = string.IsNullOrWhiteSpace(nullability) || nullability.ToLower() == "null";
dataTable.Columns.Add(dataColumn);
}
}
return(dataTable);
}
