How can I write new T-SQL script with ScriptDom and changed columns?

Question

I would like to build T-SQL parser which will be building new queries for different DBMS with specific rules. First rule which would like to implement is to add to every column name ' at the beginning and end of column name.

I get code from below link SQL, all column names are in brackets, C#

Used Code:

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

namespace ConsoleApplication8
{
    public class QueryParser
    {
        public IEnumerable<string> Parse(string sqlSelect)
        {
            TSql100Parser parser = new TSql100Parser(false);
            TextReader rd = new StringReader(sqlSelect);
            IList<ParseError> errors;
            var columns = new List<string>();

            var fragments = parser.Parse(rd, out errors);
            var columnVisitor = new SQLVisitor();
            fragments.Accept(columnVisitor);
            columns = new List<string>(columnVisitor.Columns);

            return columns;
        }
    }


    internal class SQLVisitor : TSqlFragmentVisitor
    {
        private List<string> columns = new List<string>();

        private string GetNodeTokenText(TSqlFragment fragment) 
        { 
            StringBuilder tokenText = new StringBuilder(); 
            for (int counter = fragment.FirstTokenIndex; counter <= fragment.LastTokenIndex; counter++) 
            { 
                tokenText.Append(fragment.ScriptTokenStream[counter].Text); 
            }

            return tokenText.ToString(); 
        }


        public override void ExplicitVisit(ColumnReferenceExpression node)
        {
            columns.Add(GetNodeTokenText(node));
        }

        public IEnumerable<string>  Columns {
            get { return columns; }
        }
    } 

    public class Program
    {

        private static void Main(string[] args)
        {
            QueryParser queryParser = new QueryParser();
            var columns = queryParser.Parse("SELECT A,[B],C,[D],E FROM T  WHERE isnumeric(col3) = 1 Order by Id desc");
            foreach (var column in columns)
            {
                Console.WriteLine(column);
            }
        }
    }
}

But I do not know how to build a new script which the only difference between old and new one will be column names with '.

So the result of below SQL:

SELECT A,[B],C,[D],E FROM T  WHERE isnumeric(col3) = 1 Order by [Id] desc

should be

SELECT A,'B',C,'D',E FROM T WHERE isnumeric(col3) = 1 Order by 'Id' desc

Answer 1

Below is a refactored version of the code that replaces square bracket enclosures in column identifier references with single quotes and returns the transformed script. This will also replace enclosures in multi-part identifiers.

I implemented the visitor with the QueryParser class, inheriting from TSqlConcreteFragmentVisitor instead of TSqlFragmentVisitor. It's best to use the concrete visitor as the base class unless you need to visit fragments as abstract base types.

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

namespace ConsoleApplication8
{
    public class QueryParser : TSqlConcreteFragmentVisitor
    {

        //column identifiers keyed by FirstTokenIndex
        private Dictionary<int, Identifier> columnIdentifiers = new Dictionary<int, Identifier>();
        private TSqlFragment tsqlScriptFragment;

        public string Parse(string sqlSelect)
        {

            var parser = new TSql160Parser(false);
            var rd = new StringReader(sqlSelect);
            IList<ParseError> errors;
            tsqlScriptFragment = parser.Parse(rd, out errors);
            if(errors.Count > 0)
            {
                throw new ArgumentException($"Error(s) parsing SQL script. {errors.Count} errors found.");
            }

            tsqlScriptFragment.Accept(this);

            return getTransformedSqlScript();

        }

        //this is not used in this example but retained if you need it for other use cases        
        private string getNodeTokenText(TSqlFragment fragment)
        {
            StringBuilder tokenText = new StringBuilder();
            for (int counter = fragment.FirstTokenIndex; counter <= fragment.LastTokenIndex; counter++)
            {
                tokenText.Append(fragment.ScriptTokenStream[counter].Text);
            }

            return tokenText.ToString();
        }

        //add identifiers in ColumnReferenceExpression to dictionary upon visit
        public override void ExplicitVisit(ColumnReferenceExpression node)
        {

            foreach(var identifier in node.MultiPartIdentifier.Identifiers)
            {
                this.columnIdentifiers.Add(identifier.FirstTokenIndex, identifier);
            }

        }

        private string getTransformedSqlScript()
        {

            var transformedScript = new StringBuilder();

            for (int i = 0; i < tsqlScriptFragment.ScriptTokenStream.Count; ++i)
            {

                if (columnIdentifiers.ContainsKey(i))
                {
                    //replace square braket enclosures with single quotes, if needed
                    var columnIdentifier = columnIdentifiers[i];
                    var newcolumnIdentifier = columnIdentifier.QuoteType == QuoteType.SquareBracket ? $"'{columnIdentifier.Value}'" : columnIdentifier.Value;
                    transformedScript.Append(newcolumnIdentifier);
                }
                else
                {
                    //keep original script text
                    transformedScript.Append(tsqlScriptFragment.ScriptTokenStream[i].Text);
                }

            }

            return transformedScript.ToString();
        }

    }

    public class Program
    {
        private static void Main(string[] args)
        {

            var queryParser = new QueryParser();
            var transformedScript = queryParser.Parse("SELECT A,[B],T.C,T.[D],E FROM T  WHERE isnumeric(col3) = 1 Order by [Id] desc");

            Console.WriteLine(transformedScript);

        }
    }
}