Can anyone suggest an elegant way of writing a generic C# Dapper procedure to return n result sets?

user_3529977 0
Viewed 841

I have created a small number of generic procedures using Dapper to return lists or individual items of any given type (using SPs on the SQL side) for use in a DI model data service, e.g.

public async Task<List<TOut>> GetList<TOut>(string proc, dynamic parameters)
{
    await using var conn = new SqlConnection(connectionString);
    var cmd = parameters == null ? new CommandDefinition(commandText: proc, commandType: CommandType.StoredProcedure) : new CommandDefinition(commandText: proc, commandType: CommandType.StoredProcedure, parameters: parameters);
    return conn.Query<TOut>(cmd).ToList();
}

However I have been trying to extend this to use the QueryMultiple method to return an arbitrary number of result sets in one query (for efficiency, since the first query is quite expensive and the other result sets depend on it). I would like to end up with strongly typed result sets (not anonymous objects) without writing reams of code. However I can't see any way to come up with an arbitrary number of type parameters - so far the best I've managed to do is to produce one method for two result sets, one for three and so on:

public async Task<dynamic> Get2Lists<T1, T2>(string proc, dynamic parameters)
{
    await using var conn = new SqlConnection(connectionString);
    var cmd = parameters == null ? new CommandDefinition(commandText: proc, commandType: CommandType.StoredProcedure) : new CommandDefinition(commandText: proc, commandType: CommandType.StoredProcedure, parameters: parameters);
    var result = conn.QueryMultiple(cmd);
    return new
    {
        Table1 = result.Read<T1>(),
        Table2 = result.Read<T2>()
    };
}

public async Task<dynamic> Get3Lists<T1, T2, T3>(string proc, dynamic parameters)
{
    await using var conn = new SqlConnection(connectionString);
    var cmd = parameters == null ? new CommandDefinition(commandText: proc, commandType: CommandType.StoredProcedure) : new CommandDefinition(commandText: proc, commandType: CommandType.StoredProcedure, parameters: parameters);
    var result = conn.QueryMultiple(cmd);
    return new
    {
        Table1 = result.Read<T1>(),
        Table2 = result.Read<T2>(),
        Table3 = result.Read<T3>()
    };
}

It seems to me there has to be a more elegant way - any suggestions?

dapper type-parameter sql c#
1 Answers

Short answer: Dapper supports the use of SqlMapper.GridReader.

Here's how we use this. You should be able to modify this to fit your needs. We have a DapperRepository class that houses all of our generic dapper calls. This is a (greatly) shortened version to show you just what you need.

using Dapper;
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

public class DapperRepository
{

    private readonly string _connStringName;

    public DapperRepository(string connStringName)
    {
        _connStringName = connStringName;
    }

    private SqlConnection OpenConnection(string cs)
    {
        var connection = ConfigurationManager.ConnectionStrings[cs].ConnectionString;
        SqlConnection con = new SqlConnection(connection);
        con.Open();
        return con;
    }

    // lots of other methods removed for the brevity of this post

    // This is the method you want
    public void QuerySPMultiple(string sql, Action<SqlMapper.GridReader> callback, object parameters = null)
    {
        using (var connection = OpenConnection(_connStringName))
        {
            var gr = connection.QueryMultiple(sql, param: parameters,commandTimeout:0, commandType: CommandType.StoredProcedure);
            callback(gr);
         }
    }
}

Let's say we have a class that looks like this:

public class FooBar
{
    public List<Stuff> MyStuff { get; set; }
    public int MyInt { get; set; }
}

And then to populate FooBar with a single stored proc call that returns multiple data sets:

// Create an instance of the repository
DapperRepository repo = new DapperRepository("your connection string");

// Create your parameters
DynamicParameters param = new DynamicParameters();
param.Add("@Name", "Value");

// Call the stored proc and get back multiple sets of data
FooBar fooBar = new FooBar();
repo.QuerySPMultiple("YourSPName", (reader) =>
{
    fooBar.MyStuff = reader.Read<Stuff>().ToList();
    fooBar.MyInt = reader.Read<int>().FirstOrDefault();
}, parameters: param);

I hope all of this makes sense! Feel free to post followup questions if not.

user_14187040
Related