在 C# 中解析 SQL 代码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/589096/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Parsing SQL code in C#
提问by Archie
I want to parse SQL code using C#.
我想使用 C# 解析 SQL 代码。
Specifically, is there any freely available parser which can parse SQL code and generate a tree or any other structure out of it? It should also generate the proper tree for nested structures.
具体来说，是否有任何免费的解析器可以解析 SQL 代码并从中生成树或任何其他结构？它还应该为嵌套结构生成合适的树。
It should also return which kind of statement the node of this tree represents.
它还应该返回这棵树的节点代表哪种语句。
For example, if the node contains a loop condition then it should return that this is a "loop type" of a node.
例如，如果节点包含循环条件，那么它应该返回这是节点的“循环类型”。
Or is there any way by which I can parse the code in C# and generate a tree of the type I want?
或者有什么方法可以解析 C# 中的代码并生成我想要的类型的树？
采纳答案by TFD
Use Microsoft Entity Framework(EF).
使用 Microsoft实体框架(EF)。
It has a "Entity SQL" parser which builds an expression tree,
它有一个“Entity SQL”解析器，可以构建一个表达式树，
using System.Data.EntityClient;
...
EntityConnection conn = new EntityConnection(myContext.Connection.ConnectionString);
conn.Open();
EntityCommand cmd = conn.CreateCommand();
cmd.CommandText = @"Select t.MyValue From MyEntities.MyTable As t";
var queryExpression = cmd.Expression;
....
conn.Close();
Or something like that, check it out on MSDN.
或类似的东西，请在 MSDN 上查看。
And it's all on Ballmers tick :-)
这一切都在鲍尔默斯的勾号上:-)
There is also one on The Code Project, SQL Parser.
The Code Project 上也有一个SQL Parser。
Good luck.
祝你好运。
回答by Diego Jancic
Try GOLD Parser, it's a powerful and easy to learn BNF engine. You can search the grammars already made for what you want (ie: SQL ANSI 89 Grammar).
试试GOLD Parser，它是一个功能强大且易于学习的 BNF 引擎。你可以搜索你想要的已经制作的语法（即：SQL ANSI 89 Grammar）。
I started using this for HQL parsing (the NHibernate query language, very similar to SQL), and it's awesome.
我开始使用它进行 HQL 解析（NHibernate 查询语言，与 SQL 非常相似），它很棒。
UPDATE: Now the NH dev team has done the HQL parsing using ANTLR (which is harder to use, but more powerful AFAIK).
更新：现在 NH 开发团队已经使用 ANTLR（更难使用，但更强大的 AFAIK）完成了 HQL 解析。
回答by Mehmet Aras
VSTS 2008 Database Edition GDR includes assemblies that handle SQL parsing and script generation that you can reference from your project. Database Edition uses the parser to parse the script files to represent in-memory model of your database and then uses the script generator to generate SQL scripts from the model. I think there are just two assemblies you need to have and reference in your project. If you don't have the database edition, you may install the trial version to get the assemblies or there might be another way to have them without installing the database edition. Check out the following link. Data Dude:Getting to the Crown Jewels .
VSTS 2008 Database Edition GDR 包括处理 SQL 解析和脚本生成的程序集，您可以从项目中引用这些程序集。数据库版使用解析器解析脚本文件来表示数据库的内存模型，然后使用脚本生成器从模型生成 SQL 脚本。我认为您只需要拥有两个程序集并在您的项目中引用。如果您没有数据库版本，您可以安装试用版来获取程序集，或者可能有另一种方法可以在不安装数据库版本的情况下获得它们。查看以下链接。 数据老兄：获得皇冠上的珠宝。
回答by James
You may take a look at a commerical component: general sql parser at http://www.sqlparser.comIt supports SQL syntax of Oracle, T-SQL, DB2 and MySQL.
你可以看看一个商业组件：http://www.sqlparser.com 上的general sql parser 它支持Oracle、T-SQL、DB2 和MySQL 的SQL 语法。
回答by Rbjz
As Diego suggested, grammars are the way to go IMHO. I've tried Coco/r before, but that is too simple for complex SQL. There's ANTLRwith a number of grammarsready.
正如迭戈所建议的那样，恕我直言，语法是一种方法。我以前尝试过 Coco/r，但这对于复杂的 SQL 来说太简单了。有ANTLR有一些语法的准备。
Someone even tried to build a SQL engine, check the code if there's something for you in SharpHSQL - An SQL engine written in C#.
有人甚至试图构建一个 SQL 引擎，如果在SharpHSQL - 用 C# 编写的 SQL 引擎中找到适合你的东西，请检查代码。
回答by user423430
Scott Hanselman recently featuredthe Irony projectwhich includes a sample SQL parser.
回答by Andrey Belykh
Specifically for Transact-SQL (Microsoft SQL Server) you can use the Microsoft.SqlServer.Management.SqlParser.Parsernamespaceavailable in Microsoft.SqlServer.Management.SqlParser.dll, an assembly included with SQL Server and which can be freely distributed.
特别是对于 Transact-SQL (Microsoft SQL Server)，您可以使用Microsoft.SqlServer.Management.SqlParser.dll 中可用的Microsoft.SqlServer.Management.SqlParser.Parser命名空间，这是 SQL Server 附带的程序集，可以自由分发。
Here's an example method for parsing T-SQL as a string into a sequence of tokens:
这是将 T-SQL 作为字符串解析为标记序列的示例方法：
IEnumerable<TokenInfo> ParseSql(string sql)
{
ParseOptions parseOptions = new ParseOptions();
Scanner scanner = new Scanner(parseOptions);
int state = 0,
start,
end,
lastTokenEnd = -1,
token;
bool isPairMatch, isExecAutoParamHelp;
List<TokenInfo> tokens = new List<TokenInfo>();
scanner.SetSource(sql, 0);
while ((token = scanner.GetNext(ref state, out start, out end, out isPairMatch, out isExecAutoParamHelp)) != (int)Tokens.EOF)
{
TokenInfo tokenInfo =
new TokenInfo()
{
Start = start,
End = end,
IsPairMatch = isPairMatch,
IsExecAutoParamHelp = isExecAutoParamHelp,
Sql = sql.Substring(start, end - start + 1),
Token = (Tokens)token,
};
tokens.Add(tokenInfo);
lastTokenEnd = end;
}
return tokens;
}
Note that the TokenInfoclass is just a simple class with the above-referenced properties.
请注意，TokenInfo该类只是一个具有上述引用属性的简单类。
Tokensis this enumeration:
Tokens这是枚举：
and includes constants like TOKEN_BEGIN, TOKEN_COMMIT, TOKEN_EXISTS, etc.
并包括像常数TOKEN_BEGIN，TOKEN_COMMIT，TOKEN_EXISTS等。
