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 void ExportDataToScripts(string query, string _DataDirectory, DataScriptType dataScriptType, SqlScriptType sqlScriptType, bool includeSoftDependencies, bool excludeUnwantedDependencies, bool autoMerge, string environmentName) { Dictionary <string, ExtractedTable> results = ExportDataFromDatabase(query, includeSoftDependencies, excludeUnwantedDependencies); string dataScriptDirectory = string.Empty; if (dataScriptType == DataScriptType.Static) { dataScriptDirectory = Path.Combine(_DataDirectory, "Static"); } else if (dataScriptType == DataScriptType.Test) { dataScriptDirectory = Path.Combine(_DataDirectory, "Test"); } if (!Directory.Exists(dataScriptDirectory)) { Directory.CreateDirectory(dataScriptDirectory); } // Check for existing script files if autoMerge not set if (!autoMerge) { List <string> existingScripts = new List <string>(); foreach (string tableName in results.Keys) { string scriptName = $"{tableName}.sql"; if (File.Exists(Path.Combine(dataScriptDirectory, scriptName))) { existingScripts.Add(scriptName); } } if (existingScripts.Count > 0) { throw new System.Exception($"{sqlScriptType.ToString()} script already exists for: {string.Join(", ", existingScripts)}. Enable auto merge to merge the new data with the existing data."); } } // Cleanse data DataCleanser dataCleanser = new DataCleanser(); dataCleanser.ReplaceImagePathFields(results.Values.Select(et => et.DataTable).ToList()); // Create scripts for each table foreach (string tableName in results.Keys) { FileInfo scriptFileInfo = new FileInfo(Path.Combine(dataScriptDirectory, $"{tableName}.sql")); // If script already exists, merge if (scriptFileInfo.Exists) { DataTable scriptDataTable = null; if (sqlScriptType == SqlScriptType.Insert) { scriptDataTable = SqlDataScriptHelper.NewDataTableFromInsertScript(scriptFileInfo.FullName, DataParser.GetXmlSchemaFromDataTable(results[tableName].DataTable)); } else if (sqlScriptType == SqlScriptType.Merge) { // todo throw new System.Exception("Not implemented"); } results[tableName].DataTable.Merge(scriptDataTable); } string script = null; if (sqlScriptType == SqlScriptType.Insert) { script = SqlDataScriptHelper.NewInsertScriptFromDataTable(results[tableName].TableName.Schema, results[tableName].TableName.Name, results[tableName].DataTable, results[tableName].PrimaryKeyColumnNames); } else if (sqlScriptType == SqlScriptType.Merge) { script = SqlDataScriptHelper.NewMergeScriptFromDataTable(results[tableName].TableName.Schema, results[tableName].TableName.Name, results[tableName].DataTable, false, true, environmentName, results[tableName].PrimaryKeyColumnNames); } SqlDataScriptHelper.WriteScriptToFile(scriptFileInfo.Name, script, dataScriptDirectory, true); } string sqlCmdScript = SqlDataScriptHelper.NewSqlCmdScript(dataScriptDirectory, dataScriptType); string sqlCmdScriptName = string.Empty; if (dataScriptType == DataScriptType.Static) { sqlCmdScriptName = "StaticData.sql"; } else if (dataScriptType == DataScriptType.Test) { sqlCmdScriptName = "TestData.sql"; } SqlDataScriptHelper.WriteScriptToFile(sqlCmdScriptName, sqlCmdScript, _DataDirectory, true); }
public static DataTable NewDataTableFromInsertScript(string insertScriptPath, string dataTableXml) { DataTable dataTable = DataParser.NewDataTableFromXml(dataTableXml); FileInfo insertScriptFileInfo = new FileInfo(insertScriptPath); if (insertScriptFileInfo.Exists) { string insertScript = File.ReadAllText(insertScriptFileInfo.FullName); // Break up insert script into parts Match regexMatch = Regex.Match(insertScript, @"INSERT INTO \[(?<TableSchema>[^\]\[]{1,})\].\[(?<TableName>[^\]\[]{1,})\]\s*\((?<ColumnNames>[^\)\(]{1,})\)\s*VALUES\s*(?<Rows>\((?s:.*)\))"); if (regexMatch.Success) { string tableSchema = string.Empty; string tableName = string.Empty; string[] columnNames = new string[0]; string rows = string.Empty; Group matchSearch = regexMatch.Groups["TableSchema"]; if (matchSearch.Success) { tableSchema = matchSearch.Value; } matchSearch = regexMatch.Groups["TableName"]; if (matchSearch.Success) { tableName = matchSearch.Value; } matchSearch = regexMatch.Groups["ColumnNames"]; MatchCollection regexMatches; if (matchSearch.Success) { regexMatches = Regex.Matches(matchSearch.Value, @"\[(?<ColumnName>[^\]\[]+)\]"); columnNames = new string[regexMatches.Count]; for (int i = 0; i < regexMatches.Count; i++) { if (regexMatches[i].Success) { columnNames[i] = regexMatches[i].Groups["ColumnName"].Value; } } } matchSearch = regexMatch.Groups["Rows"]; if (matchSearch.Success) { rows = matchSearch.Value; } regexMatches = Regex.Matches(rows, @"\((?<RowValues>.*)\),?"); foreach (Match match in regexMatches) { if (match.Success) { matchSearch = match.Groups["RowValues"]; if (matchSearch.Success) { DataRow newRow = dataTable.NewRow(); string[] rowValues = new string[columnNames.Count()]; int columnIndex = 0; foreach (TSQLToken tSqlToken in TSQLTokenizer.ParseTokens(matchSearch.Value)) { if (!(tSqlToken is TSQLCharacter && tSqlToken.Text == ",")) { newRow[columnNames[columnIndex]] = SqlValueStringToTypedObject( tSqlToken.Text, dataTable.Columns[columnNames[columnIndex]].DataType); columnIndex++; } } dataTable.Rows.Add(newRow); } } } } } 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); }