Multi-result sets

A stored procedure can return more than one SELECT result. CaeriusNet maps each set to a separate DTO collection in a single round trip. You do not need extra queries or manual NextResultAsync calls.

This page demonstrates two scenarios: a dashboard read with three result sets, and a read with two result sets driven by a TVP filter.

SQL Server objects

-- Dashboard summary with users, orders, and per-user statistics.
CREATE PROCEDURE Users.usp_Get_Dashboard
AS
BEGIN
    SET NOCOUNT ON;

    -- Set #1: users
    SELECT UserId, UserGuid
    FROM   Users.Users
    ORDER BY UserId;

    -- Set #2: orders
    SELECT OrderId, UserId, Label, Amount, CreatedAt
    FROM   Users.Orders
    ORDER BY OrderId;

    -- Set #3: per-user statistics
    SELECT  u.UserId,
            u.UserName,
            COUNT(o.OrderId)           AS OrdersCount,
            COALESCE(SUM(o.Amount), 0) AS TotalAmount
    FROM    Users.Users        AS u
    LEFT JOIN Users.Orders     AS o ON o.UserId = u.UserId
    GROUP BY u.UserId, u.UserName
    ORDER BY u.UserId;
END
GO

-- Users and their orders, filtered by a TVP of user IDs.
CREATE PROCEDURE Users.usp_Get_Users_With_Orders_By_Tvp
    @tvp Types.tvp_Int READONLY
AS
BEGIN
    SET NOCOUNT ON;

    -- Set #1: matching users
    SELECT u.UserId, u.UserGuid
    FROM   Users.Users AS u
    INNER JOIN @tvp    AS t ON t.UserId = u.UserId
    ORDER BY u.UserId;

    -- Set #2: their orders
    SELECT o.OrderId, o.UserId, o.Label, o.Amount, o.CreatedAt
    FROM   Users.Orders AS o
    INNER JOIN @tvp     AS t ON t.UserId = o.UserId
    ORDER BY o.OrderId;
END
GO

DTO definitions

[GenerateDto]
public sealed partial record UserDto(int UserId, Guid UserGuid);

[GenerateDto]
public sealed partial record OrderDto(
    int OrderId,
    int UserId,
    string Label,
    decimal Amount,
    DateTime CreatedAt);

[GenerateDto]
public sealed partial record UserStatsDto(
    int UserId,
    string UserName,
    int OrdersCount,
    decimal TotalAmount);

1. Read three result sets

A 3-tuple destructures the three sets directly at the call site. The DTO type at each position must match the columns of the corresponding SELECT.

public async ValueTask<DashboardSnapshot> GetDashboardAsync(CancellationToken ct)
{
    var sp = new StoredProcedureParametersBuilder(
            "Users", "usp_Get_Dashboard", ResultSetCapacity: 25)
        .Build();

    var (users, orders, stats) = await DbContext
        .QueryMultipleReadOnlyCollectionAsync<UserDto, OrderDto, UserStatsDto>(sp, ct);

    return new DashboardSnapshot(users, orders, stats);
}

Telemetry tags

caerius.resultset.multi = true · caerius.resultset.expected_count = 3

2. Read two result sets with a TVP

You can combine a TVP input with a multi-result-set output in one round trip and one span:

public async ValueTask<(IReadOnlyCollection<UserDto> Users, IReadOnlyCollection<OrderDto> Orders)>
    GetUsersWithOrdersByTvpAsync(
        IReadOnlyCollection<int> userIds,
        CancellationToken ct)
{
    if (userIds.Count == 0) return ([], []);

    IEnumerable<UsersIntTvp> tvp = userIds.Select(id => new UsersIntTvp(id));

    var sp = new StoredProcedureParametersBuilder(
            "Users", "usp_Get_Users_With_Orders_By_Tvp", ResultSetCapacity: 25)
        .AddTvpParameter("tvp", tvp)
        .Build();

    var (users, orders) = await DbContext
        .QueryMultipleReadOnlyCollectionAsync<UserDto, OrderDto>(sp, ct);

    return (users, orders);
}

Telemetry tags

caerius.tvp.used = true · caerius.resultset.multi = true · caerius.resultset.expected_count = 2

Available method families

Method	Sets	Return type
QueryMultipleReadOnlyCollectionAsync<T1, T2> through <T1, T2, T3, T4, T5>	2-5	tuple of ReadOnlyCollection<T>
QueryMultipleImmutableArrayAsync<T1, T2> through <T1, T2, T3, T4, T5>	2-5	tuple of ImmutableArray<T>
QueryMultipleIEnumerableAsync<T1, T2> through <T1, T2, T3, T4, T5>	2-5	tuple of IEnumerable<T>

Result-set order is the contract

CaeriusNet maps result sets positionally. The first SELECT becomes T1, the second becomes T2, and so on. The DTO type passed at each position must match the columns of the corresponding SELECT. CaeriusNet does not match columns by name at runtime.

---

Next: Transactions - commit, C#-side rollback, and SQL-side rollback.