Code to validate SQL Scripts

Question

How i can validate sql scripts before executing using .net2 and c#. If sql is not valid i want to return error rows.

Answer 1

What does 'valid' SQL mean? The syntax or the results?

The only sure way to validate the syntax is the execute the SQL in SQL Server. Have you considered running the SQL in a Transaction and then do a rollback at the end?

Begin Transaction

--execute your code between the 'Begin Transaction' and the 'rollback' keywords.
...

--example
Insert into mytable(ID)Values(2)

...

Rollback

MSDN Documentation on rollback

Answer 2

The following google search on stackoverfow has useful suggestions for performing the query against SQL server but providing options so that it is only validated and not actually executed:

http://www.google.co.uk/m/search?oe=UTF-8&client=safari&hl=en&aq=f&oq=&aqi=-k0d0t0&fkt=3026&fsdt=4277&q=site%3Astackoverflow.com+validate+SQL+query

Answer 3

SSMS has a way of doing this.

If you use the SQL Profiler you will see that it executes SET PARSEONLY ON, then the SQL and then SET PARSEONLY OFF and any errors are risen without compiling or executing the query.

SET PARSEONLY ON;
SELECT * FROM Table; --Query To Parse
SET PARSEONLY OFF; 

PARSEONLY

I have never tried this from c# but I see no reason why it should not work, it works from SSMS after all.

As Martin Smith points out in the comments you can use SET NOEXEC ON

MSDN says the following about both commands.

When SET NOEXEC is ON, SQL Server compiles each batch of Transact-SQL statements but does not execute them. When SET NOEXEC is OFF, all batches are executed after compilation.

When SET PARSEONLY is ON, SQL Server only parses the statement. When SET PARSEONLY is OFF, SQL Server compiles and executes the statement.

That indicates that NOEXEC will also compile the query where PARSEONLY will not. So NOEXEC may catch errors that PARSEONLY does not. The usage is the same.

SET NOEXEC ON;
SELECT * FROM Table; --Query To Parse
SET NOEXEC OFF; 

NOEXEC

Answer 4

If you are creating a tool that allows the user enter some sql code by hand and you want to validate the code entered using C# code before execution on sql server, you can create a method like this:

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