Executing Stored Procedures With Dapper

There are two ways to execute stored procedures with Dapper: with the CommandType as Text; or as StoredProcedure.

CommandType.Text

All DbCommand objects have a CommandType property. By default this is set to Text. If you want to execute a stored procedure in a SQL statement (text) you use the Execute (or Exec) statement:

var sql = "exec [Sales by Year] @Beginning_Date, @Ending_Date";
var values = new { Beginning_Date = "2017.1.1", Ending_Date = "2017.12.31" };
var results = connection.Query(sql, values).ToList();
results.ForEach(r => Console.WriteLine($"{r.OrderID} {r.Subtotal}"));

The name of the stored procedure in this case is Sales by Year. Since the object name has embedded spaces, it is wrapped in square brackets. Parameters are supplied as a comma-separated list after the name of the procedure.

CommandType.StoredProcedure

Dapper provides access to the CommandType property via the commandType parameter included in all the various query and execute methods. The next example is the equivalent to the above, but with the CommandType set to StoredProcedure:

var procedure = "[Sales by Year]";
var values = new { Beginning_Date = "2017.1.1", Ending_Date = "2017.12.31" };
var results = connection.Query(procedure, values, commandType: CommandType.StoredProcedure).ToList();
results.ForEach(r => Console.WriteLine($"{r.OrderID} {r.Subtotal}"));
Last updated: 21/05/2019 13:54:06