如何在VB.NET中通过Dapper实现MS Access数据库的有序透视？

核心问题分析 #

1. Access的静态PIVOT子句默认按列值的字母顺序排列透视列，不会自动遵循SizeProduct.Sequence字段的排序规则
2. Dapper本身不影响透视逻辑，但SQL构造错误、参数传递不当或DataGridView绑定方式不对，都会导致结果展示异常

解决方案步骤 #

1. 动态生成按Sequence排序的透视列列表 #

先从SizeProduct表中查询出按Sequence升序排列的SizeName，以此确定透视列的正确顺序：

' 获取排序后的尺寸名称列表
Dim sortedSizeNames As List(Of String) = conn.Query(Of String)(
    "SELECT SizeName FROM SizeProduct ORDER BY Sequence ASC"
).ToList()

2. 构造动态透视SQL #

将排序后的列名拼接成符合Access语法的格式，替换静态SQL中的列定义部分：

' 用[]包裹列名，避免特殊字符引发语法错误
Dim pivotColumns As String = String.Join(", ", sortedSizeNames.Select(Function(s) $"[{s}]"))

' 构造完整动态透视SQL（假设业务表为ProductSales，关联SizeProduct）
Dim pivotSql As String = $@"
    TRANSFORM SUM(ProductSales.Quantity) AS TotalQuantity
    SELECT ProductSales.ProductName
    FROM ProductSales
    INNER JOIN SizeProduct ON ProductSales.SizeID = SizeProduct.SizeID
    GROUP BY ProductSales.ProductName
    PIVOT SizeProduct.SizeName IN ({pivotColumns})
"

3. Dapper执行查询并绑定到DataGridView #

因为透视列是动态的，用DynamicDictionary接收结果后转换为DataTable，再绑定到DataGridView：

' 确保连接打开
If conn.State <> ConnectionState.Open Then conn.Open()

' 执行透视查询
Dim pivotResult As List(Of DynamicDictionary) = conn.Query(Of DynamicDictionary)(pivotSql).ToList()

' 转换为DataTable适配DataGridView
Dim dt As New DataTable()
If pivotResult.Any() Then
    ' 添加列
    For Each key In pivotResult.First().Keys
        dt.Columns.Add(key)
    Next
    ' 添加行数据
    For Each row In pivotResult
        dt.Rows.Add(row.Values.ToArray())
    Next
End If

' 绑定到控件
DataGridView1.DataSource = dt
DataGridView1.AutoGenerateColumns = True

' 关闭连接
conn.Close()

4. 排查DataGridView异常 #

如果结果仍不正确，检查以下几点：

• 确认Access连接字符串格式正确：Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\YourPath\YourDb.accdb;
• 把动态生成的pivotSql复制到Access查询中测试，验证SQL本身是否正确
• 检查DataGridView的AutoGenerateColumns属性是否设为True

完整示例代码片段 #

Imports Dapper
Imports System.Data.OleDb
Imports System.Dynamic

' 窗体按钮点击事件示例
Private Sub btnExecutePivot_Click(sender As Object, e As EventArgs) Handles btnExecutePivot.Click
    Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\YourDatabase.accdb;"
    Using conn As New OleDbConnection(connString)
        ' 获取排序后的尺寸列
        Dim sortedSizeNames As List(Of String) = conn.Query(Of String)(
            "SELECT SizeName FROM SizeProduct ORDER BY Sequence ASC"
        ).ToList()

        If sortedSizeNames.Count = 0 Then
            MessageBox.Show("无可用的尺寸数据")
            Return
        End If

        ' 构造动态SQL
        Dim pivotColumns As String = String.Join(", ", sortedSizeNames.Select(Function(s) $"[{s}]"))
        Dim pivotSql As String = $@"
            TRANSFORM SUM(ps.Quantity) AS TotalQty
            SELECT ps.ProductName
            FROM ProductSales ps
            INNER JOIN SizeProduct sp ON ps.SizeID = sp.SizeID
            GROUP BY ps.ProductName
            PIVOT sp.SizeName IN ({pivotColumns})
        "

        ' 执行查询并转换为DataTable
        Dim pivotData As List(Of DynamicDictionary) = conn.Query(Of DynamicDictionary)(pivotSql).ToList()
        Dim dt As New DataTable()
        If pivotData.Any() Then
            For Each colName In pivotData.First().Keys
                dt.Columns.Add(colName)
            Next
            For Each row In pivotData
                dt.Rows.Add(row.Values.ToArray())
            Next
        End If

        ' 绑定到DataGridView
        DataGridView1.DataSource = dt
        DataGridView1.AutoResizeColumns()
    End Using
End Sub

内容的提问来源于stack exchange，提问作者user22579796