Dynamic results using dapper in mvc

Question

Dynamic results using dapper in mvc

asp.net-core asp.net-mvc c# dapper

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?

XamDev

Popular Answer

Here we are calling method which returns Datatable :

    public DataTable GetMTDReport(bool isDepot, int userId)
    {
        using (IDbConnection _connection = DapperConnection)
        {
            var parameters = new DynamicParameters();
            parameters.Add("@IsDepot", isDepot);
            parameters.Add("@userId", userId);
            var res = this.ExecuteSP<dynamic>(SPNames.SSP_MTDReport, parameters);
            return ToDataTable(res);
        }
    }

In this we can call stored procedures by calling our custom method "ExecuteSP" :

    public virtual IEnumerable<TEntity> ExecuteSP<TEntity>(string spName, object parameters = null)
    {
        using (IDbConnection _connection = DapperConnection)
        {
            _connection.Open();
            return _connection.Query<TEntity>(spName, parameters, commandTimeout:0 , commandType: CommandType.StoredProcedure);
        }
    }

and here is "DapperConnection" method to connect the database: You can give connection string with key ["MainConnection"]

public class DataConnection
{
    public IDbConnection DapperConnection
    {
        get
        {
            return new SqlConnection(ConfigurationManager.ConnectionStrings["MainConnection"].ToString());
        }
    }
}

And at last we call "ToDataTable" method to change our response in datatable . We will receive response in DapperRow from the database because we passsed dynamic type in stored procedure.

    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;
    }

aditya goyal

View more on Stack Overflow

Kaushal · Accepted Answer · 3/27/2017 12:07:30 PM

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.

Dynamic results using dapper in mvc

Question

Fastest Dapper Plus Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator