重存DataGridView数据至SQL库：如何避免插入失败时误删数据？

要解决插入失败导致原有数据丢失的问题，核心是让删除和插入操作成为一个原子操作——要么全部成功执行，要么全部不执行。这可以通过SQL Server的事务（Transaction）实现，具体修改如下：

关键修改点 #

• 取消提前调用DeleteData()，将删除操作与插入操作合并到同一个数据库连接和事务中
• 使用SqlTransaction管理事务，所有操作完成后再提交，任何步骤出错则回滚事务
• 统一管理数据库连接，避免重复打开/关闭连接

修改后的完整代码 #

Private Sub SaveData()
    Dim answer As Integer = MsgBox("Are you sure to save?", vbQuestion + vbYesNo + vbDefaultButton1, "Save Record")
    If answer <> vbYes Then Exit Sub

    Dim conn As SqlConnection = Nothing
    Dim trans As SqlTransaction = Nothing
    Try
        ' 打开数据库连接并启动事务
        conn = New SqlConnection(你的数据库连接字符串) ' 替换为实际的连接字符串，或复用你的ConnecDatabase逻辑
        conn.Open()
        trans = conn.BeginTransaction()

        ' 1. 执行删除操作（事务内执行）
        Using deleteCmd As New SqlCommand("Operation_DeletePO", conn, trans)
            deleteCmd.CommandType = CommandType.StoredProcedure
            deleteCmd.Parameters.AddWithValue("@pono", text_ponumber.Text)
            deleteCmd.ExecuteNonQuery()
        End Using

        ' 2. 执行批量插入操作（事务内执行）
        Using insertCmd As New SqlCommand("Operation_InsertPO", conn, trans)
            insertCmd.CommandType = CommandType.StoredProcedure
            ' 预先定义参数，避免循环内重复创建
            insertCmd.Parameters.Add("@PONo", SqlDbType.VarChar) ' 根据数据库实际字段类型调整
            insertCmd.Parameters.Add("@ProductCode", SqlDbType.VarChar)
            insertCmd.Parameters.Add("@ProductName", SqlDbType.VarChar)
            insertCmd.Parameters.Add("@Units", SqlDbType.VarChar)
            insertCmd.Parameters.Add("@Qty", SqlDbType.Decimal)
            insertCmd.Parameters.Add("@Rate", SqlDbType.Decimal)
            insertCmd.Parameters.Add("@Amt", SqlDbType.Decimal)
            insertCmd.Parameters.Add("@User", SqlDbType.VarChar)

            For i As Integer = 0 To DGV_PO.RowCount - 2 ' 跳过DataGridView默认空行
                If DGV_PO.Rows(i).IsNewRow Then Continue For

                ' 赋值参数，处理空值避免数据库报错
                insertCmd.Parameters("@PONo").Value = text_ponumber.Text
                insertCmd.Parameters("@ProductCode").Value = If(DGV_PO.Rows(i).Cells(2).Value Is DBNull.Value, "", DGV_PO.Rows(i).Cells(2).Value)
                insertCmd.Parameters("@ProductName").Value = If(DGV_PO.Rows(i).Cells(3).Value Is DBNull.Value, "", DGV_PO.Rows(i).Cells(3).Value)
                insertCmd.Parameters("@Units").Value = If(DGV_PO.Rows(i).Cells(4).Value Is DBNull.Value, "", DGV_PO.Rows(i).Cells(4).Value)
                insertCmd.Parameters("@Qty").Value = If(DGV_PO.Rows(i).Cells(5).Value Is DBNull.Value, 0, DGV_PO.Rows(i).Cells(5).Value)
                insertCmd.Parameters("@Rate").Value = If(DGV_PO.Rows(i).Cells(6).Value Is DBNull.Value, 0, DGV_PO.Rows(i).Cells(6).Value)
                insertCmd.Parameters("@Amt").Value = If(DGV_PO.Rows(i).Cells(7).Value Is DBNull.Value, 0, DGV_PO.Rows(i).Cells(7).Value)
                insertCmd.Parameters("@User").Value = GlobalVariables.UserName

                insertCmd.ExecuteNonQuery()
            Next
        End Using

        ' 所有操作成功，提交事务
        trans.Commit()
        MsgBox("Saved Successfully", vbInformation)
    Catch ex As Exception
        ' 出错则回滚事务，撤销所有操作
        If trans IsNot Nothing Then
            Try
                trans.Rollback()
            Catch rollbackEx As Exception
                MsgBox($"回滚事务时出错: {rollbackEx.Message}", vbCritical)
            End Try
        End If
        MsgBox($"保存失败: {ex.Message}", vbCritical)
    Finally
        ' 确保数据库连接关闭释放
        If conn IsNot Nothing AndAlso conn.State = ConnectionState.Open Then
            conn.Close()
            conn.Dispose()
        End If
    End Try
End Sub

' 原DeleteData方法可删除，逻辑已合并到SaveData中

额外优化说明 #

1. 使用Using语句：自动释放SqlCommand资源，避免内存泄漏
2. 处理空行和DBNull：避免DataGridView空行或空值导致的数据库错误
3. 预先定义参数：循环内仅赋值参数，提升代码执行效率
4. 事务回滚保障：一旦任何步骤出错，事务会回滚，删除操作的影响会被完全撤销

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