SqlBulkCopy highly efficient batch insertion of SQL SERVER and sqlbulkcopysql

Source: Internet
Author: User

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 ();}}}}

 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.