BLToolkit an alternate mapper object that supports stored procedures

Question

I'm not too big a fan of direct entities, because I still think that SQL queries are the fastest and most optimized when writing manually directly and for the database (using the right joins, groupings, indexes, etc.).

In my current project, I decided to try BLToolkit, and I am very pleased with my shell around Ado.net and speed, so I query the database and return strong objects like C #. I also wrote T4, which generates stored procedure helpers , so I don’t need to use magic strings when calling stored procedures, so that all my calls use strong parameter types.

Basically, all my CRUD calls are made using stored procedures, because many of my requests are not simple select statements, and especially my creations and updates also return results that are easily made using a stored procedure that only makes one call. Anyway...

Downside

The biggest drawback of BLToolkit ( I would like everyone who rated BLToolkit to know this ) is not its capabilities or speed, but its very meager documentation, as well as its support or absence. Therefore, the biggest problem with this library is the trial version and the error to make it work. 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 it myself.

Question

What alternatives do I need to make BLToolkit:

• support for using stored procedures that return all the objects that I provide that do not necessarily match DB tables
• provide a good object mapper from a data reader to objects
• maintains relationships (all of them)
• optional (but desirable) support for multiple results.
• doesn't need any special configuration (I use only the data connection string and nothing more)

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

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

Answer 1

Alternatives (May 2011)

I see that the big guns have turned their access strategies into micro ORM tools. I played with the same idea when evaluating BLToolkit because it felt bulky (1.5 MB) for the functionality I used. In the end, I decided to write the aforementioned T4 (the link in question) to make my life easier when calling stored procedures. But there are still many features in BLToolkit that I don’t use at all and don’t even understand (the reasons are also indicated in the question).

The best alternative is micro ORM tools. Maybe it would be better to call them micro-objects. They all have the same goals: simplicity and extreme speed . They do not follow the NoSQL paradigm of their large ORMs, so most of the time we have to write (almost) every day TSQL to submit their queries. They extract data and map them to objects (and sometimes provide something more).

I would like to point out 3 of them. All of them are presented in a single code file, and not as a compiled DLL:

• Dapper - used by Stackoverflow ; all this actually provides universal extension methods compared to IDbConnection , which means that it supports any backup storage data store, since there is a connection class that implements the IDbConnection interface;
  • uses parameterized SQL
  • applies to static types as well as dynamic (.net 4 +)
  • supports matching with several objects to record the result (as in 1-1 relationships, i.e. Person + Address )
  • supports displaying objects with multiple results.
  • supports stored procedures
  • displayed, compiled (MSIL) and cached - this can also be a drawback if you use a huge number of types)
• Massive - written by Rob Connery;
  • only supports dynamic type matching (no support in .net 3.5 or earlier)
  • extremely small (several hundred lines of code)
  • provides a DynamicModel class that inherits your objects and provides CRUD functionality or from arbitrary TSQL without bimetal
  • implicit swap support
  • supports column name matching (but you must do this every time you access data, not declarative attributes)
  • supports stored procedures by writing direct parameterized TSQL
• PetaPoco - inspired by my Massive, but with the requirement to support older versions of the framework
  • supports strong types as well as dynamic
  • provides a T4 template for creating your POCO - you will get similar classes as large ORMs (which means that the first code is not supported) , but you do not need to use these you can still write your own POCO classes to keep your model easy and not Include only DB information (i.e. timestamps, etc.).
  • similar to Dapper, it also compiles mappings for speed and reuse
  • supports CRUD + IsNew
  • implicit paging support, which returns a special type with filling the page with data + all metadata (current page, number of all pages / records)
  • has an extensibility point for various scenarios (logging, type converters, etc.).
  • supports declarative metadata (matching columns and tables, etc.)
  • supports matching multiple objects for each result record with some automatic linking setting (unlike Dapper, where you must manually connect related objects)
  • supports stored procedures
  • has a helper class SqlBuilder to simplify the creation of TSQL statements.

Of all three, PetaPoco seems the busiest in terms of developing and supporting most things, taking the best of the other two (and some others).

Of all three Dappers, there is the best real-world usage reference, as it is used by one of the highest traffic sites in the world: Stackoverflow.

They all suffer from a magic string problem because most of the time you write SQL queries directly to them. But some of them can be mitigated by T4, so you can have strong typed calls that provide intuition, check compilation time, and regenerate on the fly in Visual Studio.

Bottom side of dynamic type

I think the biggest drawback of dynamic types is maintenance. Imagine your application uses dynamic types. Looking at your own code for a while will become quite problematic because you do not have any specific classes to observe or freeze. The goals of the dynamic type are blessing, which they are also a scourge in the long run.