Which ORM is the best when using Stored Procedures

Question

I have Business objects (DEVELOPERS WRITE) and some SPROCS (DBA WRITE)

Can anyone recommend a good object mapper to deal with this kind of setup.

I tried codesmith and nhibernate and had trouble. I do not mind if my ORM is free or paid.

Answer 1

SubSonic has excellent support for sprocs. It will wrap each one in a helper method and you can retrieve strongly-typed collections or entities from the results if you want. I show a way to do that in this blog post. As long as your sproc returns the same schema as SELECT * FROM TableName would, it will work with your SubSonic entities.

As far as generating classes based on your db, SubSonic generates partial classes so you can extend them as needed. You could also do mappings from the SubSonic generated classes to your actual model.

Answer 2

Subsonic has a flexible solution:

    class CustomerOrder {
        private string productName;

        public string ProductName {
            get { return productName; }
            set { productName = value; }
        }
        private int total;

        public int Total {
            get { return total; }
            set { total = value; }
        }

    }

Then:

List<CustomerOrder> orders = Northwind.SPs.CustOrderHist("ALFKI")
        .ExecuteTypedList<CustomerOrder>();

Subsonic is a solid "Swiss Army knife" style ORM.

Answer 3

Disclaimer: I am the author of Dapper.

---

If you are looking for a simple object mapper that handles mapping procs to business objects Dapper is a good fit.

Keep in mind it ships with no "graph management", "identity map" and so on. It offers a bare bone, complete solution which covers many scenarios other ORMs do not.

Nonetheless, it offers one of the fastest object materializers out there, which can be 10x faster than EF or even 100x faster than subsonic in some benchmarks.

---

The trivial:

create proc spGetOrder
   @Id int
as 
select * from Orders where Id = @Id
select * from OrderItems where OrderId = @Id 

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/Return param support
3. An extensible interface for db 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 allow 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-Params and other DB specific features.