ExecuteSqlCommand with DbContext

Question

We are using repository and unit of work patterns for our application

I have the below method to execute stored proc:

 /*** Execute stored procedure ***/
        public virtual void ExecuteProcedure(String procedureCommand, params SqlParameter[] sqlParams)
        {
            Ctxt.Database.ExecuteSqlCommand(procedureCommand, sqlParams);
        }

My client repository will invoke the method as below:

 //Check lock conditions for site part vrsm
        public bool CanLock(int spvId)
        {
            SqlParameter output = new SqlParameter("editMode", SqlDbType.Bit);
            output.Direction = ParameterDirection.Output;

        SqlParameter parameter = new SqlParameter("spvId", SqlDbType.Int);
        parameter.Value = spvId;

        ExecuteProcedure("exec [dbo].[prc_SitePartVrsn_CanLock] {0}, @editMode = {1} output", parameter, output);

        return Convert.ToBoolean(output.Value);
    }

However line ExecuteProcedure is failing with

"Incorrect syntax near 0"

I don't have any other information in Innerexception or no clue from stack trace. The procedure is working fine when I execute it in database.

Can somebody advise what is the problem ???

Answer 1

You don't need much of a change, it is only a syntax issue:

ExecuteProcedure("exec prc_SitePartVrsn_CanLock @spvId, @editMode OUTPUT", parameter, output);

And you can either use [dbo].[prc_SitePartVrsn_CanLock] or prc_SitePartVrsn_CanLock