Several methods of batch import data in asp.net

Only know SQL Server support data Bulk INSERT, but Oracle, SQLite and MySQL are also supported, but Oracle needs to use the Orace.dataaccess driver, today put out several database of BULK INSERT solution.

First of all, Iprovider has a plug-in service interface Ibatcherprovider for bulk inserts, which has been mentioned in the previous article.

The code is as follows

Copy Code

<summary> Provides methods for data batch processing. </summary> public interface Ibatcherprovider:iproviderservice { <summary> Bulk inserts data from the <see cref= "DataTable"/> into the database. </summary> <param name= "DataTable" > <see cref= "DataTable"/> to be inserted in bulk. </param> <param name= "BatchSize" > The amount of data written per batch. </param> void Insert (DataTable datatable, int batchsize = 10000); }

One, SQL Server data BULK INSERT

The BULK insert of SQL Server is simple, use SqlBulkCopy, and the following is the implementation of this class:

The code is as follows

Copy Code

<summary> The method that is provided for System.Data.SqlClient for bulk operations. </summary> public sealed class Mssqlbatcher:ibatcherprovider { <summary> Gets or sets the context of the provider service. </summary> Public Servicecontext Servicecontext {get; set;} <summary> Bulk inserts data from the <see cref= "DataTable"/> into the database. </summary> <param name= "DataTable" > <see cref= "DataTable"/> to be inserted in bulk. </param> <param name= "BatchSize" > The amount of data written per batch. </param> public void Insert (DataTable datatable, int batchsize = 10000) { Checker.argumentnull (DataTable, "DataTable"); if (dataTable.Rows.Count = 0) { Return } using (var connection = (SqlConnection) ServiceContext.Database.CreateConnection ()) { Try { Connection. Tryopen (); Add a front and rear guide to the table name var tablename = Dbutility.formatbyquote (servicecontext.database.provider.getservice<isyntaxprovider> (), Datatable.tablename); using (var bulk = new SqlBulkCopy (connection, sqlbulkcopyoptions.keepidentity, NULL) { DestinationTableName = tablename, BatchSize = BatchSize }) { Loops all columns, adding mappings for bulk Datatable.eachcolumn (c => bulk. Columnmappings.add (C.columnname, C.columnname), C =>!c.autoincrement); Bulk. WriteToServer (dataTable); Bulk. Close (); } } catch (Exception exp) { throw new Batcherexception (exp); } Finally { Connection. Tryclose (); } } } }

The names of the columns in the SqlBulkCopy columnmappings are case sensitive, so be aware that the column names are consistent with the table when you construct the DataTable.

The above transaction is not used, the use of transactions will have a certain impact on performance, if you want to use transactions, you can set up sqlbulkcopyoptions.useinternaltransaction.

Second, Oracle data BULK INSERT

The code is as follows

Copy Code

System.Data.OracleClient does not support bulk inserts, so only the oracle.dataaccess component can be used as a provider. <summary> The Oracle.Data.Access component provides a method for bulk operations. </summary> public sealed class Oracleaccessbatcher:ibatcherprovider { <summary> Gets or sets the context of the provider service. </summary> Public Servicecontext Servicecontext {get; set;} <summary> Bulk inserts data from the <see cref= "DataTable"/> into the database. </summary> <param name= "DataTable" > <see cref= "DataTable"/> to be inserted in bulk. </param> <param name= "BatchSize" > The amount of data written per batch. </param> public void Insert (DataTable datatable, int batchsize = 10000) { Checker.argumentnull (DataTable, "DataTable"); if (dataTable.Rows.Count = 0) { Return } using (var connection = ServiceContext.Database.CreateConnection ()) { Try { Connection. Tryopen (); using (var command = ServiceContext.Database.Provider.DbProviderFactory.CreateCommand ()) { if (command = null) { throw new Batcherexception (New ArgumentException ("command")); } Command. Connection = Connection; Command.commandtext = generateinsersql (servicecontext.database, command, dataTable); Command. ExecuteNonQuery (); } } catch (Exception exp) { throw new Batcherexception (exp); } Finally { Connection. Tryclose (); } } } <summary> Generates an SQL statement that inserts data. </summary> <param name= "Database" ></param> <param name= "command" ></param> <param name= "Table" ></param> <returns></returns> private string Generateinsersql (idatabase database, DbCommand command, DataTable table) { var names = new StringBuilder (); var values = new StringBuilder (); Converts the data of a DataTable to an array of arrays var data = table. ToArray (); Setting the Arraybindcount Property Command. GetType (). GetProperty ("Arraybindcount"). SetValue (Command, table.) Rows.Count, NULL); var syntax = database. Provider.getservice<isyntaxprovider> (); for (var i = 0; i < table. Columns.count; i++) { var column = table. Columns[i]; var parameter = database. Provider.DbProviderFactory.CreateParameter (); if (parameter = null) { Continue } Parameter. parametername = column. ColumnName; Parameter. Direction = ParameterDirection.Input; Parameter. DbType = column. Datatype.getdbtype (); Parameter. Value = Data[i]; if (names. Length > 0) { Names. Append (","); Values. Append (","); } Names. AppendFormat ("{0}", Dbutility.formatbyquote (Syntax, column). ColumnName)); Values. AppendFormat ("{0}{1}", syntax.) Parameterprefix, column. ColumnName); Command. Parameters.Add (parameter); } return string. Format (INSERT into {0} ({1}) VALUES ({2}), Dbutility.formatbyquote (syntax, table. TableName), names, values); } }

