Query Validation using c#

问题:

I am looking for a query validator in c#, which allows me to parse the SQL Text from textbox and verify wether its correct or not before sending it for execution. (MS SQL or DB2 queries).

回答1:

If you want to validate SQL syntax without the use of a database, the TSql100Parser class will do well for this situation.

Disclaimer, code borrowed from this post here Code to validate SQL Scripts

Pretty straightforward to use though. If it returns null, then there were no errors in parsing it.

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

public class SqlParser
{
        public List<string> Parse(string sql)
        {
            TSql100Parser parser = new TSql100Parser(false);
            IScriptFragment fragment;
            IList<ParseError> errors;
            fragment = parser.Parse(new StringReader(sql), out errors);
            if (errors != null && errors.Count > 0)
            {
                List<string> errorList = new List<string>();
                foreach (var error in errors)
                {
                    errorList.Add(error.Message);
                }
                return errorList;
            }
            return null;
        }
}

回答2:

Set your query to sql with this hint:

set PARSEONLY  on

It just checks your query and returns, like this:

set PARSEONLY  on
select * from tablea

Returns no exception.

While

set PARSEONLY  on
select * f rom tablea

returns

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'f'.

回答3:

If you would like to validate/parse just a SELECT statement, regardless of how "heavy-duty" that select statement is, I found out that the best and fastest way to validate a select statement is the following: - in your code create 2 select statements (strings) such as:

1) Your valid select statement: SELECT * FROM HUGE_TABLE JOIN MULTIPLE_TABLES WHERE <<Condition>> 2) Create a similar select statement such as SELECT TOP 1 * FROM HUGE_TABLE JOIN MULTIPLE_TABLES WHERE <<Condition>> - Parse/Validate just the second one, regardless of how many joins you have in there, it will parse it in milliseconds, such as:

SqlCommand sqlParse = new SqlCommand(ParseSelectStatement, sqlConn); 

try 
{
sqlConn.Open();
sqlParse.ExecuteNonQuery()
}

Hope it helps! Cheers!

回答4:

I think this is what you are looking for. http://www.codeproject.com/KB/database/sqlvalidator.aspx