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,

• Obligatory Introduction Post
• Syntax - SELECTs
• Stored Procedures
• Inserts, Updates & Deletes
  

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.

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

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

¹ Not a real game.