Generic dapper QueryMultiple function

Question

I am using asp.net mvc, dapper and MySql stored procedures for my web app. So far, for each page I have 1-3 stored procedures calls. I recently found out my hosting only provides 10 parallel connections and so I want to combine my calls into one per page. I already made the necessary stored procedures in the database, but my problem is using dapper in generic way to get the procedures results.

What I want is to make a generic function that will get: 1) Stored Procedure name. 2) List of types the stored procedure returns. And for each type if it is Single/ToList. The generic function should result a list of variablese that are the results from the stored procedure.

The example in dapper page shows how to make a non-generic QueryMultiple call:

var sql = 
@"
select * from Customers where CustomerId = @id
select * from Orders where CustomerId = @id
select * from Returns where CustomerId = @id";

using (var multi = connection.QueryMultiple(sql, new {id=selectedId}))
{
   var customer = multi.Read<Customer>().Single();
   var orders = multi.Read<Order>().ToList();
   var returns = multi.Read<Return>().ToList();
   ...
} 

Basicly, what I want is to insert the Customer,Order,Return types into a dictionary that will state each of them to be Single or ToList, and then do something like:

var result = new List<dynamic>();
var sql = 
@"
select * from Customers where CustomerId = @id
select * from Orders where CustomerId = @id
select * from Returns where CustomerId = @id";

using (var multi = connection.QueryMultiple(sql, new {id=selectedId}))
{
   foreach(var item in dictionary)
   {
       if (item.Value.equals("Single"))
           result.Add(multi.Read<item.Key>().Single());
       else if (item.Value.equals("ToList"))
           result.Add(multi.Read<item.Key>().ToList());
   }
}
return result;

My problem is, visual studio says:

The type or namespace name 'item' could not be found

Pointing at 'item' in: multi.Read()

What am I doing wrong? Is there a better way to implement a generic function that uses dapper's QueryMultiple?

Answer 1

I think an alternative approach could be instead of QueryMultiple() method, you can make use of

IDbConnection.ExecuteReader() 

extension method from Dapper.SqlMapper which returns IDataReader and you can loop through it and get the result sets by reader.NextResult() and map them to your object.

Answer 2

It's an old topic though but thought it might help others. In that case you can use below code to make it work. Pass type in Read function as parameter. You can return dynamic. Using Expando Object you can generate properties dynamically.

I would prefer to create object like below and pass list of object to function which will generate dynamic return type for you.

public class MapItem
{
    public Type Type { get; private set; }
    public DataRetriveTypeEnum DataRetriveType { get; private set; }
    public string PropertyName { get; private set; }

    public MapItem(Type type, DataRetriveTypeEnum dataRetriveType, string propertyName)
    {
        Type = type;
        DataRetriveType = dataRetriveType;
        PropertyName = propertyName;
    }
}

 //And then make a reusable function like below

public async Task<dynamic> ExecuteQueryMultipleAsync(IDbConnection con, string spName, object param = null,IEnumerable<MapItem> mapItems = null)
    {
        var data = new ExpandoObject();

        using (var multi = await con.QueryMultipleAsync(spName,param, commandType:CommandType.StoredProcedure))
        {
            if (mapItems == null) return data;

            foreach (var item in mapItems)
            {
                if (item.DataRetriveType == DataRetriveTypeEnum.FirstOrDefault)
                {
                    var singleItem = multi.Read(item.Type).FirstOrDefault();
                    ((IDictionary<string, object>) data).Add(item.PropertyName, singleItem);
                }

                if (item.DataRetriveType == DataRetriveTypeEnum.List)
                {
                    var listItem = multi.Read(item.Type).ToList();
                    ((IDictionary<string, object>)data).Add(item.PropertyName, listItem);
                }
            }

            return data;
        }
    }

Below how you call the above method:

var mapItems = new List<MapItem>()
        {
            new MapItem(typeof(YourObject1), DataRetriveTypeEnum.FirstOrDefault, "Object1"),
            new MapItem(typeof(YourObject2), DataRetriveTypeEnum.List, "Object2")
        };

        var response = await ExecuteQueryMultipleAsync(name, request, mapItems);

        var object1 = response.Result.Object1;
        var listOfObject2 = ((List<dynamic>)response.Result.Object2).Cast<YourObject2>();

Hope this helps.

Cheers