Execute stored procedure w/parameters in Dapper
var queryParameters = new DynamicParameters();
queryParameters.Add("@parameter1", valueOfparameter1);
queryParameters.Add("@parameter2", valueOfparameter2);
await db.QueryAsync<YourReturnType>(
"{NameOfStoredProcedure}",
queryParameters,
commandType: CommandType.StoredProcedure)
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
)
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);
}
}