本文共 6939 字，大约阅读时间需要 23 分钟。

/// 
        ///大批量数据插入        /// 
        /// 数据表        /// 数据库连接字符串        /// 
           public bool SqlServerBulkInsert(DataTable table, string connectionString)        {
               try            {
                   using (SqlConnection conn = new SqlConnection(connectionString))                {
                       conn.Open();                    SqlTransaction trans = conn.BeginTransaction();                    SqlBulkCopy sqlbulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, trans);                    // 设置源表名称                    sqlbulkCopy.DestinationTableName = table.TableName;                    //分几次拷贝                    //sqlbulkCopy.BatchSize = 10;                    // 设置超时限制                    sqlbulkCopy.BulkCopyTimeout = CommandTimeOut;                    foreach (DataColumn dtColumn in table.Columns)                    {
                           sqlbulkCopy.ColumnMappings.Add(dtColumn.ColumnName, dtColumn.ColumnName);                    }                    try                    {
                           // 写入                        sqlbulkCopy.WriteToServer(table);                        // 提交事务(经测试2021-01-02，事务是起作用的，要么全部成功写入，要么零写入)                        trans.Commit();                        return true;                    }                    catch                    {
                           trans.Rollback();                        sqlbulkCopy.Close();                        return false;                    }                    finally                    {
                           conn.Close();                        conn.Dispose();                        sqlbulkCopy.Close();                    }                }            }            catch (Exception e)            {
                   DbLog.WriteException(e);                return false;            }        }

ＶＢ．

' 
    '大批量数据插入    ' 
    ' Dataset    ' Connection String    ' Exec Command text before BulkInsert     ' Parameters of exec Command     ' Prepare Command Type (SQL / PRO)    ' 
       Public Function BulkInsert(ByVal insertDataSet As DataSet, ByVal connString As String, ByVal preCmdTxt As String, ByVal preSqlPara() As SqlParameter, ByVal preCmdType As String) As Boolean        Try            Using conn As New SqlConnection(connString)                If conn.State = ConnectionState.Closed Then                    conn.Open()                End If                Dim trans = conn.BeginTransaction()                Dim sqlBulkCopy = New SqlBulkCopy(conn, SqlBulkCopyOptions.Default, trans)                sqlBulkCopy.BulkCopyTimeout = 100 'time out seconds                Try                    'exec pre command before bulk insert                     If Not String.IsNullOrEmpty(preCmdTxt) Then                        Dim cmd As SqlCommand = conn.CreateCommand()                        cmd.CommandText = preCmdTxt                        cmd.Transaction = trans                        If preCmdType.ToUpper() = "PRO" Then ' Stored procedure                            cmd.CommandType = CommandType.StoredProcedure                        End If                        If Not preSqlPara Is Nothing Then                            cmd.Parameters.AddRange(preSqlPara)                        End If                        cmd.ExecuteNonQuery()                    End If                    ' ForLoop to bulk insert the table in the dataset                    For Each insertTable In insertDataSet.Tables.OfType(Of DataTable)                        sqlBulkCopy.DestinationTableName = insertTable.TableName                        sqlBulkCopy.ColumnMappings.Clear()                        For Each dtColumn In insertTable.Columns.OfType(Of DataColumn)                            sqlBulkCopy.ColumnMappings.Add(dtColumn.ColumnName, dtColumn.ColumnName) '注意：１.两个表的列名要相同；当然，改一下传入参数为两个表（源表，目标表），则可以做到列名不一样，只要求字段类型一样；２.可以只批量插入部分字段，须留意非空字段和已设默认值                        Next                        sqlBulkCopy.WriteToServer(insertTable) 'insert to table                    Next                    trans.Commit()                    Return True                Catch ex As Exception                    trans.Rollback()                    Return False                Finally                    conn.Close()                    conn.Dispose()                    sqlBulkCopy.Close()                End Try            End Using        Catch ex As Exception            Return False        End Try    End Function  ' 
    '大批量数据插入    ' 
    ' Dataset    ' Connection String    ' Exec Command text before BulkInsert     ' Parameters of exec Command     ' Prepare Command Type (SQL / PRO)    ' 
       Public Function BulkInsert(ByVal insertDataSet As DataSet, ByVal connString As String, ByVal preCmdTxt As String, ByVal preSqlPara() As SqlParameter, ByVal preCmdType As String) As Boolean        Try            Using conn As New SqlConnection(connString)                If conn.State = ConnectionState.Closed Then                    conn.Open()                End If                Dim trans = conn.BeginTransaction()                Dim sqlBulkCopy = New SqlBulkCopy(conn, SqlBulkCopyOptions.Default, trans)                sqlBulkCopy.BulkCopyTimeout = 100 'time out seconds                Try                    'exec pre command before bulk insert                     If Not String.IsNullOrEmpty(preCmdTxt) Then                        Dim cmd As SqlCommand = conn.CreateCommand()                        cmd.CommandText = preCmdTxt                        cmd.Transaction = trans                        If preCmdType.ToUpper() = "PRO" Then ' Stored procedure                            cmd.CommandType = CommandType.StoredProcedure                        End If                        If Not preSqlPara Is Nothing Then                            cmd.Parameters.AddRange(preSqlPara)                        End If                        cmd.ExecuteNonQuery()                    End If                    ' ForLoop to bulk insert the table in the dataset                    For Each insertTable In insertDataSet.Tables.OfType(Of DataTable)                        sqlBulkCopy.DestinationTableName = insertTable.TableName                        sqlBulkCopy.ColumnMappings.Clear()                        For Each dtColumn In insertTable.Columns.OfType(Of DataColumn)                            sqlBulkCopy.ColumnMappings.Add(dtColumn.ColumnName, dtColumn.ColumnName)                        Next                        sqlBulkCopy.WriteToServer(insertTable) 'insert to table                    Next                    trans.Commit()                    Return True                Catch ex As Exception                    trans.Rollback()                    Return False                Finally                    conn.Close()                    conn.Dispose()                    sqlBulkCopy.Close()                End Try            End Using        Catch ex As Exception            Return False        End Try    End Function

转载地址：http://himdi.baihongyu.com/