Both the bulk insert and update solutions in sql server share of asp.net

If you only need to insert data in large quantities, bcp is the best. If you need to insert, delete and update data at the same time, SqlDataAdapter is recommended. I have tested that it has high efficiency
bcp way

 
/// <summary> 
///  Bulk insert data (2000 Each batch ) 
///  Overall thing control has been adopted  
/// </summary> 
/// <param name="connString"> Database link string </param> 
/// <param name="tableName"> Target table name on the database server </param> 
/// <param name="dt"> Contains the target database table structure completely 1 to ( The field name contained is complete 1 Can be caused by ) the 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(); 
} 
} 
} 
} 
} 

SqlDataAdapter

 
/// <summary> 
///  Batch update data ( Each batch 5000) 
/// </summary> 
/// <param name="connString"> Database link string </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(); 
// Set the number of batch updates per processing  
adapter.UpdateBatchSize = 5000; 
adapter.SelectCommand.Transaction = conn.BeginTransaction();///////////////// Start the transaction  
if (table.ExtendedProperties["SQL"] != null) 
{ 
adapter.SelectCommand.CommandText = table.ExtendedProperties["SQL"].ToString(); 
} 
adapter.Update(table); 
adapter.SelectCommand.Transaction.Commit();///// Commit the transaction  
} 
catch (Exception ex) 
{ 
if (adapter.SelectCommand != null && adapter.SelectCommand.Transaction != null) 
{ 
adapter.SelectCommand.Transaction.Rollback(); 
} 
throw ex; 
} 
finally 
{ 
conn.Close(); 
conn.Dispose(); 
} 
}