Код для проверки SQL-скриптов

Ответ 1

Если вы создаете инструмент, который позволяет пользователю вводить код sql вручную, и вы хотите проверить код, введенный с использованием кода С#, перед выполнением на сервере sql, вы можете создать такой метод:

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

Начиная с 2018 года и новых версий баз данных, это может быть более новая версия:

using Microsoft.SqlServer.TransactSql.ScriptDom;

(скачать с помощью 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;
}

Ответ 2

У SSMS есть способ сделать это.

Если вы используете SQL Profiler, вы увидите, что он выполняет SET PARSEONLY ON, тогда SQL, а затем SET PARSEONLY OFF и любые ошибки будут вставлены без компиляции или выполнения запроса.

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

PARSEONLY

Я никогда не пробовал это из С#, но я не вижу причин, почему он не должен работать, он работает от SSMS в конце концов.

Как указывает Мартин Смит в комментариях, вы можете использовать SET NOEXEC ON

MSDN сообщает следующее об обеих командах.

Когда SET NOEXEC включен, SQL Server компилирует каждую партию Transact-SQL но не выполняет их. Когда SET NOEXEC выключен, все партии выполняются после компиляции.

Когда SET PARSEONLY включен, SQL Server анализирует только оператор. когда SET PARSEONLY выключен, SQL Server компилирует и выполняет инструкцию.

Это означает, что NOEXEC также скомпилирует запрос, где PARSEONLY не будет. Таким образом, NOEXEC может ловить ошибки, которые PARSEONLY нет. Использование одинаковое.

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

NOEXEC

Ответ 3

Я знаю, что вопрос был о .NET 2.0, но он может быть интересен для кого-то. Проверка последних запросов немного изменилась в последних версиях Microsoft SQL Server. Пространство имен Microsoft.SqlServer.TransactSql.ScriptDom вместо Microsoft.Data.Schema.ScriptDom.

Где найти эту библиотеку?

Путь к библиотеке %programfiles(x86)%\Microsoft SQL Server\120\SDK\Assemblies Если вы не можете найти эту библиотеку и Microsoft SQL Server установлен, попробуйте изменить с 120 на 110 или 100 и использовать соответствующий синтаксический анализатор (TSql110Parser или TSql100Parser соответственно).

Как использовать?

У меня есть два расширения: первое расширение проверяет, является ли входная строка допустимым SQL-запросом, а вторая может использоваться для получения ошибок при разборе.

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

Дополнительно, я проверяю, что входной SQL-запрос не является нулевым или пустым, потому что парсер считает, что пустая строка является абсолютно допустимой (и я не сужу ее).

Как проверить?

Есть три теста NUnit, которые показывают, как вы можете использовать эти расширения.

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

Ответ 4

Что означает "действительный" SQL? Синтаксис или результаты?

Единственным верным способом проверки синтаксиса является выполнение SQL в SQL Server. Считаете ли вы, что вы используете SQL в транзакции, а затем выполните откат в конце?

Begin Transaction

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

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

...

Rollback

Документация MSDN при откате