Call stored procedure using ExecuteSqlCommand (expects parameters which was not supplied)
Solution 1
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);
Solution 2
Here is how I currently call my stored procedures with parameters. Replace the parameter names/values with your own. Keep in mind, you may have to list the parameters in the same order as declared in the stored procedure.
Create a SqlParameter array:
SqlParameter[] parameters = new SqlParameter[] {
new SqlParameter("@ParamName1", Value1),
new SqlParameter("@ParamName2", Value2),
new SqlParameter("@ParamName3", Value3),
new SqlParameter("@ParamName4", Value4),
new SqlParameter("@ParamName5", Value5)
};
Then call your stored procedure:
_context.Database.ExecuteSqlCommand(DbHelper.GenerateCommandText("SpName", parameters), parameters);
Here is the DbHelper code:
public class DbHelper
{
public static string GenerateCommandText(string storedProcedure, SqlParameter[] parameters)
{
string CommandText = "EXEC {0} {1}";
string[] ParameterNames = new string[parameters.Length];
for (int i = 0; i < parameters.Length; i++)
{
ParameterNames[i] = parameters[i].ParameterName;
}
return string.Format(CommandText, storedProcedure, string.Join(",", ParameterNames));
}
}
Solution 3
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);
Jerome Lecerf
Updated on July 02, 2021Comments
-
Jerome Lecerf almost 3 years
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 theExecuteSqlCommand
even if it is not important.Thank in advance.