I've Business objects (Designers WRITE) plus some SPROCS (DBA WRITE)

Can anybody recommend a great object mapper to cope with this type of setup.

I attempted codesmith and nhibernate coupled with trouble. I don't mind if my ORM is free of charge or compensated.

SubSonic has excellent support for sprocs. It'll wrap each one of these inside a assistant method and you will retrieve strongly-typed collections or organizations in the results if you would like. I show a method to do this in this web site publish. As lengthy as the sproc returns exactly the same schema as Choose * FROM TableName would, it'll use your SubSonic organizations.

So far as producing classes according to your db, SubSonic creates partial classes to help you extend them when needed. You might do mappings in the SubSonic produced classes for your actual model.

Subsonic includes 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 really a solid "Swiss Military knife" style ORM.

Disclaimer: I'm the writer of Dapper.


If you're searching for an easy object mapper that handles mapping procs to business objects Dapper is a great fit.

Bear in mind it ships without any "graph management", "identity map" and so forth. It provides a bare bone, complete solution which provides coverage for many situations other ORMs don't.

Nevertheless, it provides among the quickest object materializers available, which may be 10 x faster than EF as well as 100x faster than subsonic in certain benchmarks.


The trivial:

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

Could be planned using the following:

var grid = cnn.QueryMultiple("spGetOrder", new {Id = 1}, commandType: CommandType.StoredProcedure);
var order = grid.Read<Order>();
order.Items = grid.Read<OrderItems>(); 

Furthermore you've support for:

  1. A multi-mapper that enables 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 instance:

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

Could be planned 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 permit a custom parameter implementation:

public interface IDynamicParameters
{
   void AddParameters(IDbCommand command);
}

When applying this interface you are able to tell dapper what parameters you want to increase your command. This permit you to support Table-Valued-Params along with other DB specific features.

With respect to the database Entity Framework, or NHibernate are most likely your very best options (good examples in links).