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

As you said: "I don't want to run the query."

Microsoft.Data.Schema.ScriptDom is a good choice if you only need to validate SQL syntax of Microsoft SQL Server. Otherwise, you may consider other SQL Parser such as General SQL Parser(support Oracle, SQL Server, DB2, MySQL, Teradata, PostgreSQL) help you to do offline SQL syntax check in your program.

Answer 2

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 3

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.

Answer 4

This can be done by analyzing your query with help of formal grammar but this might be an overload for you and totally depends on your usage scenario.

For analyzing any formal languages I advise you to use Irony library which is very popular both in production and individual projects and already has a built-in SQL grammar, so it would be just a matter of invoking that initializer. It would give you info about errors and their type but for something simpler use Ira's proposal.

Answer 5

Based on the fact that you use SqlCommand, I assume you want to check correctness of the statement against Sql Server. As folks pointed out, Prepare statement is the one to go with. On the c# side you can only check the correctness of the syntax, and nothing else (that does not mean the query will pass). Prepare statement will check naming resolution, binding and more, but it will consist of a round trip to the server. Moreover, you have to be aware of its limitations (ex. temporary objects). For detail on TSQL Prepare statement take a look here.