时间戳

首页 > 解决方案 > DbContext ExecuteSqlRaw 缺少参数错误

c# - DbContext ExecuteSqlRaw 缺少参数错误

问题描述

我有执行存储过程的代码：

var result = await DbContext.Database.ExecuteSqlRawAsync(
                "Get_FlightBooking @BookingReferenceNumber, @BM_BKDTFOM, @BM_BKDTTO, @User_Id",
                new SqlParameter("@BookingReferenceNumber", model.BookingId == string.Empty ? null : model.BookingId),
                new SqlParameter("@BM_BKDTFOM", model.FromDt == string.Empty ? null : model.FromDt),
                new SqlParameter("@BM_BKDTTO", model.FromDt == string.Empty ? null : model.FromDt),
                new SqlParameter("@User_Id", model.UserId));

这是存储过程：

ALTER Proc [dbo].[Get_FlightBooking]
(

 @BookingReferenceNumber   VARCHAR(50)=NULL,   
 @BM_BKDTFOM  VARCHAR(50)=NULL,  
 @BM_BKDTTO   VARCHAR(50)=NULL,  
 @User_Id  bigint=null
)
as
begin
    SELECT distinct BookingID,
            BookingReferenceNumber,
            Sector,
            AirlineCode,
            AirlineName,
    FROM dbo.Flights AS fb  
    WHERE (fb.BookingReferenceNumber=@BookingReferenceNumber OR @BookingReferenceNumber IS NULL)   
         AND((CAST(fb.BookingDate AS DATE)>= CAST(@BM_BKDTFOM AS DATE) OR @BM_BKDTFOM IS NULL) AND (CAST(fb.BookingDate AS DATE)<=CAST(@BM_BKDTTO AS DATE) OR @BM_BKDTTO IS NULL))  
         AND (fb.UserId=@User_Id or @User_Id=1)
END

是否有人看到存储过程的执行方式有问题？调用 C# 代码时出现此错误：

参数化查询 '(@BookingReferenceNumber nvarchar(4000),@BM_BKDTFOM nvarchar(400') 需要未提供的参数 '@BookingReferenceNumber'。

知道有什么问题吗？

标签： c#entity-frameworklinqentity-framework-coredbcontext

解决方案

你需要使用DBNull.Value而不是null

var result = await DbContext.Database.ExecuteSqlRawAsync(
            "UspGet_FlightBooking @BookingReferenceNumber, @BM_BKDTFOM, @BM_BKDTTO, @User_Id",
            new SqlParameter("@BookingReferenceNumber", string.IsNullOrWhiteSpace(model.BookingId) ? DNNull.Value : model.BookingId),
            new SqlParameter("@BM_BKDTFOM", string.IsNullOrWhiteSpace(model.FromDt) ? DNNull.Value : model.FromDt),
            new SqlParameter("@BM_BKDTTO", string.IsNullOrWhiteSpace(model.FromDt) ? DNNull.Value : model.FromDt),
            new SqlParameter("@User_Id", model.UserId));

如果您不能使用 C# 9.0，可以尝试像这样初始化您的 SqlParameters：

 new SqlParameter("@BookingReferenceNumber", System.Data.SqlDbType.NVarChar, 4000)
            { Value = string.IsNullOrWhiteSpace(model.BookingId) ? (object)DBNull.Value : model.BookingId }

推荐阅读