The most important step is to turn the DataTable into an array of arrays, that is, object[][], the top of the array is the number of columns, the number of rows after the array, so the loop columns the following array as the parameter value, that is, the value of the parameter is an array. The INSERT statement is not the same as the general INSERT statement.

Third, sqlite data BULK Insert

SQLite the bulk of the insert just open the transaction can be, this specific principle is unknown.

The code is as follows

Copy Code

public sealed class Sqlitebatcher:ibatcherprovider { <summary> Gets or sets the context of the provider service. </summary> Public Servicecontext Servicecontext {get; set;} <summary> Bulk inserts data from the <see cref= "DataTable"/> into the database. </summary> <param name= "DataTable" > <see cref= "DataTable"/> to be inserted in bulk. </param> <param name= "BatchSize" > The amount of data written per batch. </param> public void Insert (DataTable datatable, int batchsize = 10000) { Checker.argumentnull (DataTable, "DataTable"); if (dataTable.Rows.Count = 0) { Return } using (var connection = ServiceContext.Database.CreateConnection ()) { Dbtransaction transcation = null; Try { Connection. Tryopen (); Transcation = connection. BeginTransaction (); using (var command = ServiceContext.Database.Provider.DbProviderFactory.CreateCommand ()) { if (command = null) { throw new Batcherexception (New ArgumentException ("command")); } Command. Connection = Connection; Command.commandtext = Generateinsersql (Servicecontext.database, dataTable); if (Command.commandtext = = string. Empty) { Return } var flag = new Assertflag (); Datatable.eachrow (Row => { var-i = flag. Asserttrue (); Processcommandparameters (dataTable, command, Row, and a); Command. ExecuteNonQuery (); }); } Transcation.commit (); } catch (Exception exp) { if (transcation!= null) { Transcation. Rollback (); } throw new Batcherexception (exp); } Finally { Connection. Tryclose (); } } } private void Processcommandparameters (DataTable DataTable, DbCommand command, DataRow Row, bool-a) { for (var c = 0; c < DataTable.Columns.Count; C + +) { DbParameter parameter; The first parameter was created to use the cache if (a) { parameter = ServiceContext.Database.Provider.DbProviderFactory.CreateParameter (); Parameter. ParameterName = Datatable.columns[c]. ColumnName; Command. Parameters.Add (parameter); } Else { parameter = command. PARAMETERS[C]; } Parameter. Value = Row[c]; } } <summary> Generates an SQL statement that inserts data. </summary> <param name= "Database" ></param> <param name= "Table" ></param> <returns></returns> private string Generateinsersql (Idatabase database, DataTable table) { var syntax = database. Provider.getservice<isyntaxprovider> (); var names = new StringBuilder (); var values = new StringBuilder (); var flag = new Assertflag (); Table. Eachcolumn (Column => { if (!flag. Asserttrue ()) { Names. Append (","); Values. Append (","); } Names. Append (Dbutility.formatbyquote (Syntax, column). ColumnName)); Values. AppendFormat ("{0}{1}", syntax.) Parameterprefix, column. ColumnName); }); return string. Format (INSERT into {0} ({1}) VALUES ({2}), Dbutility.formatbyquote (syntax, table. TableName), names, values); } }

Four,MySQL data bulk Insert

The code is as follows

Copy Code

<summary> A method for bulk operations provided for the Mysql.data component. </summary> public sealed class Mysqlbatcher:ibatcherprovider { <summary> Gets or sets the context of the provider service. </summary> Public Servicecontext Servicecontext {get; set;} <summary> Bulk inserts data from the <see cref= "DataTable"/> into the database. </summary> <param name= "DataTable" > <see cref= "DataTable"/> to be inserted in bulk. </param> <param name= "BatchSize" > The amount of data written per batch. </param> public void Insert (DataTable datatable, int batchsize = 10000) { Checker.argumentnull (DataTable, "DataTable"); if (dataTable.Rows.Count = 0) { Return } using (var connection = ServiceContext.Database.CreateConnection ()) { Try { Connection. Tryopen (); using (var command = ServiceContext.Database.Provider.DbProviderFactory.CreateCommand ()) { if (command = null) { throw new Batcherexception (New ArgumentException ("command")); } Command. Connection = Connection; Command.commandtext = generateinsersql (servicecontext.database, command, dataTable); if (Command.commandtext = = string. Empty) { Return } Command. ExecuteNonQuery (); } } catch (Exception exp) { throw new Batcherexception (exp); } Finally { Connection. Tryclose (); } } } <summary> Generates an SQL statement that inserts data. </summary> <param name= "Database" ></param> <param name= "command" ></param> <param name= "Table" ></param> <returns></returns> private string Generateinsersql (idatabase database, DbCommand command, DataTable table) { var names = new StringBuilder (); var values = new StringBuilder (); var types = new list<dbtype> (); var count = table. Columns.count; var syntax = database. Provider.getservice<isyntaxprovider> (); Table. Eachcolumn (c => { if (names. Length > 0) { Names. Append (","); } Names. AppendFormat ("{0}", Dbutility.formatbyquote (Syntax, c.columnname)); Types. ADD (C.datatype.getdbtype ()); }); var i = 0; foreach (DataRow row in table). Rows) { if (i > 0) { Values. Append (","); } Values. Append ("("); for (var j = 0; J < Count; J + +) { if (J > 0) { Values. Append (","); } var isstrtype = Isstringtype (Types[j]); var parameter = CreateParameter (database. Provider, Isstrtype, Types[j], row[j], syntax. Parameterprefix, I, J); if (parameter!= null) { Values. Append (parameter. ParameterName); Command. Parameters.Add (parameter); } else if (isstrtype) { Values. AppendFormat ("' {0} '", Row[j]); } Else { Values. Append (Row[j]); } } Values. Append (")"); i++; } return string. Format (INSERT into {0} ({1}) VALUES {2}, Dbutility.formatbyquote (Syntax, table). TableName), names, values); } <summary> Determines whether it is a string category. </summary> <param name= "DbType" ></param> <returns></returns> private bool Isstringtype (DbType DbType) { return DbType = = Dbtype.ansistring | | DbType = = Dbtype.ansistringfixedlength | | DbType = = Dbtype.string | | DbType = = Dbtype.stringfixedlength; } <summary> Create parameters. </summary> <param name= "Provider" ></param> <param name= "Isstrtype" ></param> <param name= "DbType" ></param> <param name= "Value" ></param> <param name= "Parprefix" ></param> <param name= "Row" ></param> <param name= "Col" ></param> <returns></returns> Private DbParameter CreateParameter (Iprovider provider, bool Isstrtype, DbType DbType, object value, char parprefix, int r ow, int col) { If all parameters are generated, the speed is slow, so only the data type is a string (containing the ' number ') and a date type to add the parameter if (Isstrtype && value. ToString (). IndexOf (' ")!=-1) | | DbType = = dbtype.datetime) { var name = string. Format ("{0}p_{1}_{2}", Parprefix, Row, col); var parameter = provider. Dbproviderfactory.createparameter (); Parameter. ParameterName = name; Parameter. Direction = ParameterDirection.Input; Parameter. DbType = DbType; Parameter. Value = value; return parameter; } return null; } }

MySQL's bulk insert is to write values all in the statement's values, for example, insert Batcher (ID, name) VALUES (1, ' 1 ', 2, ' 2 ', 3, ' 3 ', ...). 10, ' 10 ').

Five, test

Next, write a test case to see the effect of using bulk inserts.

The code is as follows

Copy Code

[Test] public void Testbatchinsert () { Console.WriteLine (Timewatcher.watch () => Invoketest (Database => { var table = new DataTable ("Batcher"); Table. Columns.Add ("Id", typeof (int)); Table. Columns.Add ("Name1", typeof (String)); Table. Columns.Add ("Name2", typeof (String)); Table. Columns.Add ("Name3", typeof (String)); Table. Columns.Add ("Name4", typeof (String)); Construct 100,000 data for (var i = 0; i < 100000; i++) { Table. Rows.Add (i, i.ToString (), i.ToString (), i.ToString (), i.tostring ()); } Get Ibatcherprovider var batcher = database. Provider.getservice<ibatcherprovider> (); if (Batcher = null) { Console.WriteLine (bulk inserts are not supported.) "); } Else { Batcher. Insert (table); } Output the amount of data in the Batcher table var sql = new SqlCommand ("Select COUNT (1) from Batcher"); Console.WriteLine ("There are currently {0} data", database. ExecuteScalar (SQL)); }))); }

The following table lists the elapsed time for four databases to generate 100,000 data

Database	Consumption time
MsSql	00:00:02.9376300
Oracle	00:00:01.5155959
Sqlite	00:00:01.6275634
Mysql	00:00:05.4166891