Parsing a SQL string in c#

Question

I have the need to Parse a Command.CommandText.

I don't want to run the query. I only want to see if the query will succeed if the command was executed.

Say i have; "SELECT * FROM SomeTable WHERE (1=1)"

This string will succeed.

but,

"SELECT * FROM SomeTable WHERE (1=1"

will not succeed.

Now my question. How would i Parse this string c#?

Answer 1

If you just want to validate the syntax. You can use Microsoft.Data.Schema.ScriptDom for this.

using Microsoft.Data.Schema.ScriptDom;
using Microsoft.Data.Schema.ScriptDom.Sql;

.....

        string sql = "SELECT * FROM SomeTable WHERE (1=1";
        var p = new TSql100Parser(true);
        IList<ParseError> errors;

        p.Parse(new StringReader(sql), out errors);


        if (errors.Count == 0)
            Console.Write("No Errors");
        else
            foreach (ParseError parseError in errors)
                Console.Write(parseError.Message);

Answer 2

I've tried a handful of libraries that are designed to parse/manipulate/generate SQL in code. The most recent was ActiveQueryBuilder, but I know there are many out there.

With AQB - you would be able to 'validate' SQL but the problem I think you'll run into is that it is not 100%. None of them, that I've used, provide identical results to the actual database. You will find some certain SQL string that appears valid to your parser but invalid to the database or vice-versa. For example, in the AQB you couldn't have a subquery without giving it an alias or the parser would throw an exception - but Oracle would gladly accept and run the same SQL.

Depending on the database, you should be able to ask the DATABASE to validate the SQL without running it. In SQL Server, I believe you can use the Prepare statement, in Oracle I believe it's called an Explain Plan.

That's the only way I've found to get consistent results. Of course, if your queries are expected to be simple or if you don't require 100% accuracy it might be more work.