ServerConnection.ExecuteNonQuery в режиме SQLCMD

Я использую среду приложения Microsoft Data-Tier для создания сценария развертывания на основе объекта DacPackage. Я пытаюсь использовать класс http://msdn.microsoft.com/en-us/library/Microsoft.SqlServer.Management.Smo.Server(v=sql.110).aspx для выполнения этого сценария...

SqlConnection deployConnection = new SqlConnection(connBuilder.ToString());
deployConnection.Open();
Server server = new Server(new ServerConnection(deployConnection));
server.ConnectionContext.ExecuteNonQuery(deployScript);

Тем не менее, это ошибки с...

Unhandled Exception: Microsoft.SqlServer.Management.Common.ExecutionFailureException:
  An exception occurred while executing a Transact-SQL statement or batch. --->
  System.Data.SqlClient.SqlException: Incorrect syntax near ':'.

Я знаю, что ответом на эту проблему является то, что мне нужно быть в режиме SQLCMD, но я не знаю, как настроить соединение ServerConnection в указанном режиме.

Я думаю, что моя проблема не так конкретна, как то, что я заявляю в заголовке. Что мне действительно нужно, так это выполнить скрипт, сгенерированный из DacPackage через.Net framework. Кто-нибудь может мне с этим помочь?

Источник

2 ответа

Решение

Команды режима SQLCMD не являются командами T-SQL; они работают только в SQL Server Management Studio (SSMS) / Visual Studio (VS) и SQLCMD.EXE. SQLCMD-режим по своей сути является тем, как работает SQLCMD.EXE, и его можно включить вручную в SSMS / VS; это часть этих приложений, а не то, что можно сделать через провайдера.

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

Следовательно, сценарии развертывания SQL, созданные средствами SQL Server Data Tools (SSDT) ​​/ Visual Studio, необходимо запускать с помощью одной из этих трех программ.

Так как у вас есть .dacpac Microsoft уже предлагает несколько способов опубликовать те, которые вы должны проверить:

Вы также можете создать сценарий публикации SQL с помощью DacServices.GenerateDeployScript (), но это не изменит ситуацию, как указано выше, начиная со сценария публикации / развертывания, независимо от того, сгенерирован ли он из Visual Studio "Publish {project_name}" или GenerateDeployScript(), это тот же сценарий. Это означает, что он будет иметь команды двоеточия режима SQLCMD, такие как :setvar а также :on error exit а также переменные режима SQLCMD, которые по крайней мере будут $(DatabaseName) который используется в следующей строке:

USE [$(DatabaseName)];

Пока можно закомментировать начальный :setvar строк, устанавливая свойство DacDeployOptions для CommentOutSetVarDeclarations в true, что все равно покинет :on error exit линия, а также линия для :setvar __IsSqlCmdEnabled "True" это используется, чтобы определить, был ли включен режим SQLCMD. Прямо над этим :setvar строка является комментарием, заявляющим:

/*
Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
To re-enable the script after enabling SQLCMD mode, execute the following:
SET NOEXEC OFF; 
*/

Поэтому они действительно предполагают, что этот сценарий запускается только через SQLCMD, будь то через DOS -> SQLCMD.EXE или PowerShell -> Invoke-SqlCMD.

Технически возможно генерировать строку содержимого сценария развертывания (а не stream) и манипулировать этой строкой путем а) ​​удаления любых команд двоеточия и б) замены "$(DatabaseName)" любой базой данных, которую вы собираетесь развернуть. Тем не менее, я не пробовал это, я не рекомендую это, и я не уверен, что это будет работать во всех ситуациях того, какие сценарии развертывания могут быть созданы средствами данных SQL Server. Но это похоже на вариант.

Кроме того, незначительно связано: вам не нужно SMO для запуска сценариев SQL. SMO - это средство взаимодействия с SQL Server через объекты, а не напрямую через команды T-SQL.

РЕДАКТИРОВАТЬ:
Ссылки, где другие попробовали это и обнаружили, что это не работает:

Возможности заставить сгенерированный публикуемый SQL-скрипт работать программно:

Источник

Я решал задачу по программному выполнению скрипта, сгенерированного инструментом SqlPackage , для настройки базы данных для интеграционных тестов. После тщательного исследования я до сих пор не нашел ни одной стандартной библиотеки .NET для анализа или выполнения запросов в режиме SQLCMD. Поэтому я рассмотрел следующие варианты:

  1. Вызовsqlcmd.exeинструмент командной строки.
  2. ВызовInvoke-SqlCmdКоманда PowerShell.
  3. Используйте одну из сторонних библиотек для запуска сценария режима SQLCMD.

