Share two solutions for batch insert and update in SQL server (asp.net)

It is best to use bcp to insert large batches of data. If you need to insert, delete, or update data at the same time, we recommend that you use SqlDataAdapter for high efficiency, generally, these two methods meet the requirements.
Bcp Method
Copy codeThe Code is as follows:
/// <Summary>
/// Insert data in large batches (2000 per Batch)
/// Overall transaction control adopted
/// </Summary>
/// <Param name = "connString"> database link string </param>
/// <Param name = "tableName"> name of the target table on the database server </param>
/// <Param name = "dt"> a DataTable containing exactly the same structure as the target database table (including the Field Names) </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
Copy codeThe Code is as follows:
/// <Summary>
/// Update data in batches (5000 per Batch)
/// </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 entries processed each time for batch update
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 (); // submit the Transaction
}
Catch (Exception ex)
{
If (adapter. SelectCommand! = Null & adapter. SelectCommand. Transaction! = Null)
{
Adapter. SelectCommand. Transaction. Rollback ();
}
Throw ex;
}
Finally
{
Conn. Close ();
Conn. Dispose ();
}
}