如何使用非常规参数调用存储过程？

I'm attempting to integrate Red Gate's SQLBackup Pro software into my in-house backup software, written in C#. The natural way to do this is via their Extended Stored Procedure. The problems is that it's called in a format I've never seen before:

我正在尝试将Red Gate的SQLBackup Pro软件集成到我用C＃编写的内部备份软件中。这样做的自然方式是通过扩展存储过程。问题在于它以我以前从未见过的格式调用：

master..sqlbackup '-SQL "BACKUP DATABASE pubs TO DISK = [C:\Backups\pubs.sqb]"'

This works just fine when run via SSMS. Where I run into trouble is trying to call it from C# (using .NET 4 and Dapper Dot Net).

通过SSMS运行时，这很好用。遇到麻烦的地方是尝试从C＃调用它（使用.NET 4和Dapper Dot Net）。

My first attempt doesn't work because it interprets the entire cmd string as the name of the stored procedure and throws the error "Cannot find stored procedure ''":

我的第一次尝试不起作用，因为它将整个cmd字符串解释为存储过程的名称，并抛出错误“找不到存储过程''”：

var cmd = "master..sqlbackup '-SQL \"BACKUP DATABASE pubs TO DISK = [C:\\Backups\\pubs.sqb]\"'";
connection.Execute(cmd, commandType: CommandType.StoredProcedure, commandTimeout: 0);

My second attempt returns immediately and appears (to C#) to succeed, but no backup is actually taken (this also sucks for parameterization):

我的第二次尝试立即返回并显示（到C＃）成功，但实际上没有进行备份（这也很难进行参数化）：

var cmd = "master..sqlbackup";
var p = new DynamicParameters();
p.Add("", "'-SQL \"BACKUP DATABASE pubs TO DISK = [C:\\Backups\\pubs.sqb]\"'");
connection.Execute(cmd, p, commandType: CommandType.StoredProcedure, commandTimeout: 0);

My third attempt also appears to succeed, but no backup is actually taken:

我的第三次尝试似乎也成功了，但实际上没有进行备份：

var cmd = "master..sqlbackup '-SQL \"BACKUP DATABASE pubs TO DISK = [C:\\Backups\\pubs.sqb]\"'";
connection.Execute(cmd, commandTimeout: 0);

What am I missing?

我错过了什么？

UPDATE 1:

更新1：

I overlooked the Red Gate documentation that says the stored proc won't actually raise a SQL error, it just returns errors in an output table. Slick. This might explain why I was getting silent failures in the second and third tests above: some underlying problem and they're not collecting the output to show why.

我忽略了Red Gate文档，该文档说存储的proc实际上不会引发SQL错误，它只是在输出表中返回错误。油滑。这可能解释了为什么我在上面的第二次和第三次测试中得到了无声的失败：一些潜在的问题，他们没有收集输出来说明原因。

Here's where I am now:

这就是我现在的位置：

var cmd = "master..sqlbackup";
var p = new DynamicParameters();
p.Add("", "'-SQL \"BACKUP DATABASE pubs TO DISK = [C:\\Backups\\pubs.sqb]\"'");
p.Add("@exitcode", DbType.Int32, direction: ParameterDirection.Output);
p.Add("@sqlerrorcode", DbType.Int32, direction: ParameterDirection.Output);
connection.Execute(cmd, p, commandType: CommandType.StoredProcedure, commandTimeout: 0);

When I run this and check those output parameters, I get Exit Code 870:

当我运行它并检查那些输出参数时，我得到退出代码870：

No command passed to SQL Backup.

没有命令传递给SQL备份。

The command is empty.

该命令为空。

So it's not seeing the empty-named paramater.

所以它没有看到空命名的参数。

Update 2:

更新2：

Capturing the above in a trace shows that the empty parameter string ends up replaced with @Parameter1= which explains why the stored procedure doesn't see it.

在跟踪中捕获上述内容表明空参数字符串最终被@ Parameter1 =替换，这解释了存储过程没有看到它的原因。

4 个解决方案

#1

Your first attempt looks almost right. What I notice is that you failed to escape the backslashes. For this kind of thing, it's often easier to use the @ prefix to disable escaping for the string. Also, you want to prepend with exec and make it CommandType.Text:

你的第一次尝试看起来几乎是对我注意到你没能逃脱反斜杠。对于这种情况，使用@前缀来禁用字符串的转义通常更容易。此外，您希望使用exec前置并使其成为CommandType.Text：

EDIT: fixed my own escaping bugs here

编辑：修复我自己的逃避错误

var cmd = @"exec 'master..sqlbackup -SQL ""BACKUP DATABASE pubs TO DISK = [C:\Backups\pubs.sqb]""'";
connection.Execute(cmd, commandType: CommandType.Text, commandTimeout: 0);

#2

It's gross and not at all what I wanted, but this is what I've got working now:

这很糟糕，根本不是我想要的，但这就是我现在所做的工作：

var cmd = String.Format(@"
DECLARE @exitcode int; 
DECLARE @sqlerrorcode int;
EXEC master..sqlbackup '-SQL \"BACKUP DATABASE [{0}] TO DISK = [{1}])\"', @exitcode OUTPUT, @sqlerrorcode OUTPUT;
IF (@exitcode >= 500) OR (@sqlerrorcode <> 0)
BEGIN
RAISERROR('SQLBackup failed with exitcode %d and sqlerrorcode %d ', 16, 1, @exitcode, @sqlerrorcode)
END
", myDbName, myBkpPath);

connection.Execute(cmd, commandTimeout: 0);

This executes the backup and actually returns failure status, which exposed an underlying issue that caused the failure part of the silent failures.

这会执行备份并实际返回失败状态，这会暴露导致失败部分静默失败的潜在问题。

Before it runs, myDbName is checked against a list of known databases to ensure it exists and myBkpPath is generated by my code, so I'm not worried about injections. It's just...well, look at that. Hideous.

在运行之前，将针对已知数据库列表检查myDbName以确保它存在并且myBkpPath由我的代码生成，因此我不担心注入。这只是......好吧，看看那个。可怕。

#3

Have you tried creating a typical stored procedure that calls the extended stored procedure, and calling that from code? It looks like you have only a few parameters to deal with here.

您是否尝试过创建一个调用扩展存储过程的典型存储过程，并从代码中调用它？看起来你在这里只需要处理几个参数。

#4

You had several problems in your tests.

你的测试中有几个问题。

In the first one you set the CommandType.StoredProcedure. You should have set it to CommandType.Text so that it would be smart enough to just pass that string along to be exec'd.

在第一个中设置CommandType.StoredProcedure。您应该将它设置为CommandType.Text，以便它足够智能，只需将该字符串传递给exec'd。

In the subsequent examples, you didn't actually give the parameter a name. Go look at their SqlBackup procedure and see what the parameter names are. Then use it. Otherwise, nothing is going to be assigned to it.

在后面的示例中，您实际上没有为参数指定名称。去看看他们的SqlBackup程序，看看参数名称是什么。然后使用它。否则，将不会分配任何内容。

#1