ASP VB中SqlBulkCopy复制DataTable遇单行错误致全量失败的问询

你的代码逻辑确实简洁清晰，但正如你发现的，SqlBulkCopy默认是在单个事务中执行批量插入的——只要有一行数据不符合目标表的约束（比如主键重复、字段类型不匹配、非空字段为空等），整个批量操作就会回滚，所有数据都无法写入。下面给你几个实用的解决思路和修改方案：

1. 分批处理，缩小错误影响范围 #

通过设置BatchSize属性，把大的DataTable拆分成多个小批次插入。每个批次会在独立的事务中执行，这样即使某一批次里有错误行，只会回滚当前批次的内容，其他正确的批次依然能成功写入。

修改后的代码示例：

Public Sub PerformBulkCopy(ByVal dt As DataTable, ByVal tablename As String)
    Const batchSize As Integer = 1000 ' 可根据你的数据量调整批次大小
    Using sqlcon As SqlConnection = New SqlConnection(PyrDLL.KoneksiLokal(Session("IP")).ConnectionString)
        sqlcon.Open()
        Using s As SqlBulkCopy = New SqlBulkCopy(sqlcon)
            s.DestinationTableName = tablename
            s.BatchSize = batchSize ' 设置批次大小
            
            ' 可选：添加NotifyAfter事件，监控批次执行进度
            s.NotifyAfter = batchSize
            AddHandler s.SqlRowsCopied, Sub(sender, e)
                                           Console.WriteLine($"已成功复制 {e.RowsCopied} 行数据")
                                       End Sub
            
            Try
                s.WriteToServer(dt)
            Catch ex As SqlException
                Console.WriteLine($"批次插入出错：{ex.Message}")
                ' 这里可以添加错误日志记录，或者针对错误批次做进一步排查
            End Try
            s.Close()
        End Using
        sqlcon.Close()
    End Using
End Sub

2. 捕获错误并隔离错误行 #

如果需要精准定位并跳过错误行，可以在捕获异常后，逐行验证或尝试插入，把错误行单独记录下来，剩下的正确行重新执行批量插入。这种方法适合需要保留错误数据进行后续分析的场景：

Public Sub PerformBulkCopyWithErrorHandling(ByVal dt As DataTable, ByVal tablename As String)
    Dim errorRows As New DataTable(dt.Clone()) ' 用于存储错误行
    Using sqlcon As SqlConnection = New SqlConnection(PyrDLL.KoneksiLokal(Session("IP")).ConnectionString)
        sqlcon.Open()
        
        ' 先尝试全量插入
        Try
            Using s As SqlBulkCopy = New SqlBulkCopy(sqlcon)
                s.DestinationTableName = tablename
                s.WriteToServer(dt)
            End Using
        Catch ex As SqlException
            Console.WriteLine("全量插入失败，开始逐行排查错误...")
            ' 逐行尝试插入，分离错误行
            For Each row As DataRow In dt.Rows
                Try
                    ' 这里用普通的SqlCommand逐行插入（小数据量适用）
                    Dim insertCmd As New SqlCommand(BuildInsertCommand(row, tablename), sqlcon)
                    insertCmd.ExecuteNonQuery()
                Catch rowEx As SqlException
                    errorRows.ImportRow(row)
                    Console.WriteLine($"行数据出错：{rowEx.Message}，已记录到错误集合")
                End Try
            Next
            
            ' 如果还有正确行未插入，也可以再次用批量插入处理
            If dt.Rows.Count > errorRows.Rows.Count Then
                Dim correctRows As DataTable = dt.Clone()
                For Each row As DataRow In dt.Rows
                    Dim isErrorRow As Boolean = False
                    For Each errRow As DataRow In errorRows.Rows
                        If row.ItemArray.SequenceEqual(errRow.ItemArray) Then
                            isErrorRow = True
                            Exit For
                        End If
                    Next
                    If Not isErrorRow Then
                        correctRows.ImportRow(row)
                    End If
                Next
                Using s As SqlBulkCopy = New SqlBulkCopy(sqlcon)
                    s.DestinationTableName = tablename
                    s.WriteToServer(correctRows)
                End Using
            End If
        End Try
        
        sqlcon.Close()
    End Using
    
    ' 这里可以把errorRows保存到日志表或者导出文件
    If errorRows.Rows.Count > 0 Then
        Console.WriteLine($"共发现 {errorRows.Rows.Count} 条错误行，已保存")
        ' SaveErrorRowsToLog(errorRows)
    End If
End Sub

' 辅助方法：根据DataRow生成插入命令
Private Function BuildInsertCommand(row As DataRow, tableName As String) As String
    Dim cols As String = String.Join(", ", row.Table.Columns.Cast(Of DataColumn).Select(Function(c) $"[{c.ColumnName}]"))
    Dim vals As String = String.Join(", ", row.Table.Columns.Cast(Of DataColumn).Select(Function(c) 
        If row.IsNull(c) Then "NULL" Else $"'{row(c.ColumnName).ToString().Replace("'", "''")}'"
    ))
    Return $"INSERT INTO [{tableName}] ({cols}) VALUES ({vals})"
End Function

3. 提前验证数据约束 #

在执行批量插入前，先在内存中验证DataTable的数据是否符合目标表的约束（比如主键唯一性、字段类型匹配、非空字段是否有值等），提前过滤掉错误行，避免批量操作失败。这种方法能从源头减少错误的发生：

Private Function ValidateDataTable(dt As DataTable, targetTableConstraints As Dictionary(Of String, String)) As DataTable
    Dim validRows As DataTable = dt.Clone()
    For Each row As DataRow In dt.Rows
        Dim isRowValid As Boolean = True
        
        ' 示例：验证非空字段
        For Each col As DataColumn In dt.Columns
            If targetTableConstraints.ContainsKey(col.ColumnName) AndAlso targetTableConstraints(col.ColumnName) = "NOT NULL" Then
                If row.IsNull(col) OrElse String.IsNullOrWhiteSpace(row(col).ToString()) Then
                    isRowValid = False
                    Console.WriteLine($"行 {row.RowState} 的字段 {col.ColumnName} 不能为空")
                    Exit For
                End If
            End If
        Next
        
        ' 示例：验证主键唯一性（需要提前获取目标表的主键值集合）
        ' Dim primaryKeyCol As String = "ID"
        ' If targetPrimaryKeys.Contains(row(primaryKeyCol).ToString()) Then
        '     isRowValid = False
        '     Console.WriteLine($"行 {row.RowState} 的主键 {row(primaryKeyCol)} 重复")
        ' End If
        
        If isRowValid Then
            validRows.ImportRow(row)
        End If
    Next
    Return validRows
End Function

你可以根据自己的业务场景选择合适的方案：如果追求效率，优先用分批处理；如果需要精准处理错误行，就用捕获异常分离错误行的方法；如果希望提前避免错误，就做前置数据验证。

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