ServiceStack MARS (Multiple Active Result Sets) using ORMLite and Output Parameters

Question

ServiceStack ORMLite is great, I've typically steered clear of the ORM mentality preferring to build databases as it makes sense to build databases instead of a 1:1 class model. That said, there are a couple of things that I seem to be running into difficulty around, I'm certain it's simply my ignorance shining through.

First:

Is there a way to manage multiple result sets using ORMLite? I know that one can use the QueryMultiple method using Dapper, but for whatever reason I'm having a bear of a time figuring out how to use the built-in Dapper implementation of ServiceStack.

Second:

Is there a way using ORMLite to return output parameters within a stored procedure call?

Ideally, I'd like to steer clear of MARS and output parameters and ideally I'd like to live in an ideal world :)

I'm using .NET framework 4.5, SQL Server 2008 R2 and ServiceStack 3.9.46.

Answer 1

It turns out that this is really quite simple (provided you know the magic to make it happen).

Based on the documentation and a seemingly misleading post indicating that Dapper is "included" in razor I assumed that when it was implied that Dapper was "built-in" that it was essentially a part of the included libraries.

Laugh if you will, but for those of us that aren't enlightened, I'm going to outline how to make the Dapper extensions show up. So here's the magic.

Using the Package Manager console execute the following:

Install-Package ServiceStack
Install-Package Dapper

Add the following using statements (C#) to your Service:

using ServiceStack.OrmLite;
using Dapper;

Now, when you leverage the Db object all the OrmLite AND Dapper methods will be there.

To get an output parameter it is now as simple as:

var p = new DynamicParameters();

p.Add("@param1", request.stuff1);
p.Add("@param2", request.stuff2);
p.Add("@param3", dbType: DbType.Int32, direction: ParameterDirection.Output);

Db.Execute("schema.sp_stored_proc_name", p, commandType: CommandType.StoredProcedure);

response.outputStuff = p.Get<int>("@param3");

In order to manage MARS (assume you have a SP that returns two result sets AND an output param):

p.Add("@param1", request.stuff1);
p.Add("@param2", request.stuff2);
p.Add("@param3", dbType: DbType.Int32, direction: ParameterDirection.Output);

var mars = Db.QueryMultiple("schema.sp_stored_proc_name", p, commandType: CommandType.StoredProcedure);

//firstSet contains the first result set
var firstSet = mars.Read().ToList();
//secondSet contains the second result set
var secondSet = mars.Read().ToList();

response.outputStuff = p.Get<int>("param3");

It's beautifully simple, once you know the magic :)

Here's a much more complicated example.

Hopefully this helps someone else out and saves them a bit of time.