bcp和SqlDataAdapter进行批量跟新插入方法

/// <summary>

/// 大批量插入数据(2000每批次)

/// 已采用整体事物控制

/// </summary>

/// <param name="connString">数据库链接字符串</param>

/// <param name="tableName">数据库服务器上目标表名</param>

/// <param name="dt">含有和目标数据库表结构完全一致(所包含的字段名完全一致即可)的DataTable</param>

public static void BulkCopy(string connString, string tableName, DataTable dt)

{

    using (SqlConnection conn = new SqlConnection(connString))

    {

    conn.Open();

 

    using (SqlTransaction transaction = conn.BeginTransaction())

    {

        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, transaction))

        {

        bulkCopy.BatchSize = 2000;

        bulkCopy.BulkCopyTimeout = _CommandTimeOut;

        bulkCopy.DestinationTableName = tableName;

 

        try

        {

            foreach (DataColumn col in dt.Columns)

            {

            bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);

 

            }

            bulkCopy.WriteToServer(dt);

            transaction.Commit();

        }

        catch (Exception ex)

        {

            transaction.Rollback();

            throw ex;

        }

        finally

        {

            conn.Close();

        }

        }

    }

    }

}

 

/// <summary> /// 批量更新数据(每批次5000) /// </summary> /// <param name="connString">数据库链接字符串</param> /// <param name="table"></param> public static void Update(string connString, DataTable table) {     SqlConnection conn = new SqlConnection(connString);     SqlCommand comm = conn.CreateCommand();     comm.CommandTimeout = _CommandTimeOut;     comm.CommandType = CommandType.Text;     SqlDataAdapter adapter = new SqlDataAdapter(comm);     SqlCommandBuilder commandBulider = new SqlCommandBuilder(adapter);     commandBulider.ConflictOption = ConflictOption.OverwriteChanges;     try     {     conn.Open();     //设置批量更新的每次处理条数     adapter.UpdateBatchSize = 5000;     adapter.SelectCommand.Transaction = conn.BeginTransaction();/////////////////开始事务       if (table.ExtendedProperties["SQL"] != null)     {         adapter.SelectCommand.CommandText = table.ExtendedProperties["SQL"].ToString();     }     adapter.Update(table);     adapter.SelectCommand.Transaction.Commit();/////提交事务     }     catch (Exception ex)     {     if (adapter.SelectCommand != null && adapter.SelectCommand.Transaction != null)     {         adapter.SelectCommand.Transaction.Rollback();     }     throw ex;     }     finally     {     conn.Close();     conn.Dispose();     } }