Use of SqlBulkCopy

1, Problem: Import Big Data volume to the database, with our ordinary sqlhelper to do is every insert is open connection closed connection, so too slow, so we would like to keep SqlConnection open until all the data is inserted to complete and then close the connection. However, depending on the database connection pool, this speed is still slow.

2, the solution:. NET provides us with SqlBulkCopy to perform inserts at once, with much higher efficiency and speed.

3. Example:

such as: Import mobile phone number attribution information

Preparation material: "Mobile phone number segment attribution to database. txt" document.

If you add a button in WinForm, the button is implemented in the Click event.

#code

Private voidBtn_import_click (Objectsender, RoutedEventArgs e) {            //read the file first//Open the dialog box and select the file. OpenFileDialog OFD =NewOpenFileDialog (); Ofd. Filter="text Files |*.txt"; if(OFD. ShowDialog () = =false)            {                return; }             string[] lines =File.readlines (OFD. Filename,encoding.default).            ToArray (); DateTime StartTime=DateTime.Now; //New TableDataTable dttable =NewDataTable (); //adding columns to a tableDTTABLE.COLUMNS.ADD ("Starttellname"); DTTABLE.COLUMNS.ADD ("Telltype"); DTTABLE.COLUMNS.ADD ("Tellarea"); //iterate through each row of data, process data, add to Rows (DataRow)            foreach(stringLineinchlines) {                string[] STRs = line. Split ('\ t');//\ t Tab                stringStarttellnum = strs[0]; stringTelltype = strs[1]. Trim ('"');//remove both sides of the "                stringTellarea = strs[2]. Trim ('"'); DataRow Row=Dttable.newrow (); row["Starttellname"] = Starttellnum;//Assigning a value to a fieldrow["Telltype"] =Telltype; row["Tellarea"] =Tellarea; DTTABLE.ROWS.ADD (row);//Add to Line            }            //get the connection string in the configuration file            stringConnStr = configurationmanager.connectionstrings["ConnStr"].            ConnectionString; //SqlBulkCopy is the implementation of the IDisposable interface, you must use the using            using(SqlBulkCopy bulk =NewSqlBulkCopy (CONNSTR)) {Bulk. DestinationTableName="T_tellnum";//Specify table name//Local column name establishes connection to database column nameBulk. Columnmappings.add ("Starttellname","Starttellnum"); Bulk. Columnmappings.add ("Telltype","Telltype"); Bulk. Columnmappings.add ("Tellarea","Tellarea"); //write the dttable data to the databaseBulk.            WriteToServer (dttable); } TimeSpan TS= DateTime.Now-StartTime; //Calculation TimeMessageBox.Show (TS.        ToString ()); }

#code

4. Database fields

ID bigint primary KEY,

Starttellnum nvarchar (30),

Telltype nvarchar (30),

Tellarea nvarchar (30),

5. Problems that may be encountered

Workaround: Check that the field names are correct when the connection field is established.

Workaround

1, first check if the database table field is too small

2, check the data for a similar single-quote data, do a filter

Use of SqlBulkCopy