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