Cannot Pass Parameter in DbCommand in Entity Framework

Question

I have error when I try to pass parameter to stored procedure using DbCommand

Error returned:

Procedure or function 'Procedure_Name' expects parameter '@TEID', which was not supplied.

These are my procedure parameters:

@PageNumber INT = 1,
@PageSize INT = 50,
@StartTime nvarchar(max) = -1 ,
@EndTime nvarchar(max) = -1 ,
@Month NVARCHAR(2) = -1,
@Year NVARCHAR(4) = -1,
@Day NVARCHAR(2) = -1,
@Hour NVARCHAR(2)=-1,
@TEID nvarchar(max) ,
@IgnoreIdlingTime int=120,
@DrivingTime int=300,--5 minutes by default
@CalculationFactor nvarchar(10)='speed'

My code to execute procedure and pass parameters:

using (var context = new GPSModel())
{
    context.Database.Initialize(force: false);

    // Create a SQL command to execute the stored procedure
    var cmd = context.Database.Connection.CreateCommand();
    cmd.CommandText = "Procedure_Name";

    DbParameter TEIDParam = cmd.CreateParameter();
    TEIDParam.ParameterName = "@TEID";
    TEIDParam.DbType = System.Data.DbType.String;
    TEIDParam.Direction = ParameterDirection.Input;
    TEIDParam.Value = TEID;
    cmd.Parameters.Add(TEIDParam);

    context.Database.Connection.Open();

    var reader = cmd.ExecuteReader();
}

I tried to remove @ sign and send SqlParameter instead of DbParameter but still I have the same issue.

Is there any other way to do that where my stored procedure is very complex and contains multi sets

Thanks...

You have to add `cmd.CommandType = CommandType.StoredProcedure;` — Evk, Apr 02 '18 at 06:55

score 7 · Accepted Answer · edited Apr 02 '18 at 07:15

You can use the following code to solve the error. I have added cmd.CommandType = CommandType.StoredProcedure;. Now it works properly.

    var cmd = context.Database.Connection.CreateCommand();
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "Procedure_Name";
    DbParameter TEIDParam = cmd.CreateParameter();
    TEIDParam.ParameterName = "@TEID";
    TEIDParam.DbType = System.Data.DbType.String;
    TEIDParam.Direction = ParameterDirection.Input;
    TEIDParam.Value = TEID;
    cmd.Parameters.Add(TEIDParam);

When you are using stored procedure you have to set CommandType property to StoredProcedure, and then you should set the CommandText property to the name of the stored procedure. The command executes this stored procedure when you call one of the Execute methods.

score 0 · Answer 2 · answered Feb 12 '19 at 20:31

FOR EF core it can be done using fromSQL()

var tbl = new DataTable();
           tbl.Columns.Add("id", typeof(string));

        foreach (var item in imageChunkRequest.ChunkNames)
        {
               tbl.Rows.Add(item);

        }
 SqlParameter Parameter = new SqlParameter();
                Parameter.ParameterName = "@udt";
                Parameter.SqlDbType = SqlDbType.Structured;
                Parameter.Value = tbl;
                Parameter.TypeName = "dbo.StringList";

     _dbContext.Set<T>().FromSql("EXEC dbo.FindChunks @udt",Parameter);

Cannot Pass Parameter in DbCommand in Entity Framework

2 Answers2