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