Using DacFX how do I comment out a statement in a TsqlFragment/TSqlScript

If I have a TqlFragment or TsqlScript with a set of statements, how do I comment out a specific statement using DacFX? There is no CommentStatement or anything like that. How do I replace that statement in the syntax tree with a commented version of it?

I know this could be accomplished with pure text editing or regex but at this time I am using the DacFx visitor pattern to scan for certain statements. So I need to remain using that constraint.

Solution

As you have found out there there is no script dom statement for a comment, what we do have is a token stream which is like a statement but at a slightly lower level.

I'm not 100% sure what it is you are after, but if I assume that you have a script and want to comment a statement out then it is possible like this. You said that you needed to stay within the visitor pattern but a comment won't be returned by a visitor so this should do what you want and you can either re-parse the script again or just leave it as sql and do what you like with it:

   static void Main(string[] args)
    {

        var sqlText = @"
         create procedure something
            as
                select 100;
                select 200

                exec sp_who2;              
        ";

        var sql = new StringReader(sqlText);

        var parser = new TSql140Parser(false);
        IList<ParseError> errors;
        var script = parser.Parse(sql, out errors);
        var visitor = new visitor();
        script.Accept(visitor);

        TSqlParserToken startComment = new TSqlParserToken(TSqlTokenType.SingleLineComment, "/*");
        TSqlParserToken endComment = new TSqlParserToken(TSqlTokenType.SingleLineComment, "*/");


        var newScriptSql = "";

        for (var i = 0; i < script.LastTokenIndex; i++)
        {
            if (i == visitor.Statement.FirstTokenIndex)
            {
                newScriptSql += startComment.Text;
            }

            if (i == visitor.Statement.LastTokenIndex)
            {
                newScriptSql += endComment.Text;
            }

            newScriptSql += script.ScriptTokenStream[i].Text;
        }

        script = parser.Parse(new StringReader(newScriptSql), out errors);

        Console.WriteLine(newScriptSql);
    }

    class visitor : TSqlFragmentVisitor
    {
        public ExecuteStatement Statement;
        public override void Visit(ExecuteStatement s)
        {
            Statement = s;
        }
    }
}

What this does is to pass the procedure above (put it in a code file or something) and uses the visitor pattern to find the exec, once we have that we take a note of where the statement started and ended in the script token stream, then we iterate through the tokens turning them into t-sql, when we get to our statement we insert a startComment, after the statement we insert an endToken (note it is before the ; so you might want to add some extra logic to handle that).

I tried just altering the ScriptTokenStream of the original script but even though it allows you, if you do anything with it then it is ignored so consider it immutable (unless i'm missing something). This doesn't work as far as I can tell but would make the roundtrip to text/parse unnessesary if it did:

        /* - changes the token stream but if you use a generator on script, they don't appear
        script.ScriptTokenStream.Insert(visitor.Statement.LastTokenIndex, endComment);
        script.ScriptTokenStream.Insert(visitor.Statement.FirstTokenIndex, startComment);
        */

Hope it helps!