. Comparison of several implementation methods of batch adding data in net

In. NET, you often encounter bulk add data, such as importing data from Excel into a database, adding data directly to the DataGridView control, saving it to a database, and so on.

Method One: one loop to add

Usually our first reaction is to use a for or foreach loop to add a single bar.

 for (int0; i < DGV. Rows.Count; i++) {    string sql  'insert INTO ... .. " ;     NULL );}

Such a method can be imagined, the efficiency is very low, it may be slow to the operator cannot accept the kind. Tested (local area network), 1W data will take 3 minutes 42 seconds 842 milliseconds

Method Two: Add every 1000 loops

Maybe someone will think about insert multiple records, i.e. insert into TableName values (', ', ', '), values (', ', ') ', this method improves a lot of efficiency to some extent, but this method has several drawbacks. For example, in SQL Server 2000 it does not support this syntax and will prompt "line 2nd: ', ' near a syntax error." "The warning, the batch operation also can not talk about. For example, this method can only INSERT 1000 data at most, if more than 1000 will be an error: "The number of row value expression in the INSERT statement exceeds the maximum allowable value of 1000 rows of values." "。 Of course, we can divide it into several times, adding 1000 data at a time, which is much more efficient than method one. Tested (local area network), 1W data will take 0 minutes 14 seconds 766 milliseconds

intRowCount =DataGridView1.Rows.Count;intquotient = RowCount/ +;//Businessintremainder = rowCount% +;//remainderStringBuilder str =NewStringBuilder ();  for(intj =0; J < Quotient; J + +) {str. Append ("INSERT INTO batchtable values");  for(inti =0; I < +; i++) {str. AppendFormat ("(' {0} ', ' {1} ', ' {2} ', ' {3} ', ' {4} ', ' {5} ', ' {6} ', ' {7} ', ' {8} ', ' {9 } ', ' {ten} '),", datagridview1[0, I]. Value, datagridview1[1, I]. Value, datagridview1[2, I]. Value, datagridview1[3, I]. Value, datagridview1[4, I]. Value, datagridview1[5, I]. Value, datagridview1[6, I]. Value, datagridview1[7, I]. Value, datagridview1[8, I]. Value, datagridview1[9, I]. Value, datagridview1[Ten, I].    Value); }     stringsql = str. ToString (). TrimEnd (','); Sqlhelper.excutenonquery (CommandType.Text, SQL,NULL); Str. Clear ();}if(Remainder >0) {str. Append ("INSERT INTO batchtable values");  for(inti =0; i < remainder; i++) {str. AppendFormat ("(' {0} ', ' {1} ', ' {2} ', ' {3} ', ' {4} ', ' {5} ', ' {6} ', ' {7} ', ' {8} ', ' {9 } ', ' {ten} '),", datagridview1[0, I]. Value, datagridview1[1, I]. Value, datagridview1[2, I]. Value, datagridview1[3, I]. Value, datagridview1[4, I]. Value, datagridview1[5, I]. Value, datagridview1[6, I]. Value, datagridview1[7, I]. Value, datagridview1[8, I]. Value, datagridview1[9, I]. Value, datagridview1[Ten, I].    Value); }    stringsql = str. ToString (). TrimEnd (','); Sqlhelper.excutenonquery (CommandType.Text, SQL,NULL); Str. Clear ();}

Method Three: Use the SqlBulkCopy class to add data in bulk

The SqlBulkCopy class is located under the System.Data.SqlClient namespace and is excerpted from MSDN:Microsoft SQL Server provides abcpPopular command prompt utility for moving data from one table to another (the table can be either on the same server or on a different server).SqlBulkCopyclass allows you to write managed code solutions that provide similar functionality. There are other ways to load data into a SQL Server table, such as an INSERT statement, but in contrast SqlBulkCopy provides significant performance benefits. Use SqlBulkCopyClass can only write data to a SQL Server table. However, the data source is not limited to SQL Server; You can use any data source, as long as the data can be loaded into datatable  instance or you can use Idatareader  instance reads data.

Using the SqlBulkCopy class to add data in bulk will greatly increase efficiency. Tested (local area network), 1W data will take 0 minutes 0 seconds 292 milliseconds

 Public Static BOOLexcutenonquery (DataTable dt) {SqlConnection connection=NewSqlConnection (connstring); Connection.    Open (); SqlBulkCopy SqlBulkCopy=NewSqlBulkCopy (connection); SqlBulkCopy. Bulkcopytimeout= -;//the number of seconds allowed for the operation to complete before timing outSqlBulkCopy. batchsize = dt. Rows.Count;//number of rows in each batchSqlBulkCopy. DestinationTableName = dt. TableName;//name of the destination table on the server     for(inti =0; i < dt. Columns.count; i++) {SqlBulkCopy.  Columnmappings.add (i, I); //mappings define relationships between columns in the data source and columns in the target table} sqlbulkcopy.  WriteToServer (DT); //uploading DataTable data to a data tableconnection.    Close (); return true;}

. Comparison of several implementation methods of batch adding data in net