Batch of data import database, as little as possible to access the database, high-performance database storage.
Use SqlBulkCopy to process stored data. SqlBulkCopy storage of large volumes of data is highly efficient, storing data tables in memory directly into the database without having to insert data into the database once.
After several experiments, millions other data sheets can be fully stored in a database in a matter of seconds, faster than the traditional insert into method, many times faster.
Here's a look at the code:
/// <summary> ///BULK INSERT Data/// </summary> /// <param name= "connectionString" >Connection Database String</param> /// <param name= "TableName" >Table name</param> /// <param name= "DT" >need to BULK INSERT database DataTable data Source</param> /// <param name= "Bulkcopytimeout" >overflow time for connected databases</param> /// <returns></returns> Public Static BOOLSqlbulkcopybydatatable (stringConnectionString,stringTableName, DataTable DT,intBulkcopytimeout = -) { Try { using(SqlBulkCopy SqlBulkCopy =NewSqlBulkCopy (connectionString, sqlbulkcopyoptions.useinternaltransaction)) {Sqlbulkcopy.destinationtablename=TableName; Sqlbulkcopy.bulkcopytimeout=bulkcopytimeout; for(inti =0; i < dt. Columns.count; i++) {sqlbulkCopy.ColumnMappings.Add (dt. Columns[i]. ColumnName, dt. Columns[i]. ColumnName); } sqlbulkcopy.writetoserver (DT); Sqlbulkcopy.close ();//Close Connection return true; } } Catch(System.Exception ex) {Throwex; } } /// <summary> ///BULK INSERT Data/// </summary> /// <param name= "connectionString" >Connection Database String</param> /// <param name= "TableName" >Table name</param> /// <param name= "DT" >need to BULK INSERT database DataTable data Source</param> /// <param name= "BatchSize" >How many data to insert in a batch</param> /// <param name= "Bulkcopytimeout" >overflow time for connected databases</param> /// <returns></returns> Public Static BOOLSqlbulkcopybydatatable (stringConnectionString,stringTableName, DataTable DT,intBatchSize,intbulkcopytimeout= -) { Try { using(SqlBulkCopy SqlBulkCopy =NewSqlBulkCopy (connectionString, sqlbulkcopyoptions.useinternaltransaction)) {Sqlbulkcopy.batchsize=batchsize; Sqlbulkcopy.destinationtablename=TableName; Sqlbulkcopy.bulkcopytimeout=bulkcopytimeout; for(inti =0; i < dt. Columns.count; i++) {sqlbulkCopy.ColumnMappings.Add (dt. Columns[i]. ColumnName, dt. Columns[i]. ColumnName); } sqlbulkcopy.writetoserver (DT); Sqlbulkcopy.close ();//Close Connection return true; } } Catch(System.Exception ex) {Throwex; } }
. NET BULK insert data into SQL Server database, SqlBulkCopy class BULK insert big data into database