Multiple SQL statements in one roundtrip using Dapper.NET

sql

database-operations

dapper-net

query-multiple

byNikita Barsukov·Jan 23, 2025

using (var conn = new SqlConnection("your_conn_string"))
{
    var sql = "SELECT * FROM Users; SELECT * FROM Orders;";
    var multi = conn.QueryMultiple(sql);
    var users = multi.Read<User>().ToList();
    var orders = multi.Read<Order>().ToList();
}

Instantly execute multiple queries in one batch with Dapper's QueryMultiple. Retrieve data and populate collections with just a few lines of code—simultaneously efficient and tidy.

High gear with QueryMultiple

Bolster your multi-query operations with QueryMultiple, a method designed for maximizing efficiency by reducing database trips. Dare we say this is probably the Cheetah 🐆 of Dapper's jungle? Unleash its complete potential by adopting Sam Saffron's tips for optimal use:

Use ReadSingle() or ReadSingleOrDefault() to rapidly fetch a single record—a method recommended by Dapper creators.

var multi = connection.QueryMultiple(sql);
var singleUser = multi.ReadSingle<User>(); // Single expected, else it's a code "Titanic" ⛴️
var singleOrder = multi.ReadSingleOrDefault<Order>(); // Nullable Order, lest the code "Deserts" you 🏜️

Stay frosty! Result sets up next

You can dynamically check for additional result sets using the GridReader.IsConsumed property—a life-saver when handling unexpected data or unsure how many result sets are getting returned.

while (!multi.IsConsumed) // Like Pringles...Once you pop, you can't stop 🤤
{
    var extraResults = multi.Read();
    // Enjoy your "extra" data
}

Store it, process it—you're in control

Are you expecting large result sets? Storing the QueryMultiple results in a collection for later processing ensures you're ready for scale-up situations.

var resultsList = new List<object>();
while (!multi.IsConsumed)
{
    resultsList.Add(multi.Read()); // Like squirrels with nuts 🐿️
}

Why Dapper rules the roost

Capable of streamlining complex operations, Dapper's QueryMultiple method marries efficiency and simplicity and serves as an alluring alternative to traditional ADO.NET approaches.

Gotchas and pro tips

Ensure the safety and correctness of SQL commands.
Embrace parameterized queries to fend off SQL injection.
Stick to using statements for proper disposal of connection resources.
Remain ever-vigilant by continually monitoring query performance.

Deep dive: Tailoring queries to scenarios

Dapper's QueryMultiple offers freedom to tailored settings based on unique needs. Here are some practical examples:

Grabbing linked data

Retrieve foreign-key linked data in a single shot, such as fetching an order with its associated line items:

using (var multi = conn.QueryMultiple(sql))
{
    var order = multi.Read<Order>().Single();
    order.Items = multi.Read<OrderItem>().ToList(); // It's a match! 💑
}

Bagging summary with details

Imagine needing customer statistics and their list of orders both. Two birds, one stone!

using (var multi = conn.QueryMultiple(sql))
{
    var customerStats = multi.ReadSingle<CustomerStats>();
    var customerOrders = multi.Read<Order>().ToList(); // All that data, one trip! 🛍️
}

Rolodex of result handling

Depending on the result set, customize your processing logic:

if (!multi.IsConsumed && multi.ReadSingle<int>() > 0)
{
    var customerOrders = multi.Read<Order>().ToList(); // Bazinga! 🎉
}

Baking transactional consistency

Integrating QueryMultiple within transactional operations ensures data integrity:

using (var transaction = conn.BeginTransaction())
{
    var multi = conn.QueryMultiple(sql, transaction: transaction);
    // Process results here
    transaction.Commit(); // Sealed with a kiss 😘
}