Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Call stored procedure using ExecuteSqlCommand (expects parameters which was not supplied)

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);
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));
                Slight improvement to allow passing parameters without first declaring a SqlParameter array: public static string GenerateCommandText(string storedProcedure, params SqlParameter[] parameters).
– user692942
                Aug 20, 2019 at 12:50
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);
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.