Asp.net 数据透视表

Question

我的客户在他的电子商务数据库中有两张表

No(PK), PropertyName

，

No(PK), ProductNo(FK), PropertyNo(FK), Value

他只是想让我制作一张类似的表，

+-----------+-------------+--------------+------------+
|           | Property 1  | Property 2   | .. all properties-> | 
+-----------+-------------+--------------+------------+
| Product1  |      x      |     4        |      x     |
| Product2  |      2      |     x        |      1     |
| Product3  |      x      |     x        |      x     |
|   ...     |             |              |            |
| (all products)          |              |            |
+----+-------------+---------------------+------------+

我试图通过中继器制作它，但我做不到。我怎样才能实现它？

---

我放弃并以@Bala R 的身份提出了解决方案 但有一点变化......

示例类

public class list {
        public int No { get; set; }
        public string PropertyName { get; set; }
    }

    public class list2 {
        public int ProductNo { get; set; }
        public int PropertyNo { get; set; }
        public int Value { get; set; }

    }
    public class list3 {
        public string ProductName { get; set; }
        public int No { get; set; }

    }

示例列表

        List<list> propertyList = new List<list>();
        List<list2> propertyProductList = new List<list2>();
        List<list3> productList = new List<list3>();
        propertyList.Add(new list { No = 1, PropertyName=  "Property 1" });
        propertyList.Add(new list { No = 2, PropertyName = "Property 2" });
        propertyList.Add(new list { No = 3, PropertyName = "Property 3" });
        propertyList.Add(new list { No = 4, PropertyName = "Property 4" });

        propertyProductList.Add(new list2 { ProductNo = 1,  PropertyNo = 1, Value = 3 });
        propertyProductList.Add(new list2 { ProductNo = 2, PropertyNo = 3, Value = 13 });
        propertyProductList.Add(new list2 { ProductNo = 2, PropertyNo = 2, Value = 8 });
        propertyProductList.Add(new list2 { ProductNo = 3, PropertyNo = 2, Value = 6 });
        propertyProductList.Add(new list2 { ProductNo = 4, PropertyNo = 1, Value = 2 });
        propertyProductList.Add(new list2 { ProductNo = 3, PropertyNo = 1, Value = 55 });


        productList.Add(new list3 { No = 1, ProductName = "Ball" });
        productList.Add(new list3 { No = 2, ProductName = "Book" });
        productList.Add(new list3 { No = 3, ProductName = "Pencil" });
        productList.Add(new list3 { No = 4, ProductName = "TV" });

和解决方案，

        var resultSet = (from c in list2
                         group c by c.ProductNo into g
                          select new {
                              ProductNo = g.Key,
                              Value = g
                          }).ToList();
        DataTable dt = new DataTable();
        dt.Columns.Add(new DataColumn("Products"));
        foreach (var item in list) {
            dt.Columns.Add(new DataColumn() { ColumnName = item.PropertyName });
        }
        foreach (var item in resultSet) {
            DataRow dr = dt.NewRow();
            dr["Products"] = list3.First(p=> p.No== item.ProductNo).ProductName;
            foreach (var item2 in item.Value) {
                dr[list.First(l=>l.No == item2.PropertyNo).PropertyName] = item2.Value;
            }
            dt.Rows.Add(dr);
        }
        dataGrid1.DataSource = dt;
        dataGrid1.DataBind();

Answer 1

您可以创建这样的类

    class Product
    {
        public int ProductNo { get; set; }
        public string ProductName { get; set; }
    }

    class Property
    {
        public int PropertyNo { get; set; }
        public string PropertyName { get; set; }
    }

    class Value
    {
        public int ProductPropertyNo { get; set; }
        public int ProductNo { get; set; }
        public int PropertyNo { get; set; }
        public string Value { get; set; }
    }

并加载枚举

        IEnumerable<Product> products = GetProducts();
        IEnumerable<Property> properties = GetProperties();
        IEnumerable<Value> values = GetValues();

，并对 DataTable 执行类似的操作

        DataTable dt = new DataTable();

        dt.Columns.Add(new DataColumn("ProductName"));

        foreach (var propNo in values.Select(v => v.PropertyNo).Distinct())
        {
            dt.Columns.Add(
                new DataColumn(properties.Where(p => p.PropertyNo == propNo).First().PropertyName));


        }

        foreach (var prodNo in  values.Select(v => v.ProductNo).Distinct())
        {
            Product  prod = products.Where(p => p.ProductNo == prodNo).First();

            DataRow dr = dt.NewRow();
            dr["ProductName"] = prod.ProductName;
            foreach (var value in values.Where(v => v.ProductNo == prodNo))
            {
                Property prop = properties.Where(p => p.PropertyNo == value.PropertyNo).First();
                dr[prop.PropertyName] = value.Value;
            }
        }

Answer 2

我不是 DataTable 的忠实粉丝，但在本例中，这是实现这一目标的最简单方法。首先创建一个列“Product”，然后在DataTable 上为每个属性创建一个新列。
为您的所有产品添加DataRows，并在相应的属性列中填写值。

然后将DataTable 绑定到DataGrid。

Answer 3

using Modabber.Web.Common;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web.UI;

namespace Modabber.Web.Modules.WorkBook.Generator
{
    public partial class PercentStudyHomeworkStudentRpt : Page
    {
        public List<Rpt> result { get; set; } = new List<Rpt>();

        public string HideAverage { get; set; }

        protected override void OnInit(EventArgs e)
        {
            base.OnInit(e);
            InitializeComponent();
        }

        private void InitializeComponent()
        {
            Load += Page_Load;
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            // get the result list from DB
            result.Add(new Rpt { CourseTitle = "فیزیک", Grade = 10, Taraz = 20 });
            result.Add(new Rpt { CourseTitle = "2ریاضی", Grade = 30, Taraz = 40 });

            // generate these courses from result list
            var courses = new List<string>() { "2ریاضی", "فیزیک" };
            rptrCourses1.DataSource = from c in courses select new { Title = c };
            rptrCourses1.DataBind();
            rptrCourses2.DataSource = from c in courses select new { Title = c };
            rptrCourses2.DataBind();
            rptrCourses3.DataSource = from c in courses select new { Title = c };
            rptrCourses3.DataBind();

            bool isShowTaraz = true;
            if (isShowTaraz == false)
                HideAverage = "display:none";
        }

        protected decimal GetGrade(string courseTitle)
        {
            return result.First(r => r.CourseTitle == courseTitle).Grade;
        }

        protected decimal GetTaraz(string courseTitle)
        {
            return result.First(r => r.CourseTitle == courseTitle).Taraz;
        }

    }

    public class Rpt
    {
        public string CourseTitle { get; set; }
        public decimal Grade { get; set; }
        public decimal Taraz { get; set; }
    }
}

aspx：

<%@ Page Language="C#" AutoEventWireup="false" CodeBehind="PercentStudyHomeworkStudentRpt.aspx.cs"
    Inherits="Modabber.Web.Modules.WorkBook.Generator.PercentStudyHomeworkStudentRpt" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<style type="text/css">
    .ltrClass {
        direction: ltr;
    }

    .auto-style1 {
        width: 50%;
    }

    table {
        /*font-family: arial, sans-serif;*/
        border-collapse: collapse;
        width: 100%;
        direction: rtl;
        font: 9pt 'b roya',roya;
    }

    th {
        border: 1px solid;
        padding: 0px;
        text-align: center;
        font-weight: bold;
        background-color: lightgray;
        font: 9pt 'b roya',roya;
        max-height: 10px;
    }

    td {
        border: 1px solid;
        padding: 0px;
        text-align: center;
        font: 9pt 'b roya',roya;
    }
</style>
<head runat="server"></head>
<body style="direction: rtl;">
    <div style="text-align: center; margin-bottom: 5px;">نتایج کسب شده آزمونها</div>
    <table style="width: 100%; /*margin-top: 15px; */ font: 8pt 'b roya',roya;">
        <tr>
            <td></td>
            <asp:Repeater ID="rptrCourses1" runat="server">
                <ItemTemplate>
                    <td><%# Eval("Title")  %></td>
                </ItemTemplate>
            </asp:Repeater>
        </tr>
        <tr>
            <td>درصد</td>
            <asp:Repeater ID="rptrCourses2" runat="server">
                <ItemTemplate>
                    <td><%# GetGrade(Eval("Title").ToString())  %></td>
                </ItemTemplate>
            </asp:Repeater>
        </tr>
        <tr style="<%= HideAverage %>">
            <td >تراز</td>
            <asp:Repeater ID="rptrCourses3" runat="server">
                <ItemTemplate>
                    <td><%# GetTaraz(Eval("Title").ToString())  %></td>
                </ItemTemplate>
            </asp:Repeater>
        </tr>
    </table>
</body>
</html>

代码隐藏：

protected global::System.Web.UI.WebControls.Repeater rptrCourses1;
protected global::System.Web.UI.WebControls.Repeater rptrCourses2;
protected global::System.Web.UI.WebControls.Repeater rptrCourses3;