实现DataGridView行上下移动并同步更新SQL数据库

核心问题 #

你当前的实现存在两个关键错误：

1. SQL语句语法逻辑完全错误（比如set [Priority] =[Priority] > 1是赋值布尔值，而非调整优先级数值）；
2. 仅修改当前行的Priority值，没有实现与相邻行交换优先级的逻辑，导致跳行或无法移动的问题。

正确的思路是：交换选中行与目标相邻行的Priority值，这样就能保证仅移动一行，同时同步到数据库。

优化后的实现代码 #

关键改进点 #

• 仅处理选中的单行（避免无效遍历）
• 增加边界判断（上移不能是第一行，下移不能是最后一行）
• 使用事务保证两次更新的原子性，避免数据不一致
• 采用参数化查询，杜绝SQL注入风险
• 交换当前行与相邻行的Priority值，实现精准单行移动

Private Sub BntMoveUp_Click(sender As Object, e As EventArgs) Handles BntMoveUp.Click
    ' 获取选中行的索引
    Dim selectedRowIndex As Integer = -1
    For i As Integer = 0 To DataGridView2.Rows.Count - 1
        If Convert.ToBoolean(DataGridView2.Rows(i).Cells(0).Value) Then
            selectedRowIndex = i
            Exit For
        End If
    Next

    ' 边界校验
    If selectedRowIndex = -1 OrElse selectedRowIndex = 0 Then
        MessageBox.Show("请选中非第一行的数据进行上移操作")
        Return
    End If

    ' 获取当前行与上一行的主键和优先级
    Dim currentCid As Integer = Convert.ToInt32(DataGridView2.Rows(selectedRowIndex).Cells(1).Value)
    Dim currentPriority As Integer = Convert.ToInt32(DataGridView2.Rows(selectedRowIndex).Cells("Priority").Value)
    Dim prevCid As Integer = Convert.ToInt32(DataGridView2.Rows(selectedRowIndex - 1).Cells(1).Value)
    Dim prevPriority As Integer = Convert.ToInt32(DataGridView2.Rows(selectedRowIndex - 1).Cells("Priority").Value)

    ' 事务内交换优先级
    Using conn As New Odbc.OdbcConnection("你的数据库连接字符串") ' 替换为实际连接字符串
        conn.Open()
        Dim transaction As Odbc.OdbcTransaction = conn.BeginTransaction()
        Try
            ' 更新当前行优先级为上一行的值
            Dim updateCurrentCmd As New Odbc.OdbcCommand("UPDATE Production..CMBGSeat_Staging SET [Priority] = ? WHERE cid = ?", conn, transaction)
            updateCurrentCmd.Parameters.AddWithValue("@Priority", prevPriority)
            updateCurrentCmd.Parameters.AddWithValue("@cid", currentCid)
            updateCurrentCmd.ExecuteNonQuery()

            ' 更新上一行优先级为当前行的值
            Dim updatePrevCmd As New Odbc.OdbcCommand("UPDATE Production..CMBGSeat_Staging SET [Priority] = ? WHERE cid = ?", conn, transaction)
            updatePrevCmd.Parameters.AddWithValue("@Priority", currentPriority)
            updatePrevCmd.Parameters.AddWithValue("@cid", prevCid)
            updatePrevCmd.ExecuteNonQuery()

            transaction.Commit()
            MessageBox.Show("上移成功")
        Catch ex As Exception
            transaction.Rollback()
            MessageBox.Show("上移失败：" & ex.Message)
        End Try
    End Using

    ' 刷新DataGridView
    RefreshDataGridView()
End Sub

Private Sub BtnMoveDown_Click(sender As Object, e As EventArgs) Handles BtnMoveDown.Click
    ' 获取选中行的索引
    Dim selectedRowIndex As Integer = -1
    For i As Integer = 0 To DataGridView2.Rows.Count - 1
        If Convert.ToBoolean(DataGridView2.Rows(i).Cells(0).Value) Then
            selectedRowIndex = i
            Exit For
        End If
    Next

    ' 边界校验
    If selectedRowIndex = -1 OrElse selectedRowIndex = DataGridView2.Rows.Count - 1 Then
        MessageBox.Show("请选中非最后一行的数据进行下移操作")
        Return
    End If

    ' 获取当前行与下一行的主键和优先级
    Dim currentCid As Integer = Convert.ToInt32(DataGridView2.Rows(selectedRowIndex).Cells(1).Value)
    Dim currentPriority As Integer = Convert.ToInt32(DataGridView2.Rows(selectedRowIndex).Cells("Priority").Value)
    Dim nextCid As Integer = Convert.ToInt32(DataGridView2.Rows(selectedRowIndex + 1).Cells(1).Value)
    Dim nextPriority As Integer = Convert.ToInt32(DataGridView2.Rows(selectedRowIndex + 1).Cells("Priority").Value)

    ' 事务内交换优先级
    Using conn As New Odbc.OdbcConnection("你的数据库连接字符串") ' 替换为实际连接字符串
        conn.Open()
        Dim transaction As Odbc.OdbcTransaction = conn.BeginTransaction()
        Try
            ' 更新当前行优先级为下一行的值
            Dim updateCurrentCmd As New Odbc.OdbcCommand("UPDATE Production..CMBGSeat_Staging SET [Priority] = ? WHERE cid = ?", conn, transaction)
            updateCurrentCmd.Parameters.AddWithValue("@Priority", nextPriority)
            updateCurrentCmd.Parameters.AddWithValue("@cid", currentCid)
            updateCurrentCmd.ExecuteNonQuery()

            ' 更新下一行优先级为当前行的值
            Dim updateNextCmd As New Odbc.OdbcCommand("UPDATE Production..CMBGSeat_Staging SET [Priority] = ? WHERE cid = ?", conn, transaction)
            updateNextCmd.Parameters.AddWithValue("@Priority", currentPriority)
            updateNextCmd.Parameters.AddWithValue("@cid", nextCid)
            updateNextCmd.ExecuteNonQuery()

            transaction.Commit()
            MessageBox.Show("下移成功")
        Catch ex As Exception
            transaction.Rollback()
            MessageBox.Show("下移失败：" & ex.Message)
        End Try
    End Using

    ' 刷新DataGridView
    RefreshDataGridView()
End Sub

' 封装刷新方法，避免重复代码
Private Sub RefreshDataGridView()
    Using conn As New Odbc.OdbcConnection("你的数据库连接字符串") ' 替换为实际连接字符串
        Dim cmd As New Odbc.OdbcCommand("SELECT * FROM Production..CMBGSeat_Staging WHERE DateCompleted IS NULL ORDER BY Priority DESC", conn)
        Dim adapter As New Odbc.OdbcDataAdapter(cmd)
        Dim ds As New DataSet()
        adapter.Fill(ds)
        DataGridView2.DataSource = ds.Tables(0)
    End Using
End Sub

额外说明 #

1. 请将代码中的"你的数据库连接字符串"替换为实际的数据库连接字符串，或复用你原有的con()方法逻辑；
2. 确保Priority列名与数据库一致，若列名不同需修改对应单元格的索引或名称；
3. 事务的使用保证了两次更新要么同时成功，要么同时回滚，避免数据不一致；
4. 参数化查询彻底解决了SQL注入风险，同时提升代码健壮性。

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