无法使用EF Core执行具有命名参数和输出参数的SQL Server SP

Question

我在尝试使用实体框架核心从SQL Server执行存储过程时遇到问题。此SP用于通过Econect将外部数据与客户端ERP(Microsoft Dynamics Great Plains(集成。

这是SP元数据(由于正文限制，我只包括SP DDL行(：

SET ANSI_NULLS ON 
SET QUOTED_IDENTIFIER OFF 
GO 
create procedure dbo.taIVMultibinBinToBinTransfer @I_vITEMNMBR char(30), @I_vLOCNCODE char(10), @I_vQTYTYPE smallint = 1, @I_vUOFM char(8) = '', @I_vBIN char(15), @I_vQUANTITY numeric(19, 5), @I_vTOBIN char(15), @I_vCreateBin tinyint = 0, @I_vResetQty tinyint = 0, @I_vSerLotNmbr char(20) = '', @I_vEXPNDATE datetime = '', @I_vRequesterTrx smallint = 0, @I_vUSRDEFND1 char(50) = '', @I_vUSRDEFND2 char(50) = '', @I_vUSRDEFND3 char(50) = '', @I_vUSRDEFND4 varchar(8000) = '', @I_vUSRDEFND5 varchar(8000) = '', @O_iErrorState int output, @oErrString varchar(255) output with encryption as 

这是我试图用来运行SP:的代码

private EconnectSpExecutionResult MakeGpTransferBetweenBins(ColonyFuelTransferDto colonyFuelTransferDb)
{
var spExecutionResult = new EconnectSpExecutionResult();
var spExecQuery = "EXECUTE [dbo].[taIVMultibinBinToBinTransfer] " +
"@I_vITEMNMBR" +
", @I_vLOCNCODE" +
", @I_vUOFM" +
", @I_vBIN" +
", @I_vQUANTITY" +
", @I_vTOBIN" +
", @O_iErrorState OUTPUT" +
", @oErrString OUTPUT";
var fuelItemIdParam = new SqlParameter("@I_vITEMNMBR", colonyFuelTransferDb.FuelItemId);
var locationGpIdParam = new SqlParameter("@I_vLOCNCODE", colonyFuelTransferDb.LocationGpId);
var measureUnitIdParam = new SqlParameter("@I_vUOFM", colonyFuelTransferDb.MeasureUnitId);
var sourceBinIdParam = new SqlParameter("@I_vBIN", colonyFuelTransferDb.SourceBinId);
var quantityParam = new SqlParameter("@I_vQUANTITY", colonyFuelTransferDb.Quantity);
var destinationBinIdParam = new SqlParameter("@I_vTOBIN", colonyFuelTransferDb.DestinationBinId);
var errorNumberParam = new SqlParameter("@O_iErrorState", spExecutionResult.ErrorNumber) { Direction = System.Data.ParameterDirection.Output };
var errorDescriptionParam = new SqlParameter("@oErrString", spExecutionResult.ErrorDescription) { Direction = System.Data.ParameterDirection.Output, Size = 255 };
_seaboardGpDbContext
.Database
.ExecuteSqlCommand
(
spExecQuery,
fuelItemIdParam,
locationGpIdParam,
measureUnitIdParam,
sourceBinIdParam,
quantityParam,
destinationBinIdParam,
errorNumberParam,
errorDescriptionParam
);
return spExecutionResult;
}

问题是我不能让它工作。SP有几个我想避免的参数，让它们取元数据中指定的默认值。此外，这个SP有两个输出参数，我需要在执行后检索它们，它们为您提供了有关集成过程中可能发生的错误的信息。

我当前遇到的错误如下：将数据类型nvarchar转换为smallint时出错。EF Core似乎没有使用命名参数，正如我们在实际运行的查询中看到的那样：

declare @p9 int
set @p9=NULL
declare @p10 nvarchar(255)
set @p10=NULL
exec sp_executesql N'EXECUTE [dbo].[taIVMultibinBinToBinTransfer] @I_vITEMNMBR, @I_vLOCNCODE, @I_vUOFM, @I_vBIN, @I_vQUANTITY, @I_vTOBIN, @O_iErrorState OUTPUT, @oErrString OUTPUT',N'@I_vITEMNMBR nvarchar(9),@I_vLOCNCODE nvarchar(3),@I_vUOFM nvarchar(2),@I_vBIN nvarchar(3),@I_vQUANTITY decimal(6,2),@I_vTOBIN nvarchar(3),@O_iErrorState int output,@oErrString nvarchar(255) output',@I_vITEMNMBR=N'092001122',@I_vLOCNCODE=N'CZE',@I_vUOFM=N'LT',@I_vBIN=N'CZE',@I_vQUANTITY=1007.00,@I_vTOBIN=N'CHZ',@O_iErrorState=@p9 output,@oErrString=@p10 output
select @p9, @p10

如果你能给我任何指导或帮助，我将不胜感激。我试过几种选择，但都不起作用。在执行SP之前，我总是会遇到与实体框架核心或sql server引擎相关的异常。

谢谢。

Answer 1

问题是参数将按接收顺序提供给sp。您希望使用sp中的名称将它们分配给sp，这些名称可以是相同的。

执行的sql本质上将是这样的：

declare @I_vITEMNMBR char(30) = .. , 
@I_vLOCNCODE char(10) = .., 
@I_vUOFM char(8) = .., 
@I_vBIN char(15) = .., 
@I_vQUANTITY numeric(19, 5) = .., 
@I_vTOBIN char(15) = .., 
@O_iErrorState int,
@oErrString varchar(255)
EXECUTE [dbo].[taIVMultibinBinToBinTransfer] 
@I_vITEMNMBR
, @I_vLOCNCODE
, @I_vUOFM
, @I_vBIN
, @I_vQUANTITY
, @I_vTOBIN
, @O_iErrorState OUTPUT
, @oErrString OUTPUT

@I_vUOFM被分配给@I_vQTYTYPE，这是错误的类型。

你想要的是这个：

declare @I_vITEMNMBR char(30) = .., 
@I_vLOCNCODE char(10) = .., 
@I_vUOFM char(8) = .., 
@I_vBIN char(15) = .., 
@I_vQUANTITY numeric(19, 5) = .., 
@I_vTOBIN char(15) = .., 
@O_iErrorState int = ..,
@oErrString varchar(255) = ..
EXECUTE [dbo].[taIVMultibinBinToBinTransfer] 
@I_vITEMNMBR  = @I_vITEMNMBR
, @I_vLOCNCODE = @I_vLOCNCODE
, @I_vUOFM = @I_vUOFM
, @I_vBIN = @I_vBIN
, @I_vQUANTITY = @I_vQUANTITY
, @I_vTOBIN = @I_vTOBIN
, @O_iErrorState = @O_iErrorState OUTPUT
, @oErrString = @oErrString OUTPUT

是的，如果你只想提供这些参数，你必须把你的代码改成这样：

var spExecQuery = @"EXECUTE [dbo].[taIVMultibinBinToBinTransfer] 
@I_vITEMNMBR    = @I_vITEMNMBR
, @I_vLOCNCODE    = @I_vLOCNCODE
, @I_vUOFM        = @I_vUOFM
, @I_vBIN         = @I_vBIN
, @I_vQUANTITY    = @I_vQUANTITY
, @I_vTOBIN       = @I_vTOBIN
, @O_iErrorState  = @O_iErrorState OUTPUT
, @oErrString     = @oErrString OUTPUT";