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

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 proced开发者_开发问答ures 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

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...

Check out the Entity Framework.

If memory serves, LINQ to SQL has the ability to map methods to stored procedures. When you create a LINQ to SQL context inside Visual Studio, you can connect your database and drag and drop the tables into the design view. It will detect any stored procedures and generate the necessary methods inside the model objects.

See Scott Guthrie's blog post LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures) .