Example code for implementing large data bulk inserts for several databases in C #

This article mainly introduces C # implementation of several databases of large data bulk INSERT, mainly including SQL Server, Oracle, SQLite and MySQL, interested to understand.

Only before that SQL Server support data Bulk INSERT, but not surprisingly, Oracle, SQLite and MySQL is also supported, but Oracle needs to use the Orace.dataaccess driver, today to post a number of database batch insert solution.

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

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

First, SQL Server data Bulk INSERT

SQL Server Bulk Insert is simple, using SqlBulkCopy, the following is the implementation of this class:

<summary>///For bulk operations provided by System.Data.SqlClient. </summary> public sealed class Mssqlbatcher:ibatcherprovider {//<summary>///Get or Set provider service     Context.      </summary> public servicecontext Servicecontext {get; set;}     <summary>///<see cref= "DataTable"/> data into the database in bulk. </summary>//<param Name= "DataTable" > <see cref= "DataTable"/> to be inserted in bulk. </param>//<param name= "batchsize" > amount of data written per batch. </param> public void Insert (DataTable datatable, int batchsize = 10000) {Checker.argumentnull (datatab       Le, "dataTable");       if (DataTable.Rows.Count = = 0) {return;           } using (var connection = (SqlConnection) ServiceContext.Database.CreateConnection ()) {try { Connection.           Tryopen (); Add a front and back 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) {Destinati             Ontablename = tableName, batchsize = batchsize}) {//Loop all columns, add 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 above does not use transactions, the use of transactions has a certain impact on performance, if you want to use transactions, you can set sqlbulkcopyoptions.useinternaltransaction.

Second, Oracle data BULK INSERT

System.Data.OracleClient does not support bulk inserts, so only the oracle.dataaccess component can be used as the provider.

<summary>////Oracle.Data.Access component provides methods for bulk operations. </summary> public sealed class Oracleaccessbatcher:ibatcherprovider {//<summary>//Get or set     The context of the provisioning provider service.      </summary> public servicecontext Servicecontext {get; set;}     <summary>///<see cref= "DataTable"/> data into the database in bulk. </summary>//<param Name= "DataTable" > <see cref= "DataTable"/> to be inserted in bulk. </param>//<param name= "batchsize" > amount of data written per batch. </param> public void Insert (DataTable datatable, int batchsize = 10000) {Checker.argumentnull (datatab       Le, "dataTable");       if (DataTable.Rows.Count = = 0) {return; } using (var connection = ServiceContext.Database.CreateConnection ()) {try {Connec tion.           Tryopen (); using (var command = ServiceContext.Database.Provider.DbProviderFactory.CreateCommand ()) {if (comma nd == 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> Generate SQL statements that insert data. </summary>//<param name= "Database" ></param>//<param name= "command" ></param&gt     ; <param name= "TABLE" ></param>///<returns></returns> private string Generateinsersql (I       Database database, DbCommand command, DataTable table) {var names = new StringBuilder ();       var values = new StringBuilder (); Converts the data of a DataTable into an array of the arrays var data = table.   ToArray ();     Set 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 convert the DataTable into an array representation of the array, that is, object[][], the first array of superscript is the number of columns, after the array is the number of rows, so the loop columns after the array as the parameter value, that is, the value of the parameter is an array. The INSERT statement is not the same as the normal INSERT statement.

Third, SQLite data BULK Insert

SQLite's BULK insert only needs to open the transaction, this specific principle is unknown.

public sealed class Sqlitebatcher:ibatcherprovider {//<summary>/////To get or set the context of the provider service.      </summary> public servicecontext Servicecontext {get; set;}     <summary>///<see cref= "DataTable"/> data into the database in bulk. </summary>//<param Name= "DataTable" > <see cref= "DataTable"/> to be inserted in bulk. </param>//<param name= "batchsize" > amount of data written per batch. </param> public void Insert (DataTable datatable, int batchsize = 10000) {Checker.argumentnull (datatab       Le, "dataTable");       if (DataTable.Rows.Count = = 0) {return; } using (var connection = ServiceContext.Database.CreateConnection ()) {dbtransaction transcation = Nu         ll 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 first = flag.                 Asserttrue ();                 Processcommandparameters (dataTable, command, Row, first); 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 first) {for (var c = 0; C < dataTable.Columns.Co Unt         C + +) {DbParameter parameter; The first time you create a parameter is to use the cache if {parameter = ServiceContext.Database.Provider.DbProviderFactory.Cre           Ateparameter (); Parameter. ParameterName = Datatable.columns[c].           ColumnName; Command.         Parameters.Add (parameter); } else {parameter = command.         PARAMETERS[C]; } parameter.       Value = Row[c];     }}///<summary> Generate SQL statements that insert 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

<summary>///The method for bulk operation provided by the Mysql.data component. </summary> public sealed class Mysqlbatcher:ibatcherprovider {//<summary>///Get or Set provider service     Context.      </summary> public servicecontext Servicecontext {get; set;}     <summary>///<see cref= "DataTable"/> data into the database in bulk. </summary>//<param Name= "DataTable" > <see cref= "DataTable"/> to be inserted in bulk. </param>//<param name= "batchsize" > amount of data written per batch. </param> public void Insert (DataTable datatable, int batchsize = 10000) {Checker.argumentnull (datatab       Le, "dataTable");       if (DataTable.Rows.Count = = 0) {return; } using (var connection = ServiceContext.Database.CreateConnection ()) {try {Connec tion.           Tryopen (); using (var command = ServiceContext.Database.Provider.DbProviderFactory.CreateCommand ()) {if (comma      nd = 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> Generate SQL statements that insert data. </summary>//<param name= "Database" ></param>//<param name= "command" ></param&gt     ; <param name= "TABLE" ></param>///<returns></returns> private string Generateinsersql (I       Database 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 | | db Type = = Dbtype.string | |     DbType = = Dbtype.stringfixedlength;     }///<summary>//Create parameters. </summary>//<param name= "provider" ></param>//<param name= "Isstrtype" ></param&     Gt <param NAme= "DbType" ></param>//<param name= "value" ></param>//<param name= "Parprefix" >< /param>//<param name= "Row" ></param>///<param name= "col" ></param>//<retur ns></returns> private DbParameter CreateParameter (Iprovider provider, bool Isstrtype, DbType DbType, Object v Alue, char parprefix, int row, int col) {//If all parameters are generated, the speed is very slow, so only the parameter if the data type is string (contains ' number) and date type (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; }   }

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

V. Testing

Next, write a test case to look at the effect of using BULK INSERT.

    public void Testbatchinsert () {Console.WriteLine (Timewatcher.watch () = invoketest (Database =&G           T             {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)); Constructs 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 insert is not supported.             "); } else {Batcher.             Insert (table);             }//Output batcher The amount of data in the tablevar sql = new SqlCommand ("Select COUNT (1) from Batcher"); Console.WriteLine ("{0} data currently in total", database.)            ExecuteScalar (SQL));     }))); }

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

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