In general, we use INSERT statements when inserting data into a database, but when the amount of data is large, this is a slow situation, and this is the time to sqlbulkcopy this class.
There are so few functions that are commonly used by SqlBulkCopy itself.
DestinationTableName-----The name on the server target table, which table you want to insert a large amount of data into, which is set to that table name
ColumnMappings-----Field mappings because you want to create a table locally and then add the entire table one time to the database, so you need to map the fields of the local table to the fields in the database table
WriteToServer-----This is to write to the database, do not need to say more
The imported data is the mobile phone number attribution information provided on the free video of the Wisdom podcast, inserted into the database to look at, a total of 258,113 data, the time is about 6 seconds.
Private voidBtndr_click (Objectsender, RoutedEventArgs e) {OpenFileDialog OFD=NewOpenFileDialog (); Ofd. Filter="text Files |*.txt"; if(OFD. ShowDialog ()! =true) { return; } DateTime Time= DateTime.Now;//time to see how long it tookDataTable Table=NewDataTable ();//Create a temporary table locally//add column names to temporary tablesTable. Columns.Add ("HD"); Table. Columns.Add ("DQ"); Table. Columns.Add ("LX"); using(StreamReader streamreader=NewStreamReader (OFD. Filename,encoding.default))//Read File { while(!streamreader.endofstream) {string[] StringStream = StreamReader.ReadLine (). Split ('\ t');//read by line, split by \ t, get array stringHD = stringstream[0]. Trim ('"'); stringDQ = stringstream[1]. Trim ('"'); stringLX = stringstream[2]. Trim ('"'); //inserting data into a locally created temporary tableDataRow row =table. NewRow (); row["HD"] =HD; row["DQ"] =DQ; row["LX"] =LX; Table. Rows.Add (row); } } //This is part of importing from the local to the database using(SqlConnection conn=NewSqlConnection ("Data Source=.;i Nitial catalog=imageprocess;integrated security=true") {Conn. Open (); using(SqlBulkCopy bulkcopy=NewSqlBulkCopy (conn)) {Bulkcopy.destinationtablename="T_hm"; BULKCOPY.COLUMNMAPPINGS.ADD ("HD","HD");//The previous parameter is the local temporary table column name, followed by the database column name. Two names do not need the same, I write the same, is for convenienceBULKCOPY.COLUMNMAPPINGS.ADD ("DQ","DQ"); BULKCOPY.COLUMNMAPPINGS.ADD ("LX","LX"); Bulkcopy.writetoserver (table); }} TimeSpan TS= DateTime.Now-Time ; MessageBox.Show (TS. ToString ()); }
Wisdom Podcast--ado.net--sqlbulkcopy BULK Insert data (small white must know)