QueryMultiple, transactions and bulk operations — Dapper — The Micro-ORM from Basics to Advanced

5. QueryMultiple, transactions and bulk operations

Intermediate★ Must knowLesson 5 of 8

csharp

// Several result sets in one round trip
using (var multi = await conn.QueryMultipleAsync(@"
    SELECT * FROM dbo.Customers WHERE Id = @id;
    SELECT * FROM dbo.Orders WHERE CustomerId = @id;
    SELECT COUNT(*) FROM dbo.Complaints WHERE CustomerId = @id;", new { id = 42 }))
{
    var customer = await multi.ReadSingleAsync<Customer>();
    var orders = (await multi.ReadAsync<Order>()).ToList();
    var complaints = await multi.ReadSingleAsync<int>();
}

// Transactions
await conn.OpenAsync();
await using var tx = await conn.BeginTransactionAsync();
try
{
    var orderId = await conn.QuerySingleAsync<int>(
        "INSERT INTO dbo.Orders (CustomerId, OrderDate) OUTPUT INSERTED.Id VALUES (@cid, SYSUTCDATETIME())",
        new { cid = 42 }, tx);
    await conn.ExecuteAsync("INSERT INTO dbo.OrderLines (OrderId, Product, Qty) VALUES (@orderId, @Product, @Qty)",
        lines.Select(l => new { orderId, l.Product, l.Qty }), tx);   // passing a list executes once per item
    await tx.CommitAsync();
}
catch
{
    await tx.RollbackAsync();
    throw;
}

Passing an IEnumerable of parameter objects to Execute runs the statement once per item — fine for tens of rows, slow for thousands. For bulk loads use:

Technique	Use
`SqlBulkCopy`	Fastest insert of thousands–millions of rows (from a `DataTable` or `IDataReader`)
Table-valued parameter (TVP)	Send a set of rows to a stored procedure or `MERGE` in one call
`INSERT ... SELECT FROM OPENJSON(@json)`	Send JSON and insert set-based

csharp

// Table-valued parameter
var table = new DataTable();
table.Columns.Add("ProductId", typeof(int)); table.Columns.Add("Qty", typeof(int));
foreach (var l in lines) table.Rows.Add(l.ProductId, l.Qty);
await conn.ExecuteAsync("dbo.usp_AddLines", new { OrderId = orderId, Lines = table.AsTableValuedParameter("dbo.OrderLineType") },
    commandType: CommandType.StoredProcedure);