Dynamic results using dapper in mvc

Question

I am trying the below way to return the dynamic results using dapper and stored procedure. Am I doing it in correct way?

using (IDbConnection dbConnection = Connection)
{
    dbConnection.Open();

    var result = dbConnection.Query<dynamic>("LMSSP_GetSelectedTableData",
        new
        {
            TableName = TableName,
            LangaugeID = AppTenant.SelectedLanguageID,
            UserID = AppTenant.UserID
        }, commandType: CommandType.StoredProcedure).ToList();


    if (result != null)
    {
        // Added just for checking the data
        foreach (var item in (IDictionary<string, object>)result.FirstOrDefault())
        {
            string key = item.Key;
            string value = item.Value.ToString();

        }
    }
}

What my stored procedure do is, I will pass any table name and based on that it will return the results/records.So, obviously my number of records, columns will be varied as per the table name passed.

To achieve this I have used dynamic keyword along with dapper.

So my question is how can I pass this data to view as a model and render the controls on the view as per the properties/column data type. Can I get the data type of column OR PropertyInfo?

But, when dapper retrieves the records from database it returns as dapper row type?

Answer 1

Using same SP to fetch data from different table would be confusing (not good design). However to solve your problem technically, you can create model having list of control information. Example of control information

public class ControlInformation
{
    public string Name { get; set; }

    public dynamic Value { get; set; }

    public string ControlType { get; set; }

    // Applicable for drop down or multi select
    public string AllValues { get; set; }
}

Model will have list of ControlInformations

public List<ControlInformation> ControlInformations { get; set; }

View will render the controls (partial view based on control type) Ex: very basic case to render different view for int and another view for rest. I have 2 partial views "IntCtrl" and "StringCtrl".

@foreach (var item in Model.ControlInformations)
    {
        if (@item.ControlType == "System.Int32")
        {
            Html.RenderPartial("IntCtrl", item);
        }
        else
        {
            Html.RenderPartial("StringCtrl", item);
        }
    }

Hope this help.