Dapper has fairly extensive stored procedure support.
The trivial:
create proc spGetOrder
@Id int
as
select * from Orders where Id = @Id
select * from OrderItems where OrderId = @Id
It can be mapped with the following.
var grid = cnn.QueryMultiple("spGetOrder", new {Id = 1}, commandType: CommandType.StoredProcedure);
var order = grid.Read<Order>();
order.Items = grid.Read<OrderItems>();
Additionally you have support for:
- A multi-mapper that allows you single rows to multiple objects
- Input, output and return parameter support
- An extensible interface for database specific parameter handling (like TVPs)
So for example:
create proc spGetOrderFancy
@Id int,
@Message nvarchar(100) output
as
set @Message = N'My message'
select * from Orders join Users u on OwnerId = u.Id where Id = @Id
select * from OrderItems where OrderId = @Id
return @@rowcount
Can be mapped with:
var p = new DynamicParameters();
p.Add("Id", 1);
p.Add("Message",direction: ParameterDirection.Output);
p.Add("rval",direction: ParameterDirection.ReturnValue);
var grid = cnn.QueryMultiple("spGetOrder", p, commandType: CommandType.StoredProcedure);
var order = grid.Read<Order,User,Order>((o,u) => {o.Owner = u; return o;});
order.Items = grid.Read<OrderItems>();
var returnVal = p.Get<int>("rval");
var message = p.Get<string>("message");
Finally, Dapper also allows for a custom parameter implementation:
public interface IDynamicParameters
{
void AddParameters(IDbCommand command);
}
When implementing this interface you can tell Dapper what parameters you wish to add to your command. This allow you to support table-valued parameters and other database specific features.
You're using it now on Stack Overflow...
