SQL Server 如何使用ScriptDom和更改的列编写新的T-SQL脚本？

8xiog9wr 于 2023-01-20 发布在其他

关注(0)|答案(1)|浏览(139)

我想建立T-SQL解析器，将建立新的查询不同的DBMS与特定的规则。第一个规则，要实现的是添加到每个列名的开始和结束的列名'。
我从下面的链接SQL, all column names are in brackets, C#获得代码
所用代码：

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);
            }
        }
    }
}

但是我不知道如何构建一个新脚本，新老脚本之间的唯一区别是列名为'。
下面SQL的结果是：

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

应该是

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

sql-server

来源：https://stackoverflow.com/questions/75158616/how-can-i-write-new-t-sql-script-with-scriptdom-and-changed-columns

1条答案

按热度按时间

ux6nzvsh1#

下面是代码的重构版本，它将列标识符引用中的方括号替换为单引号，并返回转换后的脚本。这也将替换多部分标识符中的括号。
我用QueryParser类实现了访问器，继承自TSqlConcreteFragmentVisitor而不是TSqlFragmentVisitor，最好使用具体的访问器作为基类，除非你需要将片段作为抽象基类来访问。

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);

        }
    }
}

赞(0）回复(0）举报 2023-01-20

我来回答

SQL Server 如何使用ScriptDom和更改的列编写新的T-SQL脚本？

1条答案

相关问题

热门标签

最新问答