VB.NET ChecklistBox双向更新数据冲突问题求助

核心问题定位 #

1. Timer执行顺序完全搞反：当前RefreshTimer_Tick里先刷新数据到控件，中途还清空了ChecklistBox，最后才调用保存方法——等于保存的是空控件数据，直接覆盖了用户之前的修改。
2. 全删全插的同步方式太激进：每次保存都先删光对应记录再重新插入，只要刷新时机不对，必然出现数据覆盖。
3. 没区分手动修改和自动刷新场景：双向更新时，程序自动刷新和用户手动操作互相干扰，没有做状态隔离。

针对性修复方案 #

1. 立刻修正Timer执行顺序 #

把保存操作放到最前面，确保用户修改先落地，再执行刷新逻辑：

Private Sub RefreshTimer_Tick(sender As Object, e As EventArgs) Handles RefreshTimer.Tick
    Try
        ' 先保存用户修改，再处理刷新
        CB1ItemSaving()
        CLBSaves()
        
        ' 刷新相关操作放在保存之后
        RefreshTop5Data()
        CBL1Counter()
        Condition()
        ' 删掉CLBClear()，RefreshTop5Data应该负责正确加载数据，不用提前清空
    Catch ex As Exception
        ' 别吞异常，至少打个日志方便排查
        Console.WriteLine($"刷新出错: {ex.Message}")
    End Try
End Sub

2. 把全删全插改成增量更新 #

不用每次都删光数据，改成对比数据库和控件的差异，只做必要的增删改：

Sub CB1ItemSaving()
    Dim ctrlNo = lblctrlNo.Text
    Dim desc = lbldesc1.Text
    
    ' 先拉取数据库里已有的记录
    Dim existingItems As New Dictionary(Of String, Boolean)
    Dim getQuery = "SELECT Item, isSelected FROM [TEST-SERVER].[ExclusiveDataInfoCASH].[dbo].[tblChecklist] WHERE CTRLNo = @CTRLNo AND Description = @Description"
    
    Using conn = New SqlConnection(gSQlConn.ConnectionString)
        conn.Open()
        Using cmd As New SqlCommand(getQuery, conn)
            cmd.Parameters.AddWithValue("@CTRLNo", ctrlNo)
            cmd.Parameters.AddWithValue("@Description", desc)
            Using reader = cmd.ExecuteReader()
                While reader.Read()
                    existingItems(reader("Item").ToString()) = CBool(reader("isSelected"))
                End While
            End Using
        End Using
        
        ' 用MERGE语句做更新/插入二合一
        Dim upsertQuery = "MERGE INTO [TEST-SERVER].[ExclusiveDataInfoCASH].[dbo].[tblChecklist] AS Target
                           USING (VALUES (@CTRLNo, @Description, @Item, @isSelected)) AS Source (CTRLNo, Description, Item, isSelected)
                           ON Target.CTRLNo = Source.CTRLNo AND Target.Description = Source.Description AND Target.Item = Source.Item
                           WHEN MATCHED THEN UPDATE SET isSelected = Source.isSelected
                           WHEN NOT MATCHED THEN INSERT (CTRLNo, Description, Item, isSelected) VALUES (Source.CTRLNo, Source.Description, Source.Item, Source.isSelected);"
        
        For Each item In CLB1.Items.Cast(Of String)()
            Dim isChecked = CLB1.GetItemCheckState(CLB1.Items.IndexOf(item)) = CheckState.Checked
            
            Using cmd As New SqlCommand(upsertQuery, conn)
                cmd.Parameters.AddWithValue("@CTRLNo", ctrlNo)
                cmd.Parameters.AddWithValue("@Description", desc)
                cmd.Parameters.AddWithValue("@Item", item)
                cmd.Parameters.AddWithValue("@isSelected", isChecked)
                cmd.ExecuteNonQuery()
            End Using
            
            existingItems.Remove(item) ' 标记已处理的项
        Next
        
        ' 删除数据库有但控件里没有的项
        Dim deleteQuery = "DELETE FROM [TEST-SERVER].[ExclusiveDataInfoCASH].[dbo].[tblChecklist] WHERE CTRLNo = @CTRLNo AND Description = @Description AND Item = @Item"
        For Each removedItem In existingItems.Keys
            Using cmd As New SqlCommand(deleteQuery, conn)
                cmd.Parameters.AddWithValue("@CTRLNo", ctrlNo)
                cmd.Parameters.AddWithValue("@Description", desc)
                cmd.Parameters.AddWithValue("@Item", removedItem)
                cmd.ExecuteNonQuery()
            End Using
        Next
    End Using
End Sub

3. 加状态标记隔离手动/自动操作 #

搞个布尔变量标记当前是否在自动刷新，避免刷新时触发手动修改的保存逻辑：

Private isAutoRefreshing As Boolean = False

Private Sub RefreshTimer_Tick(sender As Object, e As EventArgs) Handles RefreshTimer.Tick
    Try
        isAutoRefreshing = True
        
        CB1ItemSaving()
        CLBSaves()
        
        RefreshTop5Data()
        CBL1Counter()
        Condition()
    Catch ex As Exception
        Console.WriteLine($"刷新出错: {ex.Message}")
    Finally
        isAutoRefreshing = False ' 不管成功失败都要重置状态
    End Try
End Sub

' 比如手动修改ChecklistBox的事件里加判断
Private Sub CLB1_ItemCheck(sender As Object, e As ItemCheckEventArgs) Handles CLB1.ItemCheck
    If Not isAutoRefreshing Then
        ' 这里可以处理手动修改后的即时保存，不用等Timer
    End If
End Sub

4. 修复SQL注入漏洞 #

原代码里的DELETE语句用了字符串拼接，容易被注入，改成参数化：

' 替换原CB1ItemSaving里的DELETE部分
Dim deleteQuery As String = "DELETE FROM [TEST-SERVER].[ExclusiveDataInfoCASH].[dbo].[tblChecklist] WHERE CTRLNo = @CTRLNo AND Description = @Description"
Using cmd As New SqlCommand(deleteQuery, gSQlConn)
    cmd.Parameters.AddWithValue("@CTRLNo", lblctrlNo.Text)
    cmd.Parameters.AddWithValue("@Description", lbldesc1.Text)
    cmd.ExecuteNonQuery()
End Using

额外优化建议 #

• 给数据库操作加事务，确保要么全成功要么全回滚：

  Using trans = conn.BeginTransaction()
      Try
          ' 所有SqlCommand都指定Transaction属性
          cmd.Transaction = trans
          ' ...执行操作
          trans.Commit()
      Catch
          trans.Rollback()
          Throw
      End Try
  End Using
  

• 别在循环里反复打开关闭连接，用Using块统一管理连接生命周期。

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