The project I am working on has 90% of the business logic in stored procedure. I looked at Dapper as a possible path to move some of these business rules to the Application layer. The benefits to me are evident, in that I can do unit testing etc etc.
Dapper seems to help with mapping objects, to classes through sql queries. This is not enough for me, because I'd like to build my queries into an application class like service, and then unit test them before I go to my Repository( aka Dapper). I'd expect the ORM to translate my query into sql. Dapper seams not to be meant to be used this way. So I wonder what is the point if I still have to build all my business logic in sql.
My question, is do I need a different ORM like hibernate? I guess I am looking for some guidance on how to evaluate this tool.
My experience is that ORM frameworks exist on a spectrum of features and sophistication, often with inversely related compromises in speed and ease of use.
For me, Dapper is right at the end of the spectrum in terms of features and sophistication but with the trade-off that it's speed is exceptional. That's not to say that Dapper is unsophisticated and feature-less, just that it doesn't have features like sessions, query building, etc. that you will find in ORMs at the other end of that spectrum, e.g. NHibernate.
If you want an ORM that will build SQL queries based on an object-oriented domain model, then Dapper is not for you. NHibernate, Entity Framework, LLBLGen and ORMLite(?) are options you can look into. N.b. I expect there are others I've missed.
You have to have a look at SQL Plus Dot Net. It is entity framework in reverse, that is, where entity framework build SQL statements from your C# code, SQL+ .net builds C# code from your SQL. It truly is the first real innovation in data access in quite a while.