Query validation using C#

Question

I am looking for a query validator in C#, which allows me to parse the SQL text from a textbox and verify whether it's correct or not before sending it for execution (MS SQL or DB2 queries).

MS SQL - is the best query validator. Send the query to it and it will throw exception is the query is wrong. :) Also, the same query can be correct for one DB engine and incorrect for the other one. Why not to let DB engine validate? — Alex Aza
– Alex Aza, Commented Jun 9, 2011 at 3:40
ExecuteSQL on MS SQL which would throw a Exception on invalid query ,but potential harm lay wherein these Queries would be fired where as my only intention is to test the Syntax. And what in case of DB2 ? — Cannon
– Cannon, Commented Jun 9, 2011 at 3:43
what overhead? DB query validation is one of the very first steps in the query workflow. It will not just validate the syntax, but also check if table or columns exist and all kinds of things like this. — Alex Aza
– Alex Aza, Commented Jun 9, 2011 at 3:45

Community · Accepted Answer · 2017-05-23 11:46:01Z

38

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

edited May 23, 2017 at 11:46

CommunityBot

11 silver badge

answered Jun 9, 2011 at 3:50

Matthew Cox

13.7k9 gold badges57 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Cannon Over a year ago

Am I missing something ? I am not able to get references for Microsoft.Data. I am using VS2010

Matthew Cox Over a year ago

@Kunal You have to right click on references and add this reference to your project before you can do a using statement to include it.

Cannon Over a year ago

well I have added both references Microsoft.Data.Schema.ScriptDom and Microsoft.Data.Schema.ScriptDom.Sql, but in using statement it shows error on "Data"and hence not able to get TSql100Parser class.

Cannon Over a year ago

Any such class to validate DB2 Queries ?

Sam Over a year ago

TSql100Parser is part of Microsoft.SqlServer.TransactSql.ScriptDom.dll IScriptFragment with newer VSBuilds is now TSqlFragment

|

BlueCacti · Accepted Answer · 2018-09-12 07:32:06Z

16

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'.

edited Sep 12, 2018 at 7:32

BlueCacti

11.1k3 gold badges23 silver badges26 bronze badges

answered Jun 9, 2011 at 13:20

DeveloperX

4,69319 silver badges22 bronze badges

1 Comment

Cannon Over a year ago

Well. But this one will make a DB call, parse it on Client end and validate SQL text. I want to avoid DB Calls. Also, any such option for DB2 SQLs ?

Sergiu · Accepted Answer · 2014-03-10 13:51:45Z

0

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!

answered Mar 10, 2014 at 13:51

Sergiu

4427 silver badges18 bronze badges

1 Comment

Sam Over a year ago

this requires you to run the query if its successful. OP is asking for to "parse the SQL Text from textbox and verify wether its correct or not before sending it for execution" - I understand the next logical step IS to run it but your method is not what OP is asking

Jayantha Lal Sirisena · Accepted Answer · 2011-06-09 03:41:20Z

-1

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

answered Jun 9, 2011 at 3:41

Jayantha Lal Sirisena

21.4k11 gold badges74 silver badges94 bronze badges

2 Comments

Samuel Neff Over a year ago

This is not easily usable from c#

Cannon Over a year ago

Cool. But is there anything useful for DB2 Queries ?

Collectives™ on Stack Overflow

Query validation using C#

4 Answers 4

9 Comments

1 Comment

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

9 Comments

1 Comment

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related