如何基于DacFX的Visitor模式注释TSqlFragment/TSqlScript中的指定语句？

Great question! Since DacFX doesn’t provide a built-in CommentStatement type, the solution relies on replacing your target statement nodes with parsed comment blocks while sticking to the Visitor pattern for traversal. Here’s a practical, step-by-step implementation:

Step 1: Create a Custom Visitor to Identify Target Statements #

First, build a visitor that traverses the syntax tree and collects the statements you want to comment. Override the Visit method for the specific statement types you care about (e.g., SelectStatement, InsertStatement) and add logic to filter which statements get flagged.

using Microsoft.SqlServer.TransactSql.ScriptDom;
using System.Collections.Generic;
using System.IO;
using System.Linq;

public class StatementCommentVisitor : TSqlConcreteFragmentVisitor
{
    private readonly List<TSqlFragment> _targetStatements = new();
    private readonly TSqlParser _parser = new TSql160Parser(false); // Match your SQL server version

    public IEnumerable<TSqlFragment> TargetStatements => _targetStatements;

    // Override Visit for your target statement type (example: SelectStatement)
    public override void Visit(SelectStatement node)
    {
        // Add your custom logic to decide if this statement should be commented
        if (IsTargetStatement(node))
        {
            _targetStatements.Add(node);
        }

        // Continue traversing child nodes to catch nested statements if needed
        base.Visit(node);
    }

    // Example filter: Comment SELECT statements referencing "MyTargetTable"
    private bool IsTargetStatement(SelectStatement node)
    {
        if (node.QueryExpression is QuerySpecification querySpec)
        {
            foreach (var tableRef in querySpec.FromClause.TableReferences)
            {
                if (tableRef is NamedTableReference namedRef 
                    && namedRef.SchemaObject.BaseIdentifier.Value.Equals("MyTargetTable", StringComparison.OrdinalIgnoreCase))
                {
                    return true;
                }
            }
        }
        return false;
    }

    // Convert original statement text to a valid comment fragment
    public TSqlFragment GenerateCommentFragment(TSqlFragment originalStatement)
    {
        // Generate the original statement's raw text
        var scriptGen = new Sql160ScriptGenerator(); // Match parser version to avoid mismatches
        scriptGen.GenerateScript(originalStatement, out string originalText);

        // Wrap in multi-line comments (swap to -- for single-line if preferred)
        var commentText = $"/*{originalText}*/";

        // Parse the comment text into a valid TSqlFragment (critical for syntax tree integrity)
        using var reader = new StringReader(commentText);
        var parseErrors = new List<ParseError>();
        var commentFragment = _parser.Parse(reader, out parseErrors);

        if (parseErrors.Any())
        {
            throw new InvalidOperationException($"Failed to parse comment: {string.Join("; ", parseErrors.Select(e => e.Message))}");
        }

        return commentFragment;
    }
}

Step 2: Replace Target Statements with Comments #

Use DacFX's FragmentReplacer to swap the identified statements with the generated comment fragments. This ensures the syntax tree remains valid and follows DacFX's immutable node structure.

public static TSqlScript CommentSelectedStatements(TSqlScript originalScript)
{
    var visitor = new StatementCommentVisitor();
    originalScript.Accept(visitor);

    var replacer = new FragmentReplacer();

    foreach (var statement in visitor.TargetStatements)
    {
        var commentFragment = visitor.GenerateCommentFragment(statement);
        replacer.AddReplacement(statement, commentFragment);
    }

    // Apply replacements and return the modified script
    return replacer.Apply(originalScript) as TSqlScript;
}

Key Notes & Customizations #

• Version Consistency: Always match the TSqlParser and SqlScriptGenerator versions (e.g., TSql160Parser with Sql160ScriptGenerator) to avoid parsing/generation errors.
• Single-Line Comments: To use -- instead of /* */, modify the GenerateCommentFragment method to prefix each line with --:

  var commentLines = originalText.Split(Environment.NewLine);
  var commentText = string.Join(Environment.NewLine, commentLines.Select(line => $"--{line}"));
  

• Support Multiple Statement Types: Override additional Visit methods (e.g., VisitInsertStatement, VisitUpdateStatement) in your visitor to handle other statement types.
• Single TSqlFragment: If working with a standalone TSqlFragment instead of TSqlScript, adjust the replacement logic to target the fragment's parent node (ensure the replacement fits in the parent's child collection).

This approach strictly adheres to the Visitor pattern constraint, avoids raw text manipulation, and maintains the integrity of the DacFX syntax tree.

内容的提问来源于stack exchange，提问作者SpaceGhost440