I'm trying to call a stored procedure from EF using context.Database.ExecuteSqlCommand
since one of my parameters is a datatable.
Here are the procedure's parameters :
ALTER PROCEDURE [mySchema].[myProc]
@customerId INT,
@indicatorTypeId INT,
@indicators [mySchema].[IndicatorList] READONLY,
@startDate DATETIME,
@endDate DATETIME
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);
As you can see, all parameters are provided, none of them has a null value but I always get this SqlException
:
Procedure or function 'UpdateIndicators' expects parameter '@customerId', which was not supplied.
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.
Thank in advance.
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);
This works:
var bookIdParameter = new SqlParameter();
bookIdParameter.ParameterName = "@BookId";
bookIdParameter.Direction = ParameterDirection.Output;
bookIdParameter.SqlDbType = SqlDbType.Int;
var authors = context.Database.ExecuteSqlCommand("usp_CreateBook @BookName, @ISBN, @BookId OUT",
new SqlParameter("@BookName", "Book"),
new SqlParameter("@ISBN", "ISBN"),
bookIdParameter);