Big Data Bulk Insert database usage (SqlBulkCopy) more efficient

SqlBulkCopy class is System.Data.SqlClient under the class, we are not used in development, and even do not know that there is such a class exists, but it is more than SQL INSERT, transaction BULK INSERT, SQL bulk splicing is much faster than the call stored procedure insert Convenient

Here's how to use it:

public static bool Executetransactionscopeinsert (DataTable DT, int batchsize)

{

• int count = dt.  Rows.Count;
• string tableName = "TestTable";
• int copytimeout = 600;
• BOOL flag = false;
• Try
• {
• using (SqlConnection CN = New SqlConnection (connectionString))
• {
• using (TransactionScope scope = new TransactionScope ())
• {
• cn. Open ();
• using (SqlBulkCopy SBC = New SqlBulkCopy (CN))
• {
• //Name of the target table on the server
• Sbc. DestinationTableName = TableName;
• Sbc. BatchSize = batchsize;
• Sbc. Bulkcopytimeout = Copytimeout;
• For (int i = 0; i < dt. Columns.count; i++)
• {
• //Column mappings define relationships between columns in the data source and columns in the target table
• Sbc. Columnmappings.add (dt. Columns[i]. ColumnName, dt. Columns[i]. ColumnName);
• }
• Sbc. WriteToServer (DT);
• Flag = true;
• Scope.complete (); //Valid transactions
• }
• }
• }
• }
• catch (Exception ex)
• {
• Loghelper.error (ex. Message);
• return false;
• }
• return flag;
• }  

SqlBulkCopy principle is the use of SQL Server BCP protocol for data bulk replication, combined with transactions, in our case, about each batch of 800 is the balance point, performance than insert increased by 100 times, And more than 7 times times more performance than the same case with transactional bulk inserts

Here is the link from the source author experiment: http://blog.csdn.net/amandag/article/details/6393717

Big Data Bulk Insert database usage (SqlBulkCopy) more efficient