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, Jun 09 '11 at 03: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, Jun 09 '11 at 03: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, Jun 09 '11 at 03:45

score 35 · Accepted Answer · edited May 23 '17 at 11:46

35

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 '17 at 11:46

Community

1
1

answered Jun 09 '11 at 03:50

Matthew Cox

13,566
9
54
72

Am I missing something ? I am not able to get references for Microsoft.Data. I am using VS2010 – Cannon Jun 09 '11 at 04:21
@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. – Matthew Cox Jun 09 '11 at 04:29
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 Jun 09 '11 at 04:39
Any such class to validate DB2 Queries ? – Cannon Jun 09 '11 at 18:31
The assemblies are located under: `%ProgramFiles%\Microsoft Visual Studio 11.0\VSTSDB` – Daniel Abou Chleih Oct 04 '13 at 07:22
7

TSql100Parser is part of Microsoft.SqlServer.TransactSql.ScriptDom.dll IScriptFragment with newer VSBuilds is now TSqlFragment – Sam Mar 08 '18 at 18:20
this doesn't for Full-Text search queries – canbax Nov 23 '18 at 15:48
This validator seems to work ok for MS SQL queries, but not for DB2 queries: "SELECT * FROM schema.table WITH UR;" returns error "Incorrect syntax near UR." – Rumplin Jul 26 '19 at 10:04
This seems to be a shallow syntax check. It doesn't give an error e.g. when you put an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause, which is invalid and would give a sql exception. – chtenb Mar 29 '22 at 10:24

score 16 · Answer 2 · edited Sep 12 '18 at 07:32

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 '18 at 07:32

BlueCacti

9,729
3
20
24

answered Jun 09 '11 at 13:20

DeveloperX

4,633
17
22

1

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 ? – Cannon Aug 07 '11 at 04:47

score 0 · Answer 3 · answered Mar 10 '14 at 13:51

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!

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 — Sam, Mar 08 '18 at 18:40

score -1 · Answer 4 · answered Jun 09 '11 at 03:41

-1

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

answered Jun 09 '11 at 03:41

Jayantha Lal Sirisena

21,216
11
71
92

2

This is not easily usable from c# – Samuel Neff Jun 09 '11 at 03:44
Cool. But is there anything useful for DB2 Queries ? – Cannon Aug 07 '11 at 04:49

Query validation using C#

4 Answers4

Linked

Related