После более глубокого рассмотрения и экспериментов ни один из вариантов меня не удовлетворил. С другой стороны, я заметил, что, вероятно, мне не требуется поддержка всех языковых возможностей языка SQLCMD. Точнее, мне нужна только подстановка переменных и игнорирование некоторых директив. Поэтому я решил реализовать свой собственный синтаксический анализатор ограниченного режима SQLCMD на основе регулярных выражений:

      internal static class SqlCmdScriptParser
{
    private static readonly Command[] ControlCommands =
    {
        new()
        {
            Id = "setvar",
            Pattern = new(@":(?<command>setvar)\s+(?<name>\w+)\s+""(?<value>.*)"""),
            IsFullLine = true,
            MatchSubstitution =
                (match, variables) =>
                {
                    variables.Add(match.Groups["name"].Value, match.Groups["value"].Value);
                    return string.Empty;
                },
        },
        new()
        {
            Id = "on error",
            Pattern = new(@":(?<command>on error)\s+(?<value>exit|ignore)"),
            IsFullLine = true,
            MatchSubstitution = (_, _) => string.Empty,
        },
        new()
        {
            Id = "$",
            Pattern = new(@"(?<command>\$)\((?<name>\w+)\)"),
            IsFullLine = false,
            MatchSubstitution =
                (match, variables) =>
                    variables.GetValueOrDefault(match.Groups["name"].Value) ?? string.Empty,
        },
    };

    private static readonly IReadOnlyDictionary<string, Command> ControlCommandsMap =
        ControlCommands.ToDictionary(c => c.Id, StringComparer.OrdinalIgnoreCase);

    private static readonly Regex ControlCommandsPattern = GetControlCommandsPattern();

    private static readonly Regex BatchSeparatorPattern = new Regex("GO").ToFullLine();

    public static IReadOnlyCollection<string> Parse(
        string input, IReadOnlyDictionary<string, string>? variables = null) =>
        input
            .SubstituteControlCommands(variables)
            .SplitBatch();

    private static Regex GetControlCommandsPattern()
    {
        var patterns = ControlCommands
            .Select(c => c.IsFullLine ? c.Pattern.ToFullLine() : c.Pattern)
            .Select(p => $"({p})")
            .ToList();
        var combinedPattern = string.Join("|", patterns);
        return new Regex(combinedPattern, RegexOptions.Multiline | RegexOptions.Compiled);
    }

    private static Regex ToFullLine(this Regex source) =>
        new($@"^\s*{source}\s*$\n?", RegexOptions.Multiline | RegexOptions.Compiled);

    private static string SubstituteControlCommands(
        this string input, IReadOnlyDictionary<string, string>? variables)
    {
        var establishedVariables = new Dictionary<string, string>(
            variables ?? new Dictionary<string, string>(), StringComparer.OrdinalIgnoreCase);
        return ControlCommandsPattern
            .Replace(input, match => SubstituteControlCommandMatch(match, establishedVariables));
    }

    private static string SubstituteControlCommandMatch(
        Match match, Dictionary<string, string> variables)
    {
        var commandId = match.Groups["command"].Value;
        var command = ControlCommandsMap.GetValueOrDefault(commandId)
            ?? throw new InvalidOperationException($"Unknown command: {commandId}");
        return command.MatchSubstitution(match, variables);
    }

    private static IReadOnlyCollection<string> SplitBatch(this string input) =>
        BatchSeparatorPattern.Split(input)
            .Where(s => !string.IsNullOrEmpty(s))
            .ToList();

    private sealed class Command
    {
        public string Id { get; init; } = string.Empty;

        public Regex Pattern { get; init; } = new(string.Empty);

        public bool IsFullLine { get; init; }

        public Func<Match, Dictionary<string, string>, string> MatchSubstitution { get; init; } =
            (_, _) => string.Empty;
    }
}

Использование:

      var inputScript = '-- Set your input SQLCMD mode script here';

// Set your variables here
var variables = new Dictionary<string, string>();

var batches = SqlCmdScriptParser.Parse(inputScript, variables);

На выходе он генерирует коллекцию команд SQL, готовых к выполнению.

Эта реализация на данный момент ограничена, но ее легко расширить, поскольку поддерживаемые команды можно добавлять декларативно. В случае, если команды должны быть точно реализованы (а не просто проигнорированы), можно ввести некоторое состояние синтаксического анализатора, что может привести к настройкам формы, связанным с каждой сгенерированной командой SQL. Эти настройки можно использовать во время выполнения сгенерированных команд SQL.

Пример в .NET Fiddle

Источник
Другие вопросы по тегам