Parsing Dynamic SQL in C# and binding to WebGrid in ASP.Net MVC

Question

Parsing Dynamic SQL in C# and binding to WebGrid in ASP.Net MVC I have dynamic SQL Queries that uses pivot to produce result and the resultset may be like this.:

Name    Div1    Div2    Div3    Div4    Div5
Active  1   0   0   0   0
Busy    0   0   0   0   1
NA  0   1   0   0   0
Suspended   0   0   0   1   0

There can be n number of divisions. I want to bind this data with ASP.NET MVC WebGrid control. I am not able to achieve this.

My C# code is given below:

dynamic list = db.ExecuteStoreQuery<dynamic>("exec [dbo].[proc_GetData]").ToList();
return list;

I want to bind this list with ASP.NET MVC Webgrid, my webgrid code as below:

WebGrid grid = new WebGrid(Model.DataList);

List<WebGridColumn> list = new List<WebGridColumn>();

list.Add(new WebGridColumn
{
    Header = "Name",
    ColumnName = "Name"
});

foreach (var item in Model.DivList)
{
  list.Add(new WebGridColumn
  {
    Header = item,
    ColumnName = item
  });
}

@grid.GetHtml(tableStyle: "webgrid",
alternatingRowStyle: "webgrid-alternating-row",
headerStyle: "webgrid-header",
footerStyle: "webgrid-footer",
selectedRowStyle: "webgrid-selected-row",
rowStyle: "webgrid-row-style",
columns: col);

This one is not working. Please help me how can I achieve this task. Thanks.

Answer 1

You have to specify type (Class along with fixed properties) in ExecuteStoreQuery. You have another alternative option which you can use. I explain it in four simple steps:

1) Retrieve result set into DataTable

private DataTable GetResultReport()
    {
        DataTable retVal = new DataTable();
        EntityConnection entityConn = (EntityConnection)db.Connection;
        SqlConnection sqlConn = (SqlConnection)entityConn.StoreConnection;
        using (SqlCommand cmdReport = sqlConn.CreateCommand())
        {
            cmdReport.CommandType = CommandType.StoredProcedure;
            cmdReport.CommandText = "proc_GetData";

            SqlDataAdapter daReport = new SqlDataAdapter(cmdReport);
            using (cmdReport)
            {
                daReport.Fill(retVal);
            }
        }

        return retVal;
    }

2) Convert DataTable to List of IDictionary type

private List<IDictionary> ConvertToDictionary(DataTable dtObject)
    {
        var columns = dtObject.Columns.Cast<DataColumn>();

        var dictionaryList = dtObject.AsEnumerable()
            .Select(dataRow => columns
                .Select(column =>
                    new { Column = column.ColumnName, Value = dataRow[column] })
                         .ToDictionary(data => data.Column, data => data.Value)).ToList().ToArray();

        return dictionaryList.ToList<IDictionary>();
    }

3) Then loop through List of IDictionary type and add it into type of List of Dynamic type in order to bind it with webgrid:

public List<dynamic> GetData()
    {
        var resultset = ConvertToDictionary(GetResultReport());

        var result = new List<dynamic>();

        foreach (var emprow in resultset)
        {
            var row = (IDictionary<string, object>)new ExpandoObject();
            Dictionary<string, object> eachRow = (Dictionary<string, object>)emprow;

            foreach (KeyValuePair<string, object> keyValuePair in eachRow)
            {
                row.Add(keyValuePair);
            }
            result.Add(row);
        }

        return result;
    }

4) WebGrid Binding Code in view (.cshtml):

                        WebGrid grid = new WebGrid(Model.OfficerOverViewList, rowsPerPage: 5);

                    @grid.GetHtml(tableStyle: "webgrid",
                            alternatingRowStyle: "webgrid-alternating-row",
                            headerStyle: "webgrid-header",
                            footerStyle: "webgrid-footer",
                            selectedRowStyle: "webgrid-selected-row",
                            rowStyle: "webgrid-row-style");