Blog

When your stored procedure returns dynamic pivot columns, C# model cannot be used directly to map columns. There can be situations where entire result set can be dynamic or part of the result can be dynamic. In order to solve this issue, we are going to use DataTable in this tutorial. The best thing here is dynamic returned by Dapper can be cast to an IDictionary.

using (var conn= new SqlConnection(@"Data Source=..."))
{
    conn.Open();

    var params = new DynamicParameters();
    p.Add("@params", "Id=10");

    var result = conn.Query("GetPivotData", params, CommandType.StoredProcedure);
    var dt = ToDataTable(result);
}

public DataTable ToDataTable(IEnumerable<dynamic> items)
{
    if (items == null) return null;
    var data = items.ToArray();
    if (data.Length == 0) return null;

    var dt = new DataTable();
    foreach(var pair in ((IDictionary<string, object>)data[0]))
    {
        dt.Columns.Add(pair.Key, (pair.Value ?? string.Empty).GetType());
    }
    foreach (var d in data)
    {
        dt.Rows.Add(((IDictionary<string, object>)d).Values.ToArray());
    }
    return dt;
}