This post is over 6 months old. Some details, especially technical, may have changed.

MicroORMs for .NET: Stored Procedures

This post is the is part of series of posts covering the various microORMs in the .NET world. I intend to look at how each microORM meets certain data access and manipulation needs. The current series includes,

As handy as running straight, fully-tailored SELECTs straight from your app code a lot of the time, especially in the "enterprise" world your DBA is going to insist that all operations go through tightly controlled stored procedures. It's all for the greater good I'm sure but flipsake it's bloody annoying.

You are standing at the edge of a forest. Ahead of you an old wooden sign sits at a slight angle.

>> read sign

You squint your eyes as you approach the sign and read the fading writing. The sign reads

"Welcome to the forest of microORMs. You have nice eyes. Have a GREAT day."

>> enter forest

You walk, nay skip, into the forest. A few minutes in you are confronted with a horribly gnarled beast. The grumpy looking thing seems very out of place in this land of near tranquilty. As you approach, it speaks

"GROAN GRUNT Control GROWL Best Practise WHEEZE Enterprise Ready SNARL. Little being... you may not pass any way you please. That is not how we do things GRUMBLE WHEEZE. No to pass this way meatbag you must, yes must, take this route I have planned for you. If you need to change your route I must approve it and construct it COUGH GRUMBLE. There is limited scenery and not too many points to have fun"

>> kill beast

I'm afraid I can't do that

>> fffffffffffffffffffff..

Fear not help is at hand. So can we call sprocs using our microORMs, and if yes how? Lets go see shall we?1

Dapper

Dapper makes use of an extra parameter in its Query<T> method to specify a specific command type. In this way it's simply a matter of passing in the name of the sproc, the parameters and a CommandType.StoredProcedure as a named parameter.

Author author = conn.Query<Author>(
    "GetAuthorById",
    new { Id = 1 },
    commandType: CommandType.StoredProcedure
).First();

And boom thats it. Same applies for multiple results.

Massive

Massive just works.

dynamic dyn = tbl.Query("GetAuthorById @0", 1).First();

All I did was swap out the SELECT statement for the name of the sproc and Bobs Your Uncle. Nice.

PetaPoco

PetaPoco has some support for sprocs. For my needs in this article it works fine but apparently it doesn't support output parameters ATM. You need to prepend your call to the sproc with a DB specific command.

Author a = db.Query<Author>("EXECUTE GetAuthorById @0", 1).First();

The EXECUTE statement is database specific. If you are using MySql you need to use CALL instead which kind of starts breaking the abstraction a bit. I wonder if we will see this support getting more solidified in future releases.

ServiceStack ORMLite

At the time of writing this I couldn't see any built in support for sprocs using ORMLite. Either I am being my typical clueless self or we would just have to do it by hand. Here is what I came up with,

Author author = dbFactory.Exec(dbCmd => {
    dbCmd.CommandType = CommandType.StoredProcedure;
    dbCmd.Parameters.Add(new SqlParameter("@id", 1));
    dbCmd.CommandText = "GetAuthorById";
    return dbCmd.ExecuteReader().ConvertTo<Author>();
});

It's not exactly the worst thing in the world and I guess you could wrap this stuff in an extension method but still I feel spoilt by the other microORMs so far and this was a slight disappointment. Perhaps someone could either confirm or deny sproc support in ORMLite?

Simple.Data

Simple.Data has lovely syntax for this. You just call it as yet another dynamic method on the database object and Simple.Data just infers it for you.

Author author = Simple.Data.Database.Open().GetAuthorById(1).First();

Yeah thats nice.

Slightly Shorter and Summing Up

So I didn't want this one to have too much waffle (bar the odd text adventure here and there). I was concerned that perhaps calling sprocs might be one of those requirements were the whole microORM stuff starts falling apart but no. They actually handle the whole thing in a nice, clean manner.

Simple.Data is certainly starting to look more appealing to me and the whole fact that it's not database specific (you've got NoSQL stores and even experimental things such as a Registry adapter - imagine this as part of Powershell).

Anyway some code snippets for this stuff are currently available in the project/series github repo

1 Not a real game.

Published in .NET on June 17, 2011