Chuanzhi podcast --ADO.net -- SqlBulkCopy batch data insertion (),

Chuanzhi podcast --ADO.net -- SqlBulkCopy batch data insertion (),

In general, we use the Insert statement when inserting data into the database. However, when the data volume is large, this situation slows down. In this case, we need the SqlBulkCopy class.

SqlBulkCopy has several common functions.

DestinationTableName ----- name of the target table on the server, that is, the table to which you want to insert a large amount of data. Set this parameter to the table name.

ColumnMappings ----- field ing, because you need to create a Table locally and add the entire Table to the database at one time, therefore, you need to map the field in the local Table to the field in the database Table.

WriteToServer ----- write data to the database.

The imported data is the mobile phone number home location information provided in the free video of Chuanzhi podcast. It takes about 6 seconds to insert the data into the database.

Private void btnDR_Click (object sender, RoutedEventArgs e) {OpenFileDialog ofd = new OpenFileDialog (); ofd. Filter = "text file | *. txt"; if (ofd. ShowDialog ()! = True) {return;} DateTime time = DateTime. now; // timing to see how long it took DataTable table = new DataTable (); // create a temporary table locally // Add the column name table to the temporary table. columns. add ("HD"); table. columns. add ("DQ"); table. columns. add ("LX"); using (StreamReader streamReader = new StreamReader (ofd. fileName, Encoding. default) // read the file {while (! StreamReader. endOfStream) {string [] stringStream = streamReader. readLine (). split ('\ t'); // read by row and Split by \ t to obtain the array string HD = stringStream [0]. trim ('"'); string DQ = stringStream [1]. trim ('"'); string LX = stringStream [2]. trim ('"'); // inserts data into the temporary table created locally. DataRow row = table. newRow (); row ["HD"] = HD; row ["DQ"] = DQ; row ["LX"] = LX; table. rows. add (row) ;}/// this part is imported from the local database to using (SqlConnection conn = new SqlConn Ection ("Data Source = .; initial Catalog = imageprocess; Integrated Security = True ") {conn. open (); using (SqlBulkCopy bulkCopy = new SqlBulkCopy (conn) {bulkCopy. destinationTableName = "T_HM"; bulkCopy. columnMappings. add ("HD", "HD"); // The preceding parameter is the local temporary Table column name, and the following parameter is the database column name. The two names do not need to be the same. I write the same name to facilitate bulkCopy. columnMappings. add ("DQ", "DQ"); bulkCopy. columnMappings. add ("LX", "LX"); bulkCopy. writeToServer (table) ;}} TimeSpan TS = DateTime. now-time; MessageBox. show (TS. toString ());}