CODEWITHSUNDEEP
c#ado.netdapper
Mike Haas
Mike Haas

Reputation: 2563

Does Dapper support an unknown number of result sets?

Update:

As Marc noted below, my fundamental question is: What happens when Read() gets called more times than there are record sets when using QueryMultiple()?

I'm working on converting an existing DB call from using SqlDataReader to Dapper.

Having problems with it though. I call a sproc, which conditionally can call 1-4 more sprocs. So I an potentially have many results sets. To simplify my explanation, lets assume I only have 1-2 result set(s). If the first sproc isn't called, but the second sproc IS called then my first Read() call eats up the first and only result set. Then I have a bunch of useless TeamItem objects that were supposed to be ProjectItem objects. Then of course it blows up on the second call to Read() because there isn't another result set.

Am I missing something about Dapper, or is this an extreme case that Dapper just won't be able to support feasibly?

if (_searchParams.WillSearchTeams)
{
    var teams = multi.Read<TeamItem>().ToList();
}
var projects = multi.Read<ProjectItem>().ToList();

Upvotes: 6

Views: 960

Answers (2)

Cᴏʀʏ
Cᴏʀʏ

Reputation: 107536

As Marc mentioned, this is now supported. Here's an example we have in simple database size query.

sp_msforeachdb returns one result set per database so you can just loop until reader.IsConsumed is true.

public IEnumerable<DatabaseStatus> QueryDatabaseStatuses()
{
    const string sql =
        """
        exec sp_msforeachdb
        N'USE [?]; 
        SELECT 
            DB_NAME() AS DbName, 
            [name] AS FileName, 
            [type_desc] AS TypeName,
            [size]/128.0 AS CurrentSizeMb,
            [size]/128.0 - CAST(FILEPROPERTY([name], ''SpaceUsed'') AS INT)/128.0 AS FreeSpaceMb, 
            [file_id] AS FileId,
            [physical_name] AS PhysicalPath,
            [state_desc] AS StateName
        FROM 
            [sys].[database_files];';
        """;

    using var reader = Connection.QueryMultiple(sql);
    while (!reader.IsConsumed && reader.Read<DatabaseStatus>(buffered: true) is { } result)
    {
        foreach (var row in result)
            yield return row;
    }
}

Upvotes: 0

Marc Gravell
Marc Gravell

Reputation: 1062975

I assume you're already using QueryMultiple; it sounds like the fundamental question here is what happens when you call Read more times than there are grids. I suppose it could return an empty sequence, but I suspect some kind of TryRead would be preferable. No, it doesn't have tht currently - but it could in theory.

Upvotes: 4

Related Questions