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

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.

Answer 3

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 4

Depending on the database Entity Framework, or NHibernate are likely your best options (examples in links).

Answer 5

The LINQ to SQL designer will give you type-safe sprocs as methods on the DataContext object. You can map those to objects for CRUD operations fairly easily.

In fact, I'm in the middle of doing exactly that.

Answer 6

Since you've got a DBA writing the sprocs, I would think the best thing to do would be to work closely with him to figure out how to map the tables to objects, and how to structure the database so that it works with your domain model. There's nothing wrong with sprocs, they just require close collaboration between the developers and the DBAs.

Ideally, the DBA in question is part of your project team...

Answer 7

These days you might try Nfinity.Data, which has strongly typed support for stored procedures, including table-valued parameters (TVP), and out parameters. To read any results, you need to access a reader manually (it provides a reader abstraction for this).

I’m not convinced of the NHibernate or EF idea of mapping entities to stored procs, as their inner workings are indeterminate, i.e., can’t be verified in any way until runtime. It’s more declarative to just do everything in code, via a reader as above.

Answer 8

The main issue I see with this, is that by going with SP you are automatically loosing lot of the flexibility you get when using ORM, specially on the retrieval of information. Because of this, I am sure you won't be able to use All of the features of most ORM.

For example, if you use linq2sql, you will have pretty much wrapper to the SPs. You can also map insert, deletes and updates of the generated entities to stored procedures. Where you loose a lot is on the retrieval of information, both because the queries are now fixed (and you might retrieve more information than needed i.e. extra columns - or create lots of SPs) and on lazy loading.

Update: I am more a linq2sql guy, but I would take a second look at the assumptions you are taking about NHibernate. In particular, I doubt it will force column order as it is configured with column names (see http://nhibernate.info/blog/2008/11/23/populating-entities-from-stored-procedures-with-nhibernate.html). It also supports something I don't know how to do with linq2sql: http://nhibernate.info/blog/2008/11/23/populating-entities-with-associations-from-stored-procedures-with-nhibernate.html. Note, I don't mean that last one isn't supported with linq2sql, just that I don't know how to ;)

Answer 9

I like the way the Entity Framework handles sprocs right now. You can associate sprocs with the crud operations of an entity, it even detects which sprocs match up with the properties of your entity. The one big downside right now is if you associate one sproc with an entity you must associate all the crud operations with a sproc.

This EF Sproc article has some great examples of how to use sprocs in EF and has some really nice Extension methods for it as well.