Dapper.NET and stored proc with multiple result sets

Question

Is there any way to use Dapper.NET with stored procs that return multiple result sets?

In my case, the first result set is a single row with a single column; if it's 0 then the call was successful, and the second result set will contain that actual rows/columns of data. (and if it was non-zero, an error occured and no second result set will be provided)

Any chance to handle this with Dapper.NET? So far, I'm only ever getting back that single 0 - but nothing more.

Update: OK, it works fine - as long as the result set no. 2 is a single entity:

Dapper.SqlMapper.GridReader reader = 
    _conn.QueryMultiple("sprocname", dynParams, 
    commandType: CommandType.StoredProcedure);

int status = reader.Read<int>().FirstOrDefault();
MyEntityType resultObj = reader.Read<MyEntityType>().FirstOrDefault();

Now, I have yet another requirement.

Dapper's multi-mapping (splitting up a single row returned from SQL Server into two separate entities) for that second result set doesn't seem to be supported as of yet (at least there doesn't seem to be an overload of .Read<T> that can handle multi-mapping).

How can I get split that row into two entities?

If you need to merge or "stitch together" the separate resultsets...aka collections in the dotnet code after serialization, here is a great helper method. https://stackoverflow.com/questions/6379155/multi-mapper-to-create-object-hierarchy/38655663#38655663 — granadaCoder, Nov 13 '18 at 13:23

score 144 · Answer 1 · edited Nov 12 '18 at 16:29

QueryMultiple supports the ability to deal with multiple result sets. The only design restriction we added was totally disabling buffering for the grid reader. This means the whole API is streaming.

In the simplest case you can use:

var grid = connection.QueryMultiple("select 1 select 2");
grid.Read<int>().First().IsEqualTo(1);
grid.Read<int>().First().IsEqualTo(2);

In the slightly more sophisticated case you can do crazy stuff like this:

var p = new DynamicParameters();
p.Add("a", 11);
p.Add("r", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

connection.Execute(@"create proc #spEcho
@a int
as 
begin

select @a Id, 'ping' Name, 1 Id, 'pong1' Name
select @a Id, 'ping' Name, 2 Id, 'pong2' Name
return @a
end");

var grid = connection.QueryMultiple("#spEcho", p, 
                                     commandType: CommandType.StoredProcedure);

var result1 = grid.Read<dynamic, dynamic, Tuple<dynamic, dynamic>>(
                  (a, b) => Tuple.Create((object)a, (object)b)).ToList();
var result2 = grid.Read<dynamic, dynamic, Tuple<dynamic, dynamic>>(
                  (a, b) => Tuple.Create((object)a, (object)b)).ToList();

((int)(result1[0].Item1.Id)).IsEqualTo(11);
((int)(result1[0].Item2.Id)).IsEqualTo(1);

((int)(result2[0].Item1.Id)).IsEqualTo(11);
((int)(result2[0].Item2.Id)).IsEqualTo(2);

p.Get<int>("r").IsEqualTo(11);

You'll need to add this using statement to enable QueryMultiple .

using Dapper; /* to add extended method QueryMultiple public static GridReader QueryMultiple(this IDbConnection cnn, string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null); */

Note to future readers: QueryMultiple does not support Oracle, or rather, Oracle does not support QueryMultiple. See http://stackoverflow.com/questions/1062569/batch-multiple-select-statements-when-calling-oracle-from-ado-net/1064692#1064692 — Charles Burns, Feb 13 '14 at 16:47
this is great! are there any caveats to look out for on this? Also is the data retrieved as one round trip? — scgough, Oct 21 '16 at 12:08
does this support multi-threading to enable reading from the multi-reader in parallel? — barakcaf, Mar 07 '18 at 10:26
Do you have to enable MultipleActiveResultSets for this to perform? Or? Maybe i just dont understand what MultipleActiveResultSets is used for :) — mslot, Aug 07 '19 at 16:28

score 79 · Accepted Answer · edited Nov 12 '18 at 16:28

79

Have you tried the QueryMultiple method? It says it should:

Execute a command that returns multiple result sets, and access each in turn

You'll need to add this using statement to enable QueryMultiple .

using Dapper; /* to add extended method QueryMultiple public static GridReader QueryMultiple(this IDbConnection cnn, string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null); */

edited Nov 12 '18 at 16:28

granadaCoder

26,328
10
113
146

answered Jun 11 '11 at 19:16

Andomar

232,371
49
380
404

31

This answer would benefit from an example or a link with more detail. – Trevor.Screws Nov 13 '17 at 21:18
1

Tutorial on Handling Multiple ResultSets and Multiple Mapping Using Dapper https://dzone.com/articles/tutorial-on-handling-multiple-resultsets-and-multi – jaybro Sep 01 '22 at 23:17

Arun Prasad E S · Answer 3 · 2021-08-02T10:48:33.787

41

Multiple result set.

var reader = conn.QueryMultiple("ProductSearch", param: new { CategoryID = 1 }, commandType: CommandType.StoredProcedure);
var ProductListOne = reader.Read<ProuductTbl>().ToList();
var ProductListTwo = reader.Read<ProuductTbl>().ToList();

You'll need to add this using statement to enable QueryMultiple .

using Dapper; /* to add extended method QueryMultiple public static GridReader QueryMultiple(this IDbConnection cnn, string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null); */

Stored procedure:

CREATE PROCEDURE [dbo].[ProductSearch]
    @CategoryID as int
AS
BEGIN
    SELECT * FROM ProductTbl
    SELECT * FROM ProductTbl
END

edited Aug 02 '21 at 10:48

answered Apr 10 '17 at 09:15

Arun Prasad E S

9,489
8
74
87

2

Oracle does support QueryMultiple or QueryMultipleAsync – Umar Topia Aug 02 '17 at 18:56
1

But how does it know which category to map it too? Does the first time you call Read pull from the first returned result set? – WhiteleyJ Aug 15 '18 at 19:29
1

@Yojin Yes, respectively – Arun Prasad E S Oct 16 '18 at 10:45
1

If you need to "merge" or "stitch together" the separate collections, here is a great helper method. https://stackoverflow.com/questions/6379155/multi-mapper-to-create-object-hierarchy/38655663#38655663 – granadaCoder Nov 13 '18 at 13:22
What changes dependent on whether or not you include the `.ToList()`? I see that both you and @Sam Saffron have done it in your code samples. Does it have an implication on the number of round trips, or the amount of data returned? – mft25 Dec 07 '20 at 15:05
It didnt work for me. I have implemented dapper with .net core 5.0. – Mustafa Tığ Mar 19 '21 at 11:17

score 0 · Answer 4 · answered Jul 21 '23 at 04:03

0

For me, it was missing one column in my DB table, one of my query is getting exception in QueryMulitpleAsync() due to that missing column.

I will suggest that find the one that blocking you, then compare DB column and your raw query string.

answered Jul 21 '23 at 04:03

Saige Zhang

737
1
7
18

Dapper.NET and stored proc with multiple result sets

4 Answers4

Linked