Sql 用VB.NET在Dapper中实现MS Access数据库顺序透视

你的Loaddata方法总是返回Tableproduct个对象，这是你的DataGridView唯一能看到的东西.您必须创建一个表示透视结果的类，并从Loaddata方法返回它.

Public Class PivotedProduct
    Public Property CodeProduct() As String
    Public Property Colour() As String

    Public Property S() As Integer
    Public Property M() As Integer
    Public Property L() As Integer
    Public Property XL() As Integer

    Public Property Total() As Integer
End Class

当然，这将始终显示网格中的所有大小列.我不确定你所说的"顺序"是什么意思.如果你的意思是按产品分组，那么DataGridView就无法实现这一点.但也有一些支持分组的第三方网格.

如果您希望只显示每个分组产品的相关大小列，那么使用网格控件将很难或不可能做到这一点.但通过一个报告工具和一些逻辑来解决这个问题是可能的.

下面是一个在网格中加载数据并隐藏空列的示例(但一次只针对一个产品).我创建了一个服务，加载一些用于测试的假测试数据:

Public Class PivotedProductService
    Public Function LoadData(CodeProduct As String) As IEnumerable(Of PivotedProduct)
        Return New List(Of PivotedProduct) From {
            New PivotedProduct With {.CodeProduct = "B", .Colour = "Blue", .S = 20, .Total = 20},
            New PivotedProduct With {.CodeProduct = "B", .Colour = "White", .XL = 15, .Total = 15}
        }
    End Function
End Class

我的表单代码如下所示:

Public Class Form1
    Dim pps As New PivotedProductService

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        DataGridView1.DataSource = pps.LoadData("B")
        HideEmptySizeColumns()
    End Sub

    Private Sub HideEmptySizeColumns()
        For i As Integer = 2 To 5
            Dim allZeros = True
            For Each row As DataGridViewRow In DataGridView1.Rows
                If row.Cells(i).Value <> 0 Then
                    allZeros = False
                    Exit For
                End If
            Next
            If allZeros Then
                DataGridView1.Columns(i).Visible = False
            End If
        Next
    End Sub
End Class

结果看起来是这样的:

使用LINQ可以简化隐藏列的代码，如下所示

Private Sub HideEmptySizeColumns()
    For i As Integer = 2 To 5
        Dim allZeros = Not (From row In DataGridView1.Rows
                            Where row.Cells(i).Value <> 0).Any()
        If allZeros Then
            DataGridView1.Columns(i).Visible = False
        End If
    Next
End Sub