BLToolkit alternative object mapper that supports stored procedures

Question

I'm not too big of a fan of direct entity mappers, because I still think that SQL queries are fastest and most optimized when written by hand directly on and for the database (using correct joins, groupings, indexes etc).

On my current project I decided to give BLToolkit a try and I'm very pleased with its wrapper around Ado.net and speed so I query database and get strong type C# objects back. I've also written a T4 that generates stored procedure helpers so I don't have to use magic strings when calling stored procedures so all my calls use strong types for parameters.

Basically all my CRUD calls are done via stored procedures, because many of my queries are not simple select statements and especially my creates and updates also return results which is easily done using a stored procedure making just a single call. Anyway...

Downside

The biggest drawback of BLToolkit (I'd like everyone evaluating BLToolkit to know this) aren't its capabilities or speed but its very scarce documentation as well as support or lack thereof. So the biggest problem with this library is doing trial and error to get it working. That's why I also don't want to use too many different parts of it, because the more I use the more problems I have to solve on my own.

Question

What alternatives do I have to BLToolkit that:

• support use of stored procedures that return whatever entities I provide that are not necessarily the same as DB tables
• provide a nice object mapper from data reader to objects
• supports relations (all of them)
• optional (but desirable) support for multiple result-set results
• doesn't need any special configuration (I only use data connection string and nothing else)

Basically it should be very lightweight, should basically just have a simple Ado.net wrapper and object mapper.

And the most important requirement: is easy to use, well supported and community uses it.

Answer 1

Alternatives (May 2011)

I can see that big guns have converted their access strategies to micro ORM tools. I was playing with the same idea when I evaluated BLToolkit, because it felt bulky (1.5MB) for the functionality I'd use. In the end I decided to write the aforementioned T4 (link in question) to make my life easier when calling stored procedures. But there are still many possibilities inside BLToolkit that I don't use at all or even understand (reasons also pointed out in the question).

Best alternative are micro ORM tools. Maybe it would be better to call them micro object mappers. They all have the same goals: simplicity and extreme speed. They are not following the NoSQL paradigm of their big fellow ORMs, so most of the time we have to write (almost) everyday TSQL to power their requests. They fetch data and map them to objects (and sometimes provide something more - check below).

I would like to point out 3 of them. They're all provided in a single code file and not as a compiled DLL:

• Dapper - used by Stackoverflow itself; all it actually does it provides generic extension methods over IDbConnection which means it supports any backing data store as long there's a connection class that implements IDbConnection interface;
  • uses parametrised SQL
  • maps to static types as well as dynamic (.net 4+)
  • supports mapping to multiple objects per result record (as in 1-1 relationships ie. Person+Address)
  • supports multi-resultset object mapping
  • supports stored procedures
  • mappings are generated, compiled (MSIL) and cached - this can as well be downside if you use huge number of types)
• Massive - written by Rob Connery;
  • only supports dynamic type mapping (no support in .net 3.5 or older baby)
  • is extremely small (few hundreds of lines of code)
  • provides a DynamicModel class that your entities inherit from and provides CRUD functionaly or maps from arbitrary baremetal TSQL
  • implicit paging support
  • supports column name mapping (but you have to do it every time you access data as opposed to declarative attributes)
  • supports stored procedures by writing direct parametrised TSQL
• PetaPoco - inspired my Massive but with a requirement to support older framework versions
  • supports strong types as well as dynamic
  • provides T4 template to generate your POCOs - you'll end up with similar classes as big fellow ORMs (which means that code-first is not supported) but you don't have to use these you can still write your own POCO classes of course to keep your model lightweight and not include DB only information (ie. timestamps etc.)
  • similar to Dapper it also compiles mappings for speed and reuse
  • supports CRUD operations + IsNew
  • implicit paging support that returns a special type with page-full of data + all metadata (current page, number of all pages/records)
  • has extensibility point for various scenarios (logging, type converters etc)
  • supports declarative metadata (column/table mappings etc)
  • supports multi object mapping per result record with some automatic relation setting (unlike Dapper where you have to manually connect related objects)
  • supports stored procedures
  • has a helper SqlBuilder class for easier building TSQL statements

Of all three PetaPoco seems to be the liveliest in terms of development and support most of the things by taking the best of the other two (and some others).

Of all three Dapper has the best real-world usage reference because it's used by one of the highest traffic sites on the world: Stackoverflow.

They all suffer from magic string problem because you write SQL queries directly into them most of the time. But some of this can be mitigated by T4, so you can have strong typed calls that provide intellisense, compile-time checking and re-generation on the fly within Visual Studio.

Downside of dynamic type

I think the biggest downside of dynamic types is maintenance. Imagine your application using dynamic types. Looking at your own code after a while will become rather problematic, because you don't have any concrete classes to observe or hang on to. As much as dynamic types are a blessing they're as well a curse on the long run.