c# – Dynamically populate Grid with return from dapper framework

Question:

I have a screen written in VB.NET WinForms, where its objective will be to execute SQL commands without the need to open a sql studio.

For simpler commands like delete, update, alter table among others it works perfectly. Now, the idea is to create a gridview dynamically when the command is a select. Basically what Management Studio itself does when we execute a SELECT command, create the columns and fill with the returned data.

I'm using Dapper Framework to execute the commands, and its return is a "DapperRow" that I can't handle or traverse.

I would like to know a way to create this type of functionality, which is, execute a SELECT, get the return, and from this return I dynamically fill my gridview with the data returned from the SELECT.

Thanks in advance.

Answer:

DapperRow can be treated as a dynamic object. I got this idea from an old code of mine. It helps, but I admit it can be done better. I think this is kind of slow.

public statis class DapperExtensions
{
    /// <summary>
    /// Transforma uma lista de DapperRows em uma lista tipada. 
    /// </summary>
    /// <typeparam name="T">Uma classe que seja um Model.</typeparam>
    /// <param name="list">A lista de DapperRows.</param>
    /// <returns></returns>
    public static IEnumerable<T> ToTypedList<T>(this IEnumerable<dynamic> list)
        where T: class, new()
    {
        var properties = typeof(T).GetProperties();

        foreach (var element in list)
        {
            var obj = new T();
            foreach (var keyValue in ((IDictionary<string, object>) element).Where(e => e.Value != null) 
            {
                PropertyInfo property = properties.FirstOrDefault(p => p.Name == keyValue.Key);
                }

                if (property == null) continue;

                switch (property.PropertyType.ToString())
                {
                    case "System.Int16":
                        if (!String.IsNullOrEmpty(keyValue.Value.ToString()))
                        {
                            property.SetValue(obj, Convert.ToInt16(keyValue.Value));
                        }

                        break;
                    case "System.Int32":
                        if (!String.IsNullOrEmpty(keyValue.Value.ToString()))
                        {
                            property.SetValue(obj, Convert.ToInt32(keyValue.Value));
                        }

                        break;
                    case "System.Int64":
                        property.SetValue(obj, Convert.ToInt64(keyValue.Value));

                        break;
                    case "System.DateTime":
                        property.SetValue(obj, Convert.ToDateTime(keyValue.Value));

                        break;
                    case "System.Decimal":
                        property.SetValue(obj, Convert.ToDecimal(keyValue.Value));

                        break;
                    default:
                        if (keyValue.Value != null)
                        {
                            property.SetValue(obj, keyValue.Value);
                        }

                        break;
                }
            }

            yield return obj;
        }
    }
}

Use:

var listaTipada = listaDeDapperRows.ToTypedList<MeuModel>();
Scroll to Top