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?

Answer 1

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()

Answer 2

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);