Bulk INSERT and update solution sharing in SQL Server

If you only need to insert data in large batches using bcp is the best, if you need to insert, delete, update the recommended use of SqlDataAdapter I have tested very high efficiency, in general, these two will meet the needs of
BCP mode

Copy CodeThe code is as follows:
<summary>
Mass Insert data (2000 per lot)
has adopted the whole thing control
</summary>
<param name= "connstring" > Database link string </param>
<param name= "TableName" > Target table name on database server </param>
<param name= "DT" > contains exactly the same structure as the Target database table (contains field names exactly) 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

Copy CodeThe code is as follows:

<summary>
Batch update data (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 processing bars per batch update
Adapter. UpdateBatchSize = 5000;
Adapter. Selectcommand.transaction = conn. BeginTransaction ();/////////////////start transaction
if (table. extendedproperties["SQL"]! = NULL)
{
Adapter.SelectCommand.CommandText = table. extendedproperties["SQL"]. ToString ();
}
Adapter. Update (table);
Adapter.SelectCommand.Transaction.Commit ();/////COMMIT Transaction
}
catch (Exception ex)
{
if (adapter. SelectCommand! = NULL && adapter. Selectcommand.transaction = null)
{
Adapter. SelectCommand.Transaction.Rollback ();
}
Throw ex;
}
Finally
{
Conn. Close ();
Conn. Dispose ();
}
}

Bulk INSERT and update solution sharing in SQL Server