Which DAL libraries support stored procedure execution and results materialisation

Question

I'm used to EF because it usually works just fine as long as you get to know it better, so you know how to optimize your queries. But.

What would you choose when you know you'll be working with large quantities of data? I know I wouldn't want to use EF in the first place and cripple my application. I would write highly optimised stored procedures and call those to get certain very narrow results (with many joins so they probably won't just return certain entities anyway).

So I'm a bit confused which DAL technology/library I should use? I don't want to use SqlConnection/SqlCommand way of doing it, since I would have to write much more code that's likely to hide some obscure bugs.

I would like to make bug surface as small as possible and use a technology that will accommodate my process not vice-a-versa...

Is there any library that gives me the possibility to:

• provide the means of simple SP execution by name
• provide automatic materialisation of returned data so I could just provide certain materialisers by means of lambda functions?

like:

List<Person> result = Context.Execute("StoredProcName", record => new Person{
    Name = record.GetData<string>("PersonName"),
    UserName = record.GetData<string>("UserName"),
    Age = record.GetData<int>("Age"),
    Gender = record.GetEnum<PersonGender>("Gender")
    ...
});

or even calling stored procedure that returns multiple result sets etc.

List<Question> result = Context.ExecuteMulti("SPMultipleResults", q => new Question {
    Id = q.GetData<int>("QuestionID"),
    Title = q.GetData<string>("Title"),
    Content = q.GetData<string>("Content"),
    Comments = new List<Comment>()
}, c => new Comment {
    Id = c.GetData<int>("CommentID"),
    Content = c.GetData<string>("Content")
});

Basically this last one wouldn't work, since this one doesn't have any knowledge how to bind both together... but you get the point.

So to put it all down to a single question: Is there a DAL library that's optimised for stored procedure execution and data materialisation?

Answer 1

Business Layer Toolkit might be exactly what's needed here. It's a lightweight ORM tool that supports lots of scenarios including multiple result sets although they seem very complicated to do.