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)
   {
   }
}

Porta

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)

Extravagancy

Hi Marc, thanks for the reply. I am actually passing the commandType on the SomethingEnum. This issue is somehow related with my DAL on the DynamicParameters. If I executed it inline it's working. – Porta

@Porta well, Exec<T> doesn't look like a dapper method. Can you show where this actually becomes a dapper call? – Extravagancy

I've actually used this implementation for it: github.com/Pencroff/Dapper-DAL/blob/master/Dapper-DAL/… – Porta

somehow I had a misplaced reference for the DynamicParameters class in my services, and the repository didn't handle that fact very well. Set the references correctly and everything works as it should. The question can be closed. (Compilers are (almost) never wrong!) Thanks! – Porta

@MarcGravell i am using stored procedure in dapper for inserting instead of sending single single parameter i am passing object of a class in parameters – Midships

@MarcGravell i have 11 columns values to save but my class is having more than than 11 propertes and insertion gives me error stored procedure have specified too many parameters but have have checked the procedure too much time – Midships

Answer 2

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

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

Indomitable

Is there a shorter syntax option to use specified parameters something like this SQL: EXEC Production.uspSearchList @ListPrice = 150, @ProductCategoryID = 4; – Lambard

Answer 3

You'll need to extend it to support outbound parameters and returning results, but it contains the portion for creating the Dapper dynamic parameters.

internal static bool ExecuteProc(string sql, List<SqlParameter> paramList = null)
{
    try
    {
        using (SqlConnection conn = new SqlConnection (GetConnectionString()))
        {                    
           DynamicParameters dp = new DynamicParameters();
           if(paramList != null)
               foreach (SqlParameter sp in paramList)
                   dp.Add(sp.ParameterName, sp.SqlValue, sp.DbType);
           conn.Open();
           return conn.Execute(sql, dp, commandType: CommandType.StoredProcedure) > 0;
        }
    }
    catch (Exception e)
    {
        //do logging
        return false;
    }

}

Prado

The return type of this method is a little confusing, as it returns true if the stored procedure call returns a code greater than 0. From what I remember, stored procedure return code's are generally used to indicate the success of the procedure, where 0 is OK and > 0 is not OK. With this code, if the stored procedure returns 1, suggesting the call was not OK, the C# method returns true, which I'd interpret as the call succeeded. – Comber

@Klicker, this .Execute function returns the number of rows affected as the output. – Prado

Answer 4

Since this was the top result for me, but there were no answers that deal with ExecuteNonQuery with table valued parameters, here is the code for that:

var queryParameters = new DynamicParameters();
queryParameters.Add("@Param0", datatable0.AsTableValuedParameter());
queryParameters.Add("@Param1", datatable1.AsTableValuedParameter());
var result = await ExecuteStoredProc("usp_InsertUpdateTest", queryParameters);

private async Task<Result<int>> ExecuteStoredProc(string sqlStatement, DynamicParameters parameters)
    {
        try
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                await conn.OpenAsync();
                var affectedRows = await conn.ExecuteAsync(
                    sql: sqlStatement,
                    param: parameters,
                    commandType: CommandType.StoredProcedure);
                return Result.Ok(affectedRows);
            }
        }
        catch (Exception e)
        {
            //do logging
            return Result.Fail<int>(e.Message);
        }
    }

Marilla

what is SP data type @Param0 and @Param1? varchar(100)? – Firebrick

They are user defined table types – Marilla

Answer 5

This works for me. Note that the method doing this is async, hence the await and QueryAsync. Also notice that an anonymous type is created to facilitate the 'Id' parameter being sent.

await dbConnection.QueryAsync<Something>("StoredProcedureNameGoesHere @Id", new { Id = id });

Slowworm