使用ExecuteSqlCommand调用存储过程（期望未提供的参数） [英] Call stored procedure using ExecuteSqlCommand (expects parameters which was not supplied)

问题描述

我试图使用 context.Database.ExecuteSqlCommand 从EF调用存储过程，因为我的参数之一是数据表。

I'm trying to call a stored procedure from EF using context.Database.ExecuteSqlCommand since one of my parameters is a datatable.

以下是过程的参数：

ALTER PROCEDURE [mySchema].[myProc]
    @customerId INT,
    @indicatorTypeId INT,
    @indicators [mySchema].[IndicatorList] READONLY,
    @startDate DATETIME,
    @endDate DATETIME

这是调用存储过程的c＃代码：

and here is the c# code calling the stored procedure :

var indicatorsDt = new DataTable();

indicatorsDt.Columns.Add("Date", typeof(DateTime));
indicatorsDt.Columns.Add("Ongoing", typeof(int));
indicatorsDt.Columns.Add("Success", typeof(int));
indicatorsDt.Columns.Add("Warning", typeof(int));
indicatorsDt.Columns.Add("Error", typeof(int));
indicatorsDt.Columns.Add("Other", typeof(int));

var customerIdParam = new SqlParameter("customerId", SqlDbType.Int);
customerIdParam.Value = customerId;

var typeIdParam = new SqlParameter("indicatorTypeId", SqlDbType.Int);
typeIdParam.Value = typeId;

var startDateParam = new SqlParameter("startDate", SqlDbType.DateTime);
startDateParam.Value = startDate;

var endDateParam = new SqlParameter("endDate", SqlDbType.DateTime);
endDateParam.Value = endDate;

foreach (var indicator in indicators)
{
    indicatorsDt.Rows.Add(indicator.Date, indicator.Ongoing, 
                          indicator.Success, indicator.Warning, 
                          indicator.Error, indicator.Other);
}

var tableParameter = new SqlParameter("indicators", SqlDbType.Structured);
tableParameter.Value = indicatorsDt;
tableParameter.TypeName = "MySchema.IndicatorList";

context.Database.ExecuteSqlCommand("exec MySchema.MyProc", customerIdParam, typeIdParam, tableParameter, startDateParam, endDateParam);

如您所见，提供了所有参数，它们都不为空，但我总是得到此 SqlException ：

As you can see, all parameters are provided, none of them has a null value but I always get this SqlException :

过程或函数'UpdateIndicators'需要参数
'@customerId'，未提供。

Procedure or function 'UpdateIndicators' expects parameter '@customerId', which was not supplied.

我不知道自己缺少什么。使用 SqlParameter 错误吗？即使不重要，参数也会在 ExecuteSqlCommand 中以相同顺序提供。

I can't figure out what I am missing. Is the use of SqlParameter wrong ? Parameters are supplied in the same order in the ExecuteSqlCommand even if it is not important.

请先感谢。 / p>

Thank in advance.

推荐答案

您正在执行的SQL字符串中缺少参数。尝试在名称之前使用 @创建参数，然后将ExecuteSqlCommand调用更改为：

You are missing the parameters in the SQL string you are executing. Try creating your parameters with an "@" preceding the name and then changing the ExecuteSqlCommand call to:

context.Database.ExecuteSqlCommand("exec MySchema.MyProc @customerId, @indicatorTypeId, @indicators, @startDate, @endDate", customerIdParam, typeIdParam, tableParameter, startDateParam, endDateParam);

这篇关于使用ExecuteSqlCommand调用存储过程（期望未提供的参数）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！