如何在VB.NET中通过Dapper从DataGridView更新MS-Access数据库

问题背景 #

此前通过循环DataTable实现SQL插入，现需改用Dapper完成MS-Access数据库的更新操作（涉及商品库存数量变动），并完善DataGridView的数据更新逻辑。

现有代码问题分析 #

1. 主表更新时未传入Invno参数，导致WHERE条件无效，无法定位到要更新的单据
2. 使用字符串拼接SQL存在SQL注入风险，且日期类型处理不规范
3. DataGridView明细更新逻辑未实现，未处理行数据的读取与提交
4. 未考虑事务一致性，主表与明细更新可能出现部分成功的情况

修正后的完整代码实现 #

窗体代码（Form2）

Public Class Form2
    Inherits Form
    Dim itrservice As New ItemtransfersService()

    Sub New()
        InitializeComponent()
    End Sub

    Public Sub New(item As ItemTransfers)
        Me.New()
        DataGridView1.DataSource = itrservice.GetItemTransfersDetail(item.Invno)
        DataGridView1.Columns(1).Visible = False
        TextBox1.Text = item.Invno
        TextBox2.Text = item.Created
    End Sub

    Private Sub BtnUpdate_Click(sender As Object, e As EventArgs) Handles BtnUpdate.Click
        If DataGridView1.RowCount = 0 Then
            Throw New Exception("请先输入商品数据")
        End If

        Dim invno = TextBox1.Text.Trim()
        If String.IsNullOrEmpty(invno) Then
            Throw New Exception("单据编号不能为空")
        End If

        ' 开启事务确保主表与明细更新一致性
        Using conn = New OleDbConnection(GetOledbConnectionString())
            conn.Open()
            Using transaction = conn.BeginTransaction()
                Try
                    ' 更新主表Stocksout
                    Dim mainObj = New ItemTransfers With {
                        .Invno = invno,
                        .Modified = DateTime.Now
                    }
                    itrservice.UpdateItemTransfers(mainObj, transaction)

                    ' 遍历DataGridView更新明细
                    For Each row As DataGridViewRow In DataGridView1.Rows
                        If row.IsNewRow Then Continue For ' 跳过未输入的新行

                        Dim detail = New ItemTransfersDetail With {
                            .Invno = invno,
                            .No = Convert.ToInt32(row.Cells(0).Value),
                            .CodeProduct = row.Cells(2).Value?.ToString(),
                            .Barcode = row.Cells(3).Value?.ToString(),
                            .Colorcode = row.Cells(4).Value?.ToString(),
                            .Size = row.Cells(5).Value?.ToString(),
                            .Qty = Convert.ToInt32(row.Cells(6).Value)
                        }

                        itrservice.UpdateItemTransfersdetail(detail, transaction)

                        ' --------------------------
                        ' 【必填业务逻辑】同步库存变动
                        ' 根据你的库存规则补充，例如调拨时增减对应仓库库存
                        ' itrservice.AdjustInventory(detail.CodeProduct, detail.Qty, transaction)
                        ' --------------------------
                    Next

                    transaction.Commit()
                    MessageBox.Show("更新完成")
                Catch ex As Exception
                    transaction.Rollback()
                    Throw New Exception("更新失败：" & ex.Message)
                End Try
            End Using
        End Using
    End Sub
End Class

服务层代码（ItemtransfersService）

Public Class ItemtransfersService
    ' 更新主表：使用参数化查询避免注入，支持事务
    Public Sub UpdateItemTransfers(ByVal obj As ItemTransfers, transaction As OleDbTransaction)
        Dim sql = "UPDATE Stocksout SET Modified = @Modified WHERE Invno = @Invno"
        transaction.Connection.Execute(sql, obj, transaction)
    End Sub

    ' 更新明细表：直接传入对象自动匹配参数，支持事务
    Public Sub UpdateItemTransfersdetail(ByVal obj As ItemTransfersDetail, transaction As OleDbTransaction)
        Dim sql = "UPDATE Stocksoutdetail 
                   SET CodeProduct = @CodeProduct, Barcode = @Barcode, 
                       Colorcode = @Colorcode, Size = @Size, Qty = @Qty 
                   WHERE Invno = @Invno AND No = @No"
        transaction.Connection.Execute(sql, obj, transaction)
    End Sub

    ' 【示例】库存调整方法（根据业务需求实现）
    ' Public Sub AdjustInventory(productCode As String, qty As Integer, transaction As OleDbTransaction)
    '     Dim sql = "UPDATE Inventory SET Qty = Qty - @Qty WHERE ProductCode = @ProductCode"
    '     transaction.Connection.Execute(sql, New With {.ProductCode = productCode, .Qty = qty}, transaction)
    ' End Sub

    ' 获取数据库连接字符串（需自行实现）
    Private Function GetOledbConnectionString() As String
        Return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\YourDatabase.accdb;"
    End Function
End Class

关键改进点 #

• 参数化查询：彻底避免SQL注入风险，同时规范日期、数值类型的处理
• 事务支持：确保主表与明细更新原子性，防止数据不一致
• 空值处理：使用?.ToString()避免DataGridView单元格空值引发的异常
• 行有效性判断：跳过DataGridView的空新行，避免无效更新
• 库存同步：预留库存调整逻辑位置，确保库存数量与单据变动一致

界面视图 #

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