It is best to use bcp if you just need to insert data in large quantities, if you need to insert, delete, and update the proposed use of SqlDataAdapter I tested high efficiency, and in general, these two meet the demand
bcp method
Copy Code code as follows:
<summary>
Bulk Insert data (2000 per batch)
Have adopted the whole thing control
</summary>
<param name= "connstring" > Database link string </param>
<param name= "tablename" > Database server Destination table name </param>
<param name= "DT" > contains the exact same field name as the target database table structure 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 Code code as follows:
<summary>
Batch update data (5000 per lot)
</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 per-processing bars for a batch update
Adapter. UpdateBatchSize = 5000;
Adapter. Selectcommand.transaction = conn. BeginTransaction ();/////////////////Start a 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 ();
}
}
