Out parameter with ExecuteSqlCommandAsync

Question

我正在使用实体框架的ExecuteSqlCommandAsync命令调用存储过程。这将引发一条错误消息：

程序或功能"装载充电叉车上的运输车"需要参数 "@ID_Storage"，未提供。

我的代码如下所示：

SqlParameter outIdStorageParam = new SqlParameter("@ID_Storage", SqlDbType.Int) {
Direction = ParameterDirection.Output
};
try {
await Database.ExecuteSqlCommandAsync(
"exec dbo.LoadChargeCarrierOnForklift @ID_Carrier, @ForkliftName, @User",
new SqlParameter("@ID_Carrier", id_Carrier),
new SqlParameter("@ForkliftName", forkliftName),
new SqlParameter("@User", user),
outIdStorageParam
);
} catch (System.Exception ex) {
var a = ex;
throw;
}
return (int)outIdStorageParam.Value;

但是，以下 SQL 命令有效：

USE [MyDB]
GO
DECLARE @return_value int,
@ID_Storage int
EXEC    @return_value = [dbo].[LoadChargeCarrierOnForklift]
@ID_Carrier = 1,
@ForkliftName = N'ABC',
@User = N'DEF',
@ID_Storage = @ID_Storage OUTPUT
SELECT  @ID_Storage as N'@ID_Storage'
SELECT  'Return Value' = @return_value
GO

我用 C# 代码中的参数做错了什么？

Answer 1

这个：

await Database.ExecuteSqlCommandAsync(
"exec dbo.LoadChargeCarrierOnForklift @ID_Carrier, @ForkliftName, @User",
new SqlParameter("@ID_Carrier", id_Carrier),
new SqlParameter("@ForkliftName", forkliftName),
new SqlParameter("@User", user),
outIdStorageParam

按位置绑定参数，省略ID_Storage参数。 等价

物

EXEC    @return_value = [dbo].[LoadChargeCarrierOnForklift]
@ID_Carrier = 1,
@ForkliftName = N'ABC',
@User = N'DEF',
@ID_Storage = @ID_Storage OUTPUT

按名称绑定参数，并包含两个输出参数，将是

SqlParameter outIdStorageParam = new SqlParameter("@ID_Storage", SqlDbType.Int) {
Direction = ParameterDirection.Output};
SqlParameter outReturnValue = new SqlParameter("@return_value", SqlDbType.Int) {
Direction = ParameterDirection.Output };
await Database.ExecuteSqlCommandAsync(
@"EXEC @return_value = [dbo].[LoadChargeCarrierOnForklift]
@ID_Carrier = @ID_Carrier,
@ForkliftName = @ForkliftName,
@User = @User,
@ID_Storage = @ID_Storage OUTPUT",
outReturnValue 
new SqlParameter("@ID_Carrier", id_Carrier),
new SqlParameter("@ForkliftName", forkliftName),
new SqlParameter("@User", user),
outIdStorageParam);