In Dapper, how to execute a stored procedure without parameter?

c# dapper data-access-layer sql stored-procedures

Question

I'm employing Dapper (thanks Sam, great project.) I have a DAL and a micro ORM, but for some reason I can't run 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);

    ...

}

I need to give the "somethingId," according to a SqlException that is produced when the stored procedure is triggered for execution.

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

My DAL is based on Pencroff's github project and is comparable.

Am I overlooking something here?

Update: Actually, I'm using the SomethingEnum to send the commandType:

 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)
    {
    }
}
1
16
1/8/2019 10:47:07 AM

Accepted Answer

You must provide the command type to it. Ensure that there is acommandType: CommandType.StoredProcedure in sartorial call. In all other cases, it is just issuing the text command:

spMyStoredProcedure

(with a few other factors in the surrounding environment). This TSQL code is legitimate and tries to callspMyStoredProcedure the same as if you typed with no arguments passedspMyStoredProcedure then push into SSMSf5 .

Additionally, assuming your settings are set, I would really advise using just:

var param = new { somethingId };

or just align it completely:

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

Observe: If yourExec<T> function never touches anything other than stored routines, you may transfer thecommandType internal to the function, or you could make it a parameter that is optional and defaults toCommandType.StoredProcedure )

32
2/6/2014 9:24:23 AM

Popular Answer

var queryParameters = new DynamicParameters();
queryParameters.Add("@parameter1", valueOfparameter1);
queryParameters.Add("@parameter2", valueOfparameter2);

await db.QueryAsync<YourReturnType>(
    "{NameOfStoredProcedure}",
    queryParameters,
    commandType: CommandType.StoredProcedure)


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow