使用动态数据(商品)管理查询

本文关键字:管理 查询 商品 动态 数据 | 更新日期: 2025-01-25 10:36:54

我在数据库中有一个带有动态记录的表。我用静态数据(商品)得到了正确的结果,但我需要用动态数据得到结果。

数据库表为:

Id  Month  Commodity  Amount
----------------------------
1   May     wheat      100
2   May     rice       200
3   June    wheat      400
4   July    maize      100
5   June    wheat      100

我的结果:

 Month      wheat    rice    maize
 --------------------------------
 May        100      200     
 June       500
 July                        100    

我的aspx代码:

<asp:GridView ID="grdData" runat="server">
</asp:GridView>

和aspx.cs代码

protected void Page_Load(object sender, EventArgs e)
{
    if (!Page.IsPostBack)
    {
        getdata();
    }
}
public void getdata()
{
    using (GridFormatEntities context = new GridFormatEntities())
    {
        var result = (from r in context.tbl_Commodity
                          select new
                          {
                              Id = r.Id,
                              Month = r.Month,
                              Commodity = r.Commodity,
                              Amount = r.Amount
                          })
             .GroupBy(r => r.Month)
             .Select(r => new
             {
                 Month = r.Key,
                 Wheat = r.Where(x => x.Commodity == "Wheat").Sum(x => x.Amount),
                 Rice = r.Where(x => x.Commodity == "Rice").Sum(x => x.Amount),
                 maize= r.Where(x => x.Commodity == "maize").Sum(x => x.Amount),
             }).ToList();
        grdData.DataSource = result;
        grdData.DataBind();
    }
}

在上面的查询中,它是静态的(小麦、大米和玉米),但我需要这些商品动态。。。请帮助我如何管理相同的动态数据(商品)。

使用动态数据(商品)管理查询

如果您可以使用数据表而不是列表,下面的代码将对您有所帮助。请记住,这只是一个示例代码,可以帮助您理解这个概念。请根据您的输入数据检查循环的效率。

它填充表,您可以将该表分配给数据源。

var list = new[]
                           {
                               new { Id = 1, Month = 2, Commodity = "Wheat", Amount = 20 },
                               new { Id = 2, Month = 2, Commodity = "Maize", Amount = 30 },
                               new { Id = 3, Month = 2, Commodity = "Barley", Amount = 30 },
                               new { Id = 4, Month = 1, Commodity = "Wheat", Amount = 20 },
                               new { Id = 5, Month = 1, Commodity = "Maize", Amount = 30 },
                               new { Id = 6, Month = 3, Commodity = "Barley", Amount = 30 }
                           };
            // group data by month and keep all rows
            var data =
                list.GroupBy(r => r.Month)
                    .Select(
                        r =>
                        new 
                            {
                                Month = r.Key,
                                Data = r.ToList()
                            })
                    .ToList();
            // get a list of all available commodities
            var allColumns = data.SelectMany(d => d.Data.Select(s => s.Commodity)).Distinct().ToList();
            // create a table
            var datatable = new DataTable();
            datatable.Columns.Add(new DataColumn("Month"));
            // add all commodities as columns
            allColumns.ForEach(c=>datatable.Columns.Add(new DataColumn(c)));
            // create 1 row for each month group and fill the columns
            data.ForEach(
                d =>
                    {
                        var row = datatable.NewRow();
                        row["Month"] = d.Month;
                        allColumns.ForEach(c => row[c] = d.Data.Where(g => g.Commodity == c).Sum(g => g.Amount));
                        datatable.Rows.Add(row);
                    });