如何在VB.NET中重置BindingSource/BindingList序号并更新至数据库

在VB.NET开发中，删除DataGridView中的行后需要重置BindingSource/BindingList的序号，并将更新后的序号同步到数据库，最初的代码实现存在错误，无法完成正确的数据库同步。

错误实现代码 #

Public Class Formwithdb
    Private bindingSource As BindingSource = Nothing
    Dim productservice As New PurchaseService()
    Private Function GetProduct() As Product
        Return If(DataGridView1.SelectedCells.Count = 0, Nothing, TryCast(DataGridView1.SelectedCells(0).OwningRow.DataBoundItem, Product))
    End Function
    Private Sub Formwithdb_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        bindingSource = New BindingSource With {.DataSource = New BindingList(Of Product)(CType(productservice.Getpurchase(), IList(Of Product)))} 
        DataGridView1.DataSource = bindingSource
        DataGridView1.Columns("No").ReadOnly = True
        DataGridView1.Columns("Codeproduct").ReadOnly = True
        DataGridView1.Columns("Qty").ReadOnly = True
        DataGridView1.Columns("Qty_Stock").ReadOnly = True
        DataGridView1.Columns("Coldel").ReadOnly = True
    End Sub
    Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
        Dim dgv = DirectCast(sender, DataGridView)
        If e.RowIndex >= 0 AndAlso
        dgv.Columns("ColDel") Is dgv.Columns(e.ColumnIndex) Then
            Dim item = TryCast(dgv.Rows(e.RowIndex).DataBoundItem, Product)
            If item IsNot Nothing AndAlso
                MessageBox.Show("Confirm ...", "App", MessageBoxButtons.YesNo) = DialogResult.Yes Then
                Try
                    Dim i = 1
                    Dim bs = TryCast(bindingSource.DataSource, BindingList(Of Product))
                    Dim codeproduct = bs.FirstOrDefault(Function(x) x.Codeproduct = GetProduct.Codeproduct)
                    productservice.Deleteproduct(codeproduct)
                    bs.Remove(item)
                    For Each row As Product In bs
                        row.No = i
                        i += 1

' 以下代码实现错误
                        Dim codeproductdetail = bs.FirstOrDefault(Function(x) row.Codeproduct = GetProduct.Codeproduct)
                        productservice.Updateproductno(codeproductdetail)
                    Next
                    DataGridView1.Refresh()
                    MessageBox.Show("Successfully")
                Catch ex As Exception
                    MessageBox.Show(ex.Message, "App", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                End Try
            End If
        End If
    End Sub
End Class
Public Class PurchaseService
   Public Sub Updateproductno(ByVal Obj As Product)
        Dim sql = $"UPDATE Product Set [No] = {Obj.No} WHERE Codeproduct ='{Obj.Codeproduct}';"
        Using _conn = New OleDbConnection(GetOledbConnectionString())
            _conn.Execute(sql)
        End Using
    End Sub
End Class

错误分析 #

错误代码中，在遍历BindingList更新序号时，错误地通过GetProduct.Codeproduct去匹配当前行的Codeproduct来获取要更新的对象：

Dim codeproductdetail = bs.FirstOrDefault(Function(x) row.Codeproduct = GetProduct.Codeproduct)

这会导致无法正确定位到当前需要更新的Product对象，甚至返回空值，进而无法完成数据库的序号同步操作。

修正后的代码 #

Private Sub DataGridView1_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
        Dim dgv = DirectCast(sender, DataGridView)
        If e.RowIndex >= 0 AndAlso
        dgv.Columns("ColDel") Is dgv.Columns(e.ColumnIndex) Then
            Dim item = TryCast(dgv.Rows(e.RowIndex).DataBoundItem, Product)
            If item IsNot Nothing AndAlso
                MessageBox.Show("Confirm ...", "App", MessageBoxButtons.YesNo) = DialogResult.Yes Then
                Try
                    Dim i = 1
                    Dim bs = TryCast(bindingSource.DataSource, BindingList(Of Product))
                    Dim codeproduct = bs.FirstOrDefault(Function(x) x.Codeproduct = GetProduct.Codeproduct)
                    productservice.Deleteproduct(codeproduct)
                    bs.Remove(item)
                    For Each row As Product In bs
                        row.No = i
                        i += 1
                        ' 直接使用当前行的No和Codeproduct创建更新对象
                        Dim Update = New Product With {
                                 .No = row.No,
                                 .Codeproduct = row.Codeproduct}
                        productservice.Updateproductno(Update)
                    Next
                    DataGridView1.Refresh()
                    MessageBox.Show("Successfully")
                Catch ex As Exception
                    MessageBox.Show(ex.Message, "App", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                End Try
            End If
        End If
    End Sub
Public Class PurchaseService
   Public Sub Updateproductno(ByVal Obj As Product)
        Dim sql = $"UPDATE Product Set [No] = {Obj.No} WHERE Codeproduct ='{Obj.Codeproduct}';"
        Using _conn = New OleDbConnection(GetOledbConnectionString())
            _conn.Execute(sql)
        End Using
    End Sub
End Class

修正要点 #

遍历BindingList中的每个Product对象时，直接利用当前行已更新的No值和自身的Codeproduct创建新的Product实例，传递给Updateproductno方法，确保每个行的序号都能准确同步到数据库中，避免了原代码中对象匹配错误的问题。

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