Parsing SQL Statement With Irony

Question

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

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 :

INSERT INTO Persons VALUES (4,'Nilsen', 'Johan', 'Bakken 2', 'Stavanger')

And get it converted to

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.

Faddist

I'm guessing you starting point should be getting a suitable SQL grammar and converting it to the grammar format Irony can understand. It's not easy stuff. You are up for a lot of work that is not for easily frustrated people. But it can be done. – Phillipp

I already have the SQLGrammar but the problem is parsing every Statement, insert, update, create ... then returning its content [ to be used in c# in other purposes ] – Faddist

Is your question relating more to getting Irony to parse your SQL statements, or more to performing the conversion based on the parsing results? Are you satisfied with the AST you are getting back from Irony? Could you show some of the relevant SQL grammar you are using? – Wareing

return the statement as an Action that contains the type of the statement and the values of it depending on the type An action does not contain any values - it's simply a delegate. Why are you returning an action? – Ess

Isn't that what Linq is for? You could just use the proven Linq-SQL or Entity Framework, they do exactly that. No point in reinventing the wheel... – Homologate

Answer 1

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

While I had a great time learning about Irony, I don't have the coding chops to implement all the aforementioned parts correctly.

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

Reconcilable

Answer 2

If you are not doing this as a fun exercise I would recommend using Linq to SQL to generate your stub classes or Entity Framework as Drunken Code Monkey mentioned in the comments.

Here's a good article to get you started: Generating EF code from existing DB

Shirting