我正在使用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生成的脚本。谁能帮我解决这个问题?
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 模式查询。所以我考虑了以下选择:
- 调用
sqlcmd.exe
命令行工具。 - 调用
Invoke-SqlCmd
PowerShell 命令。 - 使用第三方库之一运行 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 小提琴中的示例