R – Query-Mapper like iBATIS.NET but with dirty tracking, lazy-loading and cascading updates

ibatis.netnetnhibernateormstored-procedures

The problem:

  1. A DBA-controlled database with a Stored Procedure-only mandate.
  2. An expectation that the domain be defined in POCO's.

So I decided that I need an ORM designed for stored procedures and/or legacy databases.

Ideally the ORM should allow me to declaratively (or fluently) map domain objects to CRUD stored procedures.

Some findings thus far:

  1. NHibernate would work if only I could control the queries that are generated for lazy-loading.
  2. iBATIS.NET would work perfectly, but I can't find any examples of:
    1. object state tracking (new/updated/deleted/etc)
    2. parent/child commit transactions (if a parent is updated, update all dirty child objects as well).
  3. An In-House developed ORM that works with our database conventions is an option and we have already started with this. But I want to make sure we don't have any other choice.

Non-Options:

  1. SubSonic does wrap stored procs, but it doesn't map between procs and entities. I could implement a mapper of some sort if it came to it, but there are other factors also weighting against SubSonic, such as lacking VS2005 (or SharpDevelop) support and mingling data-access with domain objects.
  2. Dropping the SProcs is not an option. Data integrity is a HUGE concern for the DBA's and the higher-ups and they feel safest with SProcs. It's not my place to convince them otherwise.

Does anyone know of an ORM that suits my needs, or a way to overcome the limitations in an ORM that doesn't suit my needs 100%?

Any suggestions welcome!

Best Answer

I think NHibernate is your best bet.

You can use loader, sql-insert, sql-delete, sql-update elements to configure store procs for entities and lazy load collections, although it means quite a lot of keystrokes.

Related Topic