ServerConnection.ExecuteNonQuery in SQLCMD Mode

本文关键字：Mode SQLCMD in ExecuteNonQuery ServerConnection | 更新日期: 2023-09-27 18:28:14

我正在使用Microsoft数据层应用程序框架来创建基于DacPackage对象的部署脚本。我正在尝试使用 Microsoft.SqlServer.Management.Smo.Server 类来执行此脚本...

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模式，但我不知道如何告诉我的服务器连接在所述模式下执行。

我想我的问题并不像我在标题中所说的那么具体。我真正需要做的是通过.Net框架执行从DacPackage生成的脚本。谁能帮我解决这个问题？

ServerConnection.ExecuteNonQuery in SQLCMD Mode

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 Server Data Tools (SSDT(/Visual Studio生成的部署SQL脚本需要通过这三个程序之一运行。

由于您已经有一个.dacpac文件，Microsoft提供了几种发布您应该签出的文件的方法：

  • SqlPackage.exe和MSDeploy.exe .它们都在使用命令行工具进行面向项目的数据库开发的 MSDN 页面上进行了介绍。
  • DacServices.Deploy() .这可以通过 DacServices 类在 C# 中完成。

您也可以通过DacServices.GenerateDeployScript((创建发布SQL脚本，但这不会改变上述情况，因为发布/部署SQL脚本，无论是从Visual Studio"Publish {project_name}"还是GenerateDeployScript()生成，都是相同的脚本。这意味着，它将具有 SQLCMD 模式冒号命令，例如 :setvar 和 :on error exit 以及 SQLCMD 模式变量，这些变量至少将$(DatabaseName)在以下行中使用：

USE [$(DatabaseName)];

虽然可以通过将 CommentOutSetVarDeclarations 的 DacDeployOptions 属性设置为 true 来注释掉初始:setvar行，但这仍将保留:on error exit行以及用于检测是否已启用 SQLCMD 模式的:setvar __IsSqlCmdEnabled "True"行。在这个特定的: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(，并通过a(删除任何冒号命令和b(将"$(DatabaseName("替换为要部署到的任何数据库来操作该字符串。但是，我没有尝试过这个，我不建议这样做，我不确定它是否适用于SQL Server数据工具可以生成的部署脚本的所有情况。但这似乎确实是一种选择。

此外，次要相关：您不需要 SMO 来运行 SQL 脚本。SMO 是通过对象而不是直接通过 T-SQL 命令与 SQL Server 交互的方法。

编辑：
其他人尝试过并发现它不起作用的链接：

  • http://www.sqlservercentral.com/Forums/Topic1161673-22-1.aspx
  • https://social.msdn.microsoft.com/Forums/sqlserver/en-US/7d3f64b0-f6ed-44ad-99cc-ce4c8324c09e/running-sqlcmd-commands-using-smodatabaseexecutenonquery?forum=sqlsmoanddmo

使生成的发布 SQL 脚本以编程方式工作的可能性：

  • C# 通过 Process.Start 调用 SQLCMD.EXE -i filename.sql ： http://msdn.microsoft.com/en-us/library/system.diagnostics.process.start(v=vs.110(.aspx
  • C#通过开源库处理"GO"语句和SQLCMD模式冒号命令(目前处理SQL文件，但可以轻松更新以接受字符串(：https://github.com/rusanu/DbUtilSqlCmd
  • PowerShell via Invoke-SqlCMD： http://www.sqlservercentral.com/Forums/Topic1502697-1351-1.aspx

我正在解决执行由 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 小提琴中的示例

相关文章：