将T-SQL语句解析为令牌 [英] Parsing T-SQL statements to tokens
问题描述
您可以在此处看到C#
代码语句的效果如何解析为令牌.例如,以下代码:
You can see here how nicely C#
code statement is parse to tokens. For example, the following code:
namespace MyNamespace
{
class MyClass
{
public void MyFunction(int arg1)
{
int var1 = arg1;
}
}
}
对此进行了解析:
我想做这样的事情,但是要用T-SQL
语句.例如,如果我有以下T-SQL
语句:
I want to do something like this but with T-SQL
statement instead. For example, if I have the following T-SQL
statement:
IIF(COALESCE([Col001], [Col002], [Col003]) > [Col004], [Col005] * [Col006] + ISNULL([Col007], [Col008]), CONCAT(SUBSTRING([Col009], 0, 3), 'sample text', [Col010]))
会给我这样的东西:
IIF, COALESCE, ISNULL, CONCAT, SUBSTRING - functions
[Col001], [Col002], ... , [Col010] - columns
0, 3, 'sample text' - variables
或者如果我有:
ISNULL([Col001], [Col002], [Col003])
结构错误:
[The isnull function requires 2 argument(s).] - error
没有任何免费或付费的最新解决方案,并且似乎使用Microsoft解析器是此处的最佳解决方案.如我所读,我需要使用 Microsoft. SqlServer.Management.SqlParser.Parser 命名空间,但是没有任何示例,我无法按照自己喜欢的方式拆分T-SQL
语句.另外,它似乎仅适用于完整的语句(例如,您需要SELECT
子句,而我仅需要将其用于代码片段).
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).
我可以使用此名称空间来执行此操作吗?还是开始为自己的需要编写C#
类?
Can I do this using this namespace or it's better to start writing C#
class for my needs instead?
推荐答案
我必须在csproj中手动添加引用
I had to add the reference manually in the csproj
Microsoft.SqlServer.Management.SqlParser,版本= 12.0.0.0,区域性=中性,PublicKeyToken = 89845dcd8080cc91
Microsoft.SqlServer.Management.SqlParser, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
喜欢
<Reference Include="Microsoft.SqlServer.Management.SqlParser, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />
一个简单的例子:
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);
}
请注意,此解析器可识别一定数量的功能(如IIF
,COALESCE
,...).无法识别的功能仅标记为TOKEN_ID
,例如列名.
Note that this parser recognizes a certain number of functions (like IIF
, COALESCE
, ...). Unrecognized functions are simply marked as TOKEN_ID
, like column names.
这篇关于将T-SQL语句解析为令牌的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!