Beispiel #1
0
        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);
        }
Beispiel #2
0
        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);
     }