将T-SQL语句解析为标记

You can see here how nicely C#code statement is parse to tokens. For example, the following code:

你可以在这里看到C#代码语句解析为令牌的好坏。例如,以下代码:

namespace MyNamespace
{
    class MyClass
    {
        public void MyFunction(int arg1)
        {
            int var1 = arg1;
        }
    }
}

is parsed to this:

被解析为:

I want to do something like this but with T-SQL statement instead. For example, if I have the following T-SQL statement:

我想做这样的事情,但用T-SQL语句代替。例如,如果我有以下T-SQL语句:

IIF(COALESCE([Col001], [Col002], [Col003]) > [Col004], [Col005] * [Col006] + ISNULL([Col007], [Col008]), CONCAT(SUBSTRING([Col009], 0, 3), 'sample text', [Col010]))

will give me something like this:

会给我这样的东西:

IIF, COALESCE, ISNULL, CONCAT, SUBSTRING    - functions 
[Col001], [Col002], ... , [Col010]          - columns 
0, 3, 'sample text'                         - variables

or in case I have:

或者如果我有:

ISNULL([Col001], [Col002], [Col003])

structure with errors:

有错误的结构:

[The isnull function requires 2 argument(s).] - error

There are not any free or paid up-to-date solutions and it seems to use the Microsoft parser is the best solution here. As I have read I need to use the Microsoft.SqlServer.Management.SqlParser.Parser namespace, but there are not any examples and I was not able to split the T-SQL statement the way I like. Also, it seems to work only with complete statements (you need SELECT clause for example, and I need to use it for code fragments only).

没有任何免费或付费的最新解决方案,似乎使用Microsoft解析器是这里的最佳解决方案。正如我所读,我需要使用Microsoft.SqlServer.Management.SqlParser.Parser命名空间,但没有任何示例,我无法以我喜欢的方式拆分T-SQL语句。此外,它似乎只适用于完整的语句(例如,您需要SELECT子句,我只需要将它用于代码片段)。

Can I do this using this namespace or it's better to start writing C# class for my needs instead?

我可以使用此命名空间执行此操作,还是最好开始编写C#类以满足我的需求?

1 个解决方案

#1

I had to add the reference manually in the csproj

我不得不在csproj中手动添加引用

Microsoft.SqlServer.Management.SqlParser, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91

Microsoft.SqlServer.Management.SqlParser,Version = 12.0.0.0,Culture = neutral,PublicKeyToken = 89845dcd8080cc91

Like

<Reference Include="Microsoft.SqlServer.Management.SqlParser, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />

A simple example:

一个简单的例子:

string sql = "IIF(COALESCE([Col001], [Col002], [Col003]) > [Col004], [Col005] * [Col006] + ISNULL([Col007], [Col008]), CONCAT(SUBSTRING([Col009], 0, 3), 'sample text', [Col010]))";

var po = new ParseOptions { };
var scanner = new Scanner(po);
scanner.SetSource(sql, 0);

Tokens token;
int state = 0;
int start;
int end;
bool isPairMatch;
bool isExecAutoParamHelp;

while ((token = (Tokens)scanner.GetNext(ref state, out start, out end, out isPairMatch, out isExecAutoParamHelp)) != Tokens.EOF)
{
    string str = sql.Substring(start, end - start + 1);
    Console.WriteLine("{0}: {1}", token, str);
}

Taken from http://www.sqlservercentral.com/blogs/dave_ballantynes_blog/2012/03/13/parsing-t-sql-the-easy-way/

摘自http://www.sqlservercentral.com/blogs/dave_ballantynes_blog/2012/03/13/parsing-t-sql-the-easy-way/

Note that this parser recognizes a certain number of functions (like IIF, COALESCE, ...). Unrecognized functions are simply marked as TOKEN_ID, like column names.

请注意,此解析器识别一定数量的函数(如IIF,COALESCE,...)。无法识别的函数只是标记为TOKEN_ID,就像列名一样。