Execute stored procedure w/parameters in Dapper

Question

I'm using Dapper (thanks Sam, great project.) a micro ORM with a DAL and by some reason I'm not able to execute stored procedures with input parameters.

In a example service I've the following code:

public void GetSomething(int somethingId)
{
    IRepository<Something, SomethingEnum> repository = UnitOfWork.GetRepository<Something, SomethingEnum>();

    var param = new DynamicParameters();
    param.Add("@somethingId", dbType: DbType.Int32, value:somethingId, direction: ParameterDirection.Input);

    var result = repository.Exec<Something>(SomethingEnum.spMyStoredProcedure, param);

    ...

}

When the execution of the stored procedure is triggered a SqlException is thrown stating that I need to provide the 'somethingId'

Procedure or function 'spMyStoredProcedure' expects parameter '@somethingId', which was not supplied.

My DAL is similar based on this github project of Pencroff.

Am I missing something here?

Update: I am actually passing the commandType via the SomethingEnum:

 public class SomethingEnum : EnumBase<SomethingEnum, string>
 {
    public static readonly SomethingEnum spMyStoredProcedure = new SomethingEnum("spMyStoredProcedure", "[dbo].[spMyStoredProcedure]", CommandType.StoredProcedure);

    public SomethingEnum(string Name, string EnumValue, CommandType? cmdType): base(Name, EnumValue, cmdType)
    {
    }
}

Answer 1

You need to tell it the command type: make sure there's a commandType: CommandType.StoredProcedure in the dapper call. Otherwise, it is simply executing the text command:

spMyStoredProcedure

(with some unused parameters in the ambient context). This is legal TSQL, and attempts to call spMyStoredProcedure without passing parameters - the same as if you put spMyStoredProcedure into SSMS and press f5.

Also, if your parameters are fixed, I would actually suggest just using:

var param = new { somethingId };

or even just inline it completely:

var result = repository.Exec<Something>(SomethingEnum.spMyStoredProcedure,
    new { somethingId }, commandType: CommandType.StoredProcedure);

(note: if your Exec<T> method only ever handles stored procedures, you could move the commandType internal to the method - or you could make it an optional parameter that defaults to CommandType.StoredProcedure)