解析动态SQL在C＃和ASP.Net MVC结合的WebGrid [英] Parsing Dynamic SQL in C# and binding to WebGrid in ASP.Net MVC

问题描述

解析动态SQL语句在C＃和ASP.Net MVC
结合的WebGrid我有一个使用支点产生结果和该结果可能是这样的动态SQL查询：

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

有可能分裂的n个。我想在ASP.NET MVC的WebGrid控制该数据绑定。

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:

我的C＃代码如下C $ C>动态列表= db.ExecuteStoreQuery<动态方式>（EXEC [DBO] [proc_GetData]）了ToList（）;
返回列表;

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

我要绑定这个名单在ASP.NET MVC中的WebGrid，下面我的WebGrid代码：

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.

推荐答案

您必须在ExecuteStoreQuery（固定属性一起级）指定类型。你有，你可以用另一种替代方案。我解释这四个简单的步骤：

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）检索结果集到数据表

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）转换数据表到IDictionary的类型的列表

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）然后通过IDictionary的类型列表循环，并将其添加到类型列表为了与绑定的WebGrid它的动态类型：

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鉴于绑定代码（.cshtml）：

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");

这篇关于解析动态SQL在C＃和ASP.Net MVC结合的WebGrid的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！