SqlBulkCopy highly efficient batch insertion of SQL SERVER and sqlbulkcopysql

SqlBulkCopy highly efficient batch insertion of SQL SERVER and sqlbulkcopysql

What

SqlBulkCopy is a class provided by. NET for batch data insertion. In particular, it inserts data in the memory into the database at one time. Currently, it can only be inserted into the SQL SERVER database. The data source can be DataTable or IDataReader.

Why

Compared with loop inserts, SqlBulkCopy inserts greatly improve the performance. The more data is inserted, the more obvious the performance advantage is.

Test results: it takes 6 seconds to insert 10 thousand data records and 0.1 seconds to Bulk. Theoretically, it takes less than 1 minute to insert millions of records.

How

The following is the test code:

String sourceConStr = "Data Source = 192.168.1.100; Initial Catalog = A; uid = sa; pwd = sa"; string destConStr = "Data Source = .; initial Catalog = B; uid = sa; pwd = sa "; DataTable dt = new DataTable (); public Form1 () {InitializeComponent ();} private void button3_Click (object sender, eventArgs e) {dt = GetTable (); MessageBox. show ("get finish");} private void button#click (object sender, EventArgs e) {string st R = "queue start ...! \ N "; Stopwatch sw = new Stopwatch (); sw. start (); CopyData (dt); sw. stop (); str + = "queue cost time is" + sw. elapsedMilliseconds + "\ n"; richTextBox1.Text = str;} private void button2_Click (object sender, EventArgs e) {string str = "bulk start ...! \ N "; Stopwatch sw = new Stopwatch (); sw. start (); CopyDataBulk (dt); sw. stop (); str + = "bulk cost time is" + sw. elapsedMilliseconds + "\ n"; richTextBox2.Text = str;} // obtain the private DataTable GetTable () {DataTable dt = new DataTable () from the data source (); using (SqlConnection sourceConnection = new SqlConnection (sourceConStr) {sourceConnection. open (); SqlCommand cmd = new SqlCommand ("select top 10000 CName, PersonID, Sex, Age FROM Customer order by cid asc; ", sourceConnection); cmd. commandTimeout = 600000; SqlDataAdapter da = new SqlDataAdapter (cmd); da. fill (dt) ;}return dt ;}// insert private void CopyData (DataTable dt) {using (SqlConnection destinationConnection = new SqlConnection (destConStr) {destinationConnection. open (); foreach (DataRow reader in dt. rows) {string SQL = "INSERT INTO Customer (Na Me, PersonID, Sex, Age) VALUES ('"+ reader [" Cname "]. toString () + "','" + reader ["PersonID"]. toString () + "','" + reader ["Sex"]. toString () + "','" + reader ["Age"]. toString () + "')"; SqlCommand cmd = new SqlCommand (SQL, destinationConnection); try {int re = cmd. executeNonQuery ();} catch (Exception ex) {MessageBox. show (ex. message) ;}}}// Bulk inserts private void CopyDataBulk (DataTable dt) {using (SQL Connection destinationConnection = new SqlConnection (destConStr) {destinationConnection. Open (); using (SqlBulkCopy bulkCopy = new SqlBulkCopy (destinationConnection) {// write the ing. For example, the data in the CName column of the old table corresponds to bulkCopy in the Name column of the new table. columnMappings. add ("CName", "Name"); // set the target table Name bulkCopy. destinationTableName = "Customer"; try {bulkCopy. writeToServer (dt);} catch (Exception ex) {MessageBox. show (ex. message);} finally {// reader. close ();}}}}