如何在VB.NET中用Dapper多查询不同路径的MS-Access至DataGridView

我有三个不同路径的MS-Access文件，路径如下：

TABLEA = D:\TABLEA.accdb
TABLEB = Z:\DATA\Malfin 07\TABLEB.accdb
TABLEC = Z:\DATA\Malfin D2\TABLEC.accdb

每个数据库中都有名为User的表，表结构及数据如下：

TABLEA的User表 #

TABLEB的User表 #

TABLEC的User表 #

期望将三个表的FIRSTNAME和LASTNAME数据合并后展示到DataGridView中，最终结果如下：

现有VB.NET代码如下：

Public Class Form1
    Public Function GetOledbConnectionString() As String
        Return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=;Persist Security Info=False;"
    End Function
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Using conn = New OleDbConnection(GetOledbConnectionString)
            Using data = conn.QueryMultiple("select * from User", Nothing)
            End Using
        End Using

    End Sub
End Class
Public Class User
    Public Property ID() As Integer
    Public Property FIRSTNAME() As String
    Public Property LASTNAME() As String
End Class

1. 修改连接字符串生成方法 #

让GetOledbConnectionString接收数据库路径参数，动态生成对应连接字符串：

Public Function GetOledbConnectionString(dbPath As String) As String
    Return $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={dbPath};Persist Security Info=False;"
End Function

2. 定义数据库路径列表 #

将三个数据库的路径存入列表，方便遍历：

Dim dbPaths As New List(Of String) From {
    "D:\TABLEA.accdb",
    "Z:\DATA\Malfin 07\TABLEB.accdb",
    "Z:\DATA\Malfin D2\TABLEC.accdb"
}

3. 遍历查询并合并数据 #

创建集合存储所有查询到的User数据，遍历每个数据库路径，建立连接查询数据后添加到集合：

Dim allUsers As New List(Of User)()

For Each path In dbPaths
    Using conn = New OleDbConnection(GetOledbConnectionString(path))
        conn.Open() ' 必须打开连接才能执行查询
        Dim users = conn.Query(Of User)("SELECT FIRSTNAME, LASTNAME FROM [User]")
        allUsers.AddRange(users)
    End Using
Next

注意：User是Access的保留字，查询时需要用方括号[User]包裹表名避免语法错误；只查询需要的字段可减少数据传输。

4. 绑定数据到DataGridView #

将合并后的集合绑定到DataGridView，同时可隐藏不需要的列：

DataGridView1.DataSource = allUsers
' 隐藏ID列，匹配期望结果的显示要求
If DataGridView1.Columns.Contains("ID") Then
    DataGridView1.Columns("ID").Visible = False
End If

Imports Dapper
Imports System.Data.OleDb

Public Class Form1
    Public Function GetOledbConnectionString(dbPath As String) As String
        Return $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={dbPath};Persist Security Info=False;"
    End Function

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ' 定义所有数据库路径
        Dim dbPaths As New List(Of String) From {
            "D:\TABLEA.accdb",
            "Z:\DATA\Malfin 07\TABLEB.accdb",
            "Z:\DATA\Malfin D2\TABLEC.accdb"
        }

        Dim allUsers As New List(Of User)()

        For Each path In dbPaths
            Using conn = New OleDbConnection(GetOledbConnectionString(path))
                conn.Open()
                ' 查询需要的字段，注意表名用方括号包裹
                Dim users = conn.Query(Of User)("SELECT FIRSTNAME, LASTNAME FROM [User]")
                allUsers.AddRange(users)
            End Using
        Next

        ' 绑定到DataGridView
        DataGridView1.DataSource = allUsers
        ' 隐藏ID列
        If DataGridView1.Columns.Contains("ID") Then
            DataGridView1.Columns("ID").Visible = False
        End If
    End Sub
End Class

Public Class User
    Public Property ID() As Integer
    Public Property FIRSTNAME() As String
    Public Property LASTNAME() As String
End Class

• 确保项目已通过NuGet安装Dapper包
• 目标机器需安装Microsoft Access Database Engine 2010 Redistributable（或对应版本），否则OLEDB连接会失败
• 确认数据库路径正确，含空格的路径无需额外转义，字符串插值会自动处理

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