Multi‑statement Operations

Dapper Multi‑Statement Operations with QueryMultiple

1. QueryMultiple(): Executes multiple SQL queries over one open connection and returns multiple result sets at once.
2. Separate multiple SQL statements with a semicolon ;.
3. The return type is GridReader. Call .Read<T>() to read each dataset sequentially in order.
4. Benefit: Only one database connection is created, cutting network overhead from repeated queries.

Example

using System;
using System.Collections.Generic;
using System.Data;
using MySql.Data.MySqlClient;
using Dapper;
using System.Configuration;

namespace ConsoleApp4
{
    public class Customers
    {
        public int CustomerID { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Email { get; set; }
    }

    public class Book
    {
        public string Title { get; set; }
    }

    class Program
    {
        static void Main(string[] args)
        {
            var connStr = ConfigurationManager.ConnectionStrings["CustomerConnection"].ConnectionString;
            using (IDbConnection db = new MySqlConnection(connStr))
            {
                // Multiple SQL separated by semicolons
                string sql = "SELECT * FROM Customers; SELECT * FROM Book;";
                // Get multi‑result reader
                var multiReader = db.QueryMultiple(sql);

                // Read in the exact order of SQL statements
                IEnumerable<Customers> customerList = multiReader.Read<Customers>();
                IEnumerable<Book> bookList = multiReader.Read<Book>();

                multiReader.Dispose(); // Dispose reader resources
            }
            Console.ReadKey();
        }
    }
}Code language: C# (cs)

1. Read order must strictly match SQL statement order
   The first SELECT must be read first. Wrong ordering triggers exceptions.
2. Do not call Read more times than the number of available result sets.
3. For MySQL, confirm multi‑statement support is enabled in your connection string (usually enabled by default).
4. QueryMultiple works best for SELECT‑only queries; use caution mixing INSERT / UPDATE / DELETE. Transactions are recommended instead.

Parameter‑Based Multi‑Statement Query

string sql = "SELECT * FROM student WHERE Id=@id; SELECT * FROM book WHERE Id=@id;";
using var multiReader = db.QueryMultiple(sql, new { id = 1 });
var stu = multiReader.ReadFirstOrDefault<Student>();
var book = multiReader.ReadFirstOrDefault<Book>();Code language: C# (cs)

Multi‑statement Operations