In C #, it is sometimes necessary to bulk insert in-memory data into database tables, and bulk inserts with a For loop are time consuming and operate the database frequently.
A For loop insertion is available for small amounts of data, but for loop insertions is not recommended for large data volumes, and SQL block processing is recommended.
Block processing takes less time and does not operate frequently on the database, but it is important to note that the columns in the DataTable must be exactly the same as the columns in the table.
The following code is a function of bulk INSERT, self-test available.
1 #regionUse SqlBulkCopy to BULK insert data from a DataTable into a database2 /// <summary> 3 ///Note: The columns in the DataTable need to be exactly the same as the columns in the database table. 4 ///self-test is available. 5 /// </summary> 6 /// <param name= "Constr" >Database connection String</param>7 /// <param name= "strTableName" >the corresponding table name in the database</param> 8 /// <param name= "Dtdata" >Data Set</param> 9 Public Static voidSqlbulkcopyinsert (stringConstr,stringstrTableName, DataTable dtdata)Ten { One Try A { - using(SqlBulkCopy sqlrevdbulkcopy =NewSqlBulkCopy (CONSTR))//Reference SqlBulkCopy - { theSqlrevdbulkcopy.destinationtablename = strTableName;//the corresponding table name in the database -Sqlrevdbulkcopy.notifyafter = DtData.Rows.Count;//There are several rows of data -Sqlrevdbulkcopy.writetoserver (Dtdata);//Data Import Database -Sqlrevdbulkcopy.close ();//Close Connection + } - } + Catch(Exception ex) A { at Throw(ex); - } - } - #endregion
View Code
C # Methods for bulk inserting data from a DataTable table into a database table