具有讽刺意味的解析SQL语句 [英] Parsing SQL Statement With Irony
问题描述
我正在尝试创建一种将常规sql语句转换为c#对象的方法,因此我决定使用Irony解析sql语句,然后将该语句作为包含该语句类型和value的Action返回.取决于类型
I am trying to create a method that converts a regular sql statement to c# objects, So i decided to use Irony to parse the sql statement then i return the statement as an Action that contains the type of the statement and the values of it depending on the type
这是我未完成的代码[因为我不知道该怎么办而感到沮丧]
Here is my non completed code [ Because i got frustrated as i don't know what to do then ]
private List<Action> ParseStatement(string statement)
{
var parser = new Parser(new SqlGrammar());
var parsed = parser.Parse(statement);
var status = parsed.Status;
while (parsed.Status == ParseTreeStatus.Parsing)
{
Task.Yield();
}
if (status == ParseTreeStatus.Error)
throw new ArgumentException("The statement cannot be parsed.");
ParseTreeNode parsedStmt = parsed.Root.ChildNodes[0];
switch (parsedStmt.Term.Name)
{
case "insertStmt":
var table = parsedStmt.ChildNodes.Find(x => x.Term.Name == "Id").ChildNodes[0].Token.ValueString;
var valuesCount =
parsedStmt.ChildNodes.Find(x => x.Term.Name == "insertData").ChildNodes.Find(
x => x.Term.Name == "exprList").ChildNodes.Count;
var values = parsedStmt.ChildNodes.Find(x => x.Term.Name == "insertData").ChildNodes.Find(
x => x.Term.Name == "exprList").ChildNodes;
foreach (var value in values)
{
string type = value.Token.Terminal.Name;
}
break;
}
return null;
}
private Type ParseType(string type)
{
switch (type)
{
case "number":
return typeof (int);
case "string":
return typeof (string);
}
return null;
}
所以这里的问题是:我该如何利用Irony将字符串SQL语句转换为c#对象?
So the Question Here is : How could i make use of Irony to convert a string SQL Statement to a c# objects ?
这是我想要实现的一个示例:
Here is an example of what i want to achieve :
插入人员值(4,"Nilsen","Johan","Bakken 2", 斯塔万格")
INSERT INTO Persons VALUES (4,'Nilsen', 'Johan', 'Bakken 2', 'Stavanger')
并将其转换为
return new Action<string type, string table, int val1, string val2, string val3, string val4, string val5>;
动态地取决于从语句中读取的方法.
Dynamically depending on what the method have read from the statement.
我希望我已经很好地解释了我的想法,以便您可以帮助我,如果有不清楚的地方,请告诉我,我会尽力解释.
I hope i have well explained my idea so you can help me guys, And if there is something unclear please tell me and i will try to explain it.
推荐答案
我也尝试使用Irony解析SQL.我之所以放弃是因为Irony中的示例SQL解析器无法处理:CTE,按列号排序,特殊语句的一半,如
I was trying to parse SQL with Irony as well. I gave up because the sample SQL parser in Irony don't handle: CTEs, Order by column number, half the special statements like
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
虽然我花了很多时间来学习Irony,但我没有编码技巧来正确实现上述所有部分.
While I had a great time learning about Irony, I don't have the coding chops to implement all the aforementioned parts correctly.
我最终使用了Microsoft提供的SQL解析库.下面的LINQPad 5的示例代码:
I ended up using the Microsoft-provided SQL parsing library. Sample code for LINQPad 5 below:
// Add a reference to
// C:\Program Files (x86)\Microsoft SQL Server\130\SDK\Assemblies\
// Microsoft.SqlServer.TransactSql.ScriptDom.dll
//
// https://blogs.msdn.microsoft.com/gertd/2008/08/21/getting-to-the-crown-jewels/
public void Main()
{
var sqlFilePath = @"C:\Users\Colin\Documents\Vonigo\database-scripts\Client\Estimate\spClient_EstimateAddNew.sql";
bool fQuotedIdenfifiers = false;
var parser = new TSql100Parser(fQuotedIdenfifiers);
string inputScript = File.ReadAllText(sqlFilePath);
IList<ParseError> errors;
using (StringReader sr = new StringReader(inputScript))
{
var fragment = parser.Parse(sr, out errors);
fragment.Dump();
}
}
这篇关于具有讽刺意味的解析SQL语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!