Dapper Multiple Discover How to Return Multiple Results by Batching SQL

Description

The QueryMultiple is an extension method of the IDbConnection interface. It executes multiple queries within the same command and maps the results to strong entities.

Parameters

The syntax of the QueryMultiple method is given below:

GridReader QueryMultiple(string sql, 
                         object param = null, 
                         IDbTransaction transaction = null, 
                         int? commandTimeout = null, 
                         CommandType? commandType = null);

• The QueryMultiple method takes a SQL query as a string and an optional object parameter. The object parameter can be used to pass parameters to the SQL query.
• The QueryMultiple method also takes optional parameters for transaction, command timeout, and command type.
• The return type of QueryMultiple is an object that implements IDataReader. We can use this object to read the results of each SQL statement.

The following table shows the different parameters of a QueryMultiple method.

The following example shows how to execute two SQL statements in one round trip to the database. In the first query, we get all invoices from the Invoices table, and in the second query, we get all invoice items from the InvoiceItems table.

string sql = "SELECT * FROM Invoices WHERE InvoiceID = @InvoiceID; SELECT * FROM InvoiceItems WHERE InvoiceID = @InvoiceID;";

using (var connection = My.ConnectionFactory())
{
    connection.Open();

    using (var multi = connection.QueryMultiple(sql, new {InvoiceID = 1}))
    {
        var invoice = multi.Read<Invoice>().First();
        var invoiceItems = multi.Read<InvoiceItem>().ToList();
    }
}

We map the results of both queries to the Invoice and InvoiceItem classes.