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?

i dont follow, what do you mean dictionary is never set? the error is during debugging not runtime. — shahaf
your code reads: foreach item in dictionary. No data is ever loaded into dictionary, so item won't ever be declared, which means it can't be found — user3036342
its a function and 'dictionary' is in the arguments. the signature is: public dynamic SomeFunctionName(IDictionary<Type,string> dictionary){ ... } — shahaf
It's hard to tell since a more complete code example isn't available. However, first thing I'd try is to target framework v4 and not framework v4 client profile and see if that helps — user3036342

Dipan Saha Dipan Saha · Accepted Answer · 2016-08-25T00:07:53

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

Generic dapper QueryMultiple function

2 Answers