VB.NET中使用Dapper操作MS Access：连接管理与CRUD实现咨询

一、连接管理相关问题 #

是否需要使用using语句释放连接？ #

必须使用。OleDbConnection实现了IDisposable接口，using语句能确保连接在代码块结束后自动释放，避免连接泄漏，这是.NET中资源管理的最佳实践。

是否需要手动执行连接的打开与关闭？ #

Dapper会自动处理连接状态：如果连接当前是关闭状态，Dapper执行查询/命令时会自动打开它，执行完成后会恢复原状态（原本关闭则自动关闭，原本打开则保持打开）。但更推荐配合using手动控制连接生命周期，能避免潜在的连接状态异常。

二、当前代码的错误检查 #

你的代码存在以下关键问题：

• 连接生命周期管理错误：UserService将_conn作为类级别成员变量，会导致连接长期持有，易引发连接泄漏、并发访问异常。应在每次操作时创建连接并通过using管理。
• SQL注入风险：InserUser方法用字符串拼接生成SQL，存在严重的SQL注入漏洞，恶意用户可通过构造特殊值篡改SQL逻辑。
• 方法名拼写错误：InserUser应为InsertUser，不符合命名规范且易引发调用错误。
• Access SQL语法错误：Access不支持反引号（）作为标识符分隔符，需用方括号（[]`）包裹表名和字段名。

三、OLEDB下高效使用Dapper完成CRUD的指导 #

核心原则 #

• 始终用using管理连接，确保资源及时释放
• 强制使用参数化查询，避免SQL注入同时提升查询性能（Access会缓存参数化查询计划）
• 按需选择Dapper方法，减少不必要的内存开销

修正后的完整CRUD示例 #

1. 修正后的UserService

Imports Dapper
Imports System.Data.OleDb

Public Class UserService
    Private ReadOnly _connectionString As String = DbContext.GetOledbConnectionString()

    ' 查询所有用户
    Public Function GetAllUsers() As IEnumerable(Of User)
        Using conn As New OleDbConnection(_connectionString)
            Dim sql = "SELECT Id, Name, Email, Password FROM Users"
            Return conn.Query(Of User)(sql).ToList()
        End Using
    End Function

    ' 根据ID查询单个用户
    Public Function GetUserById(id As Integer) As User
        Using conn As New OleDbConnection(_connectionString)
            Dim sql = "SELECT Id, Name, Email, Password FROM Users WHERE Id = @Id"
            Return conn.QuerySingleOrDefault(Of User)(sql, New With {.Id = id})
        End Using
    End Function

    ' 插入用户（参数化版本）
    Public Sub InsertUser(user As User)
        Using conn As New OleDbConnection(_connectionString)
            Dim sql = "INSERT INTO Users ([Name], [Email], [Password]) VALUES (@Name, @Email, @Password)"
            conn.Execute(sql, user)
        End Using
    End Sub

    ' 更新用户
    Public Sub UpdateUser(user As User)
        Using conn As New OleDbConnection(_connectionString)
            Dim sql = "UPDATE Users SET [Name] = @Name, [Email] = @Email, [Password] = @Password WHERE Id = @Id"
            conn.Execute(sql, user)
        End Using
    End Sub

    ' 删除用户
    Public Sub DeleteUser(id As Integer)
        Using conn As New OleDbConnection(_connectionString)
            Dim sql = "DELETE FROM Users WHERE Id = @Id"
            conn.Execute(sql, New With {.Id = id})
        End Using
    End Sub
End Class

2. 修正后的Pguser控件代码

Partial Public Class Pguser
    Inherits UserControl
    Private ReadOnly _uService As New UserService()

    Public Sub New()
        If Program.IsInDesignMode(Me) Then
            Return
        End If
        InitializeComponent()
        LoadData()
    End Sub

    Private Sub LoadData()
        Dim users = _uService.GetAllUsers()
        grid.DataSource = users
    End Sub
End Class

额外高效技巧 #

• 批量操作：插入/更新多条数据时，直接传入参数集合给Execute方法，比单条循环执行更高效：

  Public Sub BulkInsertUsers(users As IEnumerable(Of User))
      Using conn As New OleDbConnection(_connectionString)
          Dim sql = "INSERT INTO Users ([Name], [Email], [Password]) VALUES (@Name, @Email, @Password)"
          conn.Execute(sql, users)
      End Using
  End Sub
  

• 事务处理：多步操作时使用事务确保数据一致性：

  Public Sub TransferUserData(oldId As Integer, newUser As User)
      Using conn As New OleDbConnection(_connectionString)
          conn.Open()
          Using tran = conn.BeginTransaction()
              Try
                  DeleteUser(oldId)
                  InsertUser(newUser)
                  tran.Commit()
              Catch
                  tran.Rollback()
                  Throw
              End Try
          End Using
      End Using
  End Sub
  

• 避免SELECT *：明确指定查询字段，减少数据传输量，提升查询速度。

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