Code to validate SQL Scripts

Question

How can I validate sql scripts before executing them using .net 2.0 and c#?

If the sql is not valid I want to return error rows.

Answer 1

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

As of 2018 and new database versions, this might be newer version:

using Microsoft.SqlServer.TransactSql.ScriptDom;

(download with npm: PM> Install-Package Microsoft.SqlServer.TransactSql.ScriptDom -Version 14.0.3811.1 )

public bool IsSQLQueryValid(string sql, out List<string> errors)
{
    errors = new List<string>();
    TSql140Parser parser = new TSql140Parser(false);
    TSqlFragment fragment;
    IList<ParseError> parseErrors;

    using (TextReader reader = new StringReader(sql))
    {
        fragment = parser.Parse(reader, out parseErrors);
        if (parseErrors != null && parseErrors.Count > 0)
        {
            errors = parseErrors.Select(e => e.Message).ToList();
            return false;
        }
    }
    return true;
}

Answer 2

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 3

I know that the question was about .NET 2.0, but it may be interesting for someone. Validation of queries has slightly changed in the latest versions of Microsoft SQL Server. The namespace is Microsoft.SqlServer.TransactSql.ScriptDom instead of Microsoft.Data.Schema.ScriptDom.

Where to find this library?

Path to the library is %programfiles(x86)%\Microsoft SQL Server\120\SDK\Assemblies If you cannot find this library and Microsoft SQL Server is installed, try to change from 120 to 110 or 100 and use the corresponding parser (TSql110Parser or TSql100Parser respectively).

How to use?

I have two extensions: the first extension checks whether the input string is a valid SQL query and the second can be used to get errors from parsing.

using Microsoft.SqlServer.TransactSql.ScriptDom;
using System.Collections.Generic;
using System.IO;
using System.Linq;

public static class SqlStringExtensions
{
    public static bool IsValidSql(this string str)
    {
        return !str.ValidateSql().Any();
    }

    public static IEnumerable<string> ValidateSql(this string str)
    {
        if (string.IsNullOrWhiteSpace(str))
        {
            return new[] { "SQL query should be non empty." };
        }
        var parser = new TSql120Parser(false);
        IList<ParseError> errors;
        using (var reader = new StringReader(str))
        {
            parser.Parse(reader, out errors);
        }
        return errors.Select(err => err.Message);
    }
}

Additionaly, I check that the input SQL query is not null or empty, because the parser thinks that empty string is perfectly valid (and I don't judge it).

How to test?

There are three NUnit tests which show how you can use this extensions.

using System.Collections.Generic;
using System.Linq;
using NUnit.Framework;

[TestFixture]
public class SqlStringExtensionsTests
{
    [Test]
    public void ValidateSql_InvalidSql_ReturnsErrorMessages()
    {
        // this example doesn't contain "," between the field names
        string invalidSql = "SELECT /*comment*/ " +
            "CustomerID AS ID CustomerNumber FROM Customers";
        IEnumerable<string> results = invalidSql.ValidateSql();
        Assert.AreNotEqual(0, results.Count());
    }

    [Test]
    public void IsValidSql_ValidSql_ReturnsTrue()
    {
        string validSql = "SELECT /*comment*/ " +
            "CustomerID AS ID, CustomerNumber FROM Customers";
        bool result = validSql.IsValidSql();
        Assert.AreEqual(true, result);
    }

    [Test]
    public void IsValidSql_InvalidSql_ReturnsFalse()
    {
        // this example doesn't contain "," between the field names
        string invalidSql = "SELECT /*comment*/ "+
            " CustomerID AS ID CustomerNumber FROM Customers";
        bool result = invalidSql.IsValidSql();
        Assert.AreEqual(false, result);
    }
}

Answer 4

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