Which ORM is the best when using Stored Procedures

Question

So, I asked before what a good ORM was and the answer seemed to be nhibernate. I went through the giant learning curve and well it’s awesome, but totally won’t work for me. I sadly must use stored procedures and its ability to use them is very limited.

So I have Business objects (DEVELOPERS WRITE) and some SPROCS (DBA WRITE)…… I know what you are all going to say, sprocs!! WTF… Please don’t ask….

So, please what is a good Object mapper to sprocs.

I actually own codesmith, but omg could nettiers make anymore code. Apparently the first time I ran it, I saved 100, 000 dollars, though I think I wasted 200 bucks buying, but maybe not. That’s where I need your help… What’s the best free or not free object to sproc mapper out there. Any ideas? And I really dislike items making my objects based on the database, but maybe that’s something I should accept. Tough I like it when they make the sprocs, something nice in nettiers.

-Joe

EDIT--> Ok, so far, I see sub sonic as one solution and some saying LINQ to SQL ( but I still don't trust that LINQ to SQL will last and theres no LINQ to DB2 or Oracle)... Anyone recommend LLBL Gen or Code smith. Nhibernate still looks to suck with sprocs. I watched the http://www.summerofnhibernate.com/ on sprocs and read a couple other posts and see no real easy solution with sprocs and nhibernate.

Answer 1

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 2

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

Answer 3

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://nhforge.org/blogs/nhibernate/archive/2008/11/23/populating-entities-from-stored-procedures-with-nhibernate.aspx). It also supports something I don't know how to do with linq2sql: http://nhforge.org/blogs/nhibernate/archive/2008/11/23/populating-entities-with-associations-from-stored-procedures-with-nhibernate.aspx. Note, I don't mean that last one isn't supported with linq2sql, just that I don't know how to ;)

Answer 4

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 5

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

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.