ORM that works with stored procedures in ASP.NET 4 and SQL Server

Question

I would like to know if there's a good ORM that can automate some of the manual work I have to do right now. Our application uses stored procedures heavily, meaning that any interaction with the database goes through a stored procedure.

Right now, I have to call stored procedures in the traditional way, and it's quite tedious. Is there an ORM that does a good job at dealing with stored procedures that:

1. Have input/output parameters that are structured, meaning table valued types
2. Easily allow for output parameters to be user-defined types, both scalar and table valued
3. Return multiple record sets

Answer 1

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:

1. A multi-mapper that allows you single rows to multiple objects
2. Input, output and return parameter support
3. 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...

Answer 2

Check out the Entity Framework.