Multiple SQL statements in one roundtrip using Dapper.NET

Question

There is a nice feature in ADO.NET that allows you to send multiple SQL statements to database in one roundtrip and receive results for all statements:

var command = new SqlCommand("SELECT count(*) FROM TableA; SELECT count(*) FROM TableB;", connection);

using(var reader = command.ExecuteReader())
{
    reader.Read();
    resultA = reader.GetInt32(0);
    reader.NextResult();
    reader.Read();
    resultB = reader.GetInt32(0);
}

Is there a similar feature in Dapper.NET?

Steve · Answer 1 · 2020-09-17T07:41:40.367

107

Yes, the Dapper QueryMultiple extension can do that:

string query = @"SELECT COUNT(*) FROM TABLEA;
                 SELECT COUNT(*) FROM TABLEB";
using (var multi = connection.QueryMultiple(query, null))
{
    int countA = multi.Read<int>().Single();
    int countB = multi.Read<int>().Single();
}

According to Marc Gravell this is the ideal way to execute multiple queries in a single batch.

Note: Dapper creator Sam Saffron has posted a detailed explanation with code sample on using QueryMultiple to accomplish this.

UPDATE: I add the important comment from Marc

Note: from 1.5-ish (a little earler on the alpha builds) there is a ReadSingle() method that may be more convenient and efficient than Read().Single()

edited Sep 17 '20 at 07:41

answered Oct 12 '13 at 18:14

Steve

213,761
22
232
286

16

What you have here is ideal – Marc Gravell Oct 12 '13 at 19:48
6

Note: from 1.5-ish (a little earler on the alpha builds) there is a `ReadSingle()` method that may be more convenient and efficient than `Read().Single()` – Marc Gravell Jul 18 '16 at 13:27
Interesting. Need to find a bit of time to test with these new versions. Still on 1.42. Does it work with NET 4.0? – Steve Jul 18 '16 at 13:34
5

@Shil this way you reduce the number of network calls between your application server and database server. So overall response time will be better. Here i am working where we need atleast 6 result sets in a given view. So saving 5 network calls is very good – It's a trap Dec 01 '16 at 12:26
this appears to be broken in the latest version... I'm looking for a fix. – Dylan Hayes Dec 18 '17 at 20:43
how do you add the WHERE clause for each statement? – Nick Bonilla Aug 31 '21 at 19:12
Is there a way to have two queries in one QueryAsync call but somehow tell the database to swallow/suppress the results from the first one? I suppose you could SELECT INTO a dummy table variable; any more elegant way? – Patrick Szalapski Jul 28 '22 at 14:13
I don't know. Did you try to post a question with the Dapper tag? As you can see from the first comment above Dapper creators and maintainers are well known users or staff members of this site. – Steve Jul 28 '22 at 14:25

score 5 · Answer 2 · answered Jun 20 '17 at 08:58

5

var grid = connection.QueryMultiple("
             SELECT COUNT(*) FROM TABLEA
             SELECT COUNT(*) FROM TABLEB
             SELECT COUNT(*) FROM TABLEC");
var lstResult = new List<int>();
var isNext = false;
do{
    var first2 = info.Read<int>().Single();
    lstResult.Add(first2);
    isNext=info.IsConsumed;
}
while (!isNext);

answered Jun 20 '17 at 08:58

Ahmad Aghazadeh

16,571
12
101
98

Thanks!! I am comparing results of many similar stored procedure. Performance tuned and not. The procs may return multiple result sets. I was not able to get all of them. I didn't see in any example the IsConsumed flag. That's why I was not able iterate over them and collect them all. Thanks for code sample – Brikesh Kumar Dec 20 '17 at 04:16

Multiple SQL statements in one roundtrip using Dapper.NET

2 Answers2

Linked