Can a Dapper DynamicParameters object be enumerated like a dictionary of parameters?

.net-4.5 c# dapper

Question

I know I can use a ParameterDirection with Dapper.DynamicParameters:

var parameters = new DynamicParameters();
parameters.Add("iparam", 42);
parameters.Add("oparam", null, DbType.Int32, ParameterDirection.Output);
connection.Execute(sql, parameters);

But can I do so when using a Dictionary<string, object>?

var parameters = new Dictionary<string, object>();
parameters.Add("iparam", 42);
parameters.Add("oparam", /* ??? */ );
connection.Execute(sql, parameters);

Alternatively, how may I iterate over a DynamicParameters in order to get the parameter names and values?

Original title:

Can a Dapper parameter with ParameterDirection be added using a dictionary?

Accepted Answer

It appears to be a bug in Dapper. This is confirmed to work in the latest NuGet package:

foreach (var paramName in parameters.ParameterNames)
{
    var value = ((SqlMapper.IParameterLookup)parameters)[paramName];
}

However, it's a bit verbose. Locally using the Dapper source (not a NuGet package), I was able to run this code without error (As of writing that's commit b77e53):

foreach (var paramName in parameters.ParameterNames)
{
    var value = parameters.Get<dynamic>(paramName);
}

According to Charles Burns' comment, it still throws an exception, which leads me to believe the patch hasn't made it into NuGet yet. The commit which fixes Get<dynamic> is here


Popular Answer

The DynamicParameters object can contain several sections for each time it has been appended. So it's not enugh to iterate through ParameterNames (it will be empty) like in the answer above.

public static Dictionary<string, object> ToParametersDictionary(this DynamicParameters dynamicParams)
{
    var argsDictionary = new Dictionary<String, Object>();
    var iLookup = (SqlMapper.IParameterLookup) dynamicParams;
    //if (dynamicParams.ParameterNames.Any())
    //{
        // read the parameters added via dynamicParams.Add("NAME", value)
        foreach (var paramName in dynamicParams.ParameterNames)
        {
            var value = iLookup[paramName];
            argsDictionary.Add(paramName, value);
        }
    //}
    //else
    //{
        // read the "templates" field containing dynamic parameters section added
        // via dynamicParams.Add(new {PARAM_1 = value1, PARAM_2 = value2});
        var templates = dynamicParams.GetType().GetField("templates", BindingFlags.NonPublic | BindingFlags.Instance);
        if (templates != null)
        {
            var list = templates.GetValue(dynamicParams) as List<Object>;
            if (list != null)
            {
                // add properties of each dynamic parameters section
                foreach (var objProps in list.Select(obj => obj.GetPropertyValuePairs().ToList()))
                {
                    objProps.ForEach(p => argsDictionary.Add(p.Key, p.Value));
                }
            }
        }
    }
    return argsDictionary;
}

and GetPropertyValuePairs(..) is

public static Dictionary<string, object> GetPropertyValuePairs(this object obj, String[] hidden = null)
{
    var type = obj.GetType();
    var pairs = hidden == null
        ? type.GetProperties()
            .DistinctBy(propertyInfo => propertyInfo.Name)
            .ToDictionary(
                propertyInfo => propertyInfo.Name,
                propertyInfo => propertyInfo.GetValue(obj, null))
        : type.GetProperties()
            .Where(it => !hidden.Contains(it.Name))
            .DistinctBy(propertyInfo => propertyInfo.Name)
            .ToDictionary(
                propertyInfo => propertyInfo.Name,
                propertyInfo => propertyInfo.GetValue(obj, null));
    return pairs;
}

public static IEnumerable<TSource> DistinctBy<TSource, TKey>(this IEnumerable<TSource> source, Func<TSource, TKey> keySelector)
{
    var seenKeys = new HashSet<TKey>();
    return source.Where(element => seenKeys.Add(keySelector(element)));
}


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why