Bulk insert data using transactions and SqlBulkCopy

SqlBulkCopy is a new class for the. NET Framework 2.0, located under Namespace System.Data.SqlClient, primarily providing the ability to load data from other data sources into a SQL Server table in a valid batch. Similar to the command-line application named bcp in the Microsoft SQL Server package. However, using the SqlBulkCopy class, you can write managed code solutions that outperform the bcp command-line application and are better than loading large amounts of data into SQL Server tables, such as insert. SqlBulkCopy can be applied to the transfer of large quantities of data, regardless of the data source.

Before doing WinForm development, found that when the DataGridView data volume is large, with a for loop very time and performance, through the access to data, learned the SqlBulkCopy this batch of data transfer tool.

The following code implements bulk data insertion for DataGridView.

If the DataGridView column does not correspond exactly to the table structure in the database, we need to first store the DataGridView data in a DataTable, noting that the columns in the DataTable need to be compatible with the column type of the table that is about to be inserted, and the name is exactly the same as the column order. Self-increment the value of an int type can be filled in arbitrarily.

DataTable table = new DataTable ();
Table. Columns.addrange (New datacolumn[]{
New DataColumn ("flow_id", typeof (int)),
New DataColumn ("Sheet_no", typeof (String)),
New DataColumn ("Item_no", typeof (String)),
New DataColumn ("Unit_no", typeof (String)),
New DataColumn ("Unit_factor", typeof (String)),
New DataColumn ("In_price", typeof (String)),
New DataColumn ("Order_qnty", typeof (String)),
New DataColumn ("Sub_amount", typeof (String)),
New DataColumn ("Real_qty", typeof (String)),
New DataColumn ("Tax_rate", typeof (String)),
New DataColumn ("Pay_percent", typeof (String)),
New DataColumn ("Out_qty", typeof (String))});
for (int i = 0; i < dt. Rows.Count; i++)
{
DataRow r = dt. Rows[i];
Table. Rows.Add (i, Sheet_no, r["Item_no"], r["Unit_no"], r["Unit_factor"], r["In_price"], r["Order_qnty"], r["Sub_amount"], R ["Real_qty"], r["Tax_rate"], r["pay_percent"], r["Out_qty"]);

}

Start Data Saving logic

using (SqlConnection conn = new SqlConnection (connectionString))
{
Conn. Open ();

SqlTransaction TRAN = conn. BeginTransaction ();//Open transaction

Check constraints while inserting data, call Sqlbulktransaction transaction if an error occurs

SqlBulkCopy bulkcopy = new SqlBulkCopy (conn, sqlbulkcopyoptions.checkconstraints, Tran);
Bulkcopy.destinationtablename = "* * *";//*** represents the name of the table to insert data into
foreach (DataColumn dc in table. Columns)//pass in the table above
{
BULKCOPY.COLUMNMAPPINGS.ADD (DC. ColumnName, DC. ColumnName);
}

Try
{
Bulkcopy.writetoserver (table);
Tran.commit ();
}
catch (Exception ex)
{
Tran. Rollback ();
}
Finally
{
Bulkcopy.close ();
Conn. Close ();
}

Bulk insert data using transactions and SqlBulkCopy