Big data in several databases BULK insert "Go"

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        Sets the context for 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.ARGUMENTN            Ull (DataTable, "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 namevar tableName = Dbutility.formatbyquote (servicecontext.database.provider.getservice<isyntaxprovide                    R> (), datatable.tablename);                            using (var bulk = new SqlBulkCopy (connection, sqlbulkcopyoptions.keepidentity, null) {                        DestinationTableName = 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>        Gets or sets 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.ARGUMENTN            Ull (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 (comman D = = 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" ></pa ram>//<param name= "table" ></param>//<returns></returns> private string Generateinsersql (Idatabase database, DbCommand C            Ommand, 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.ARGUMENTN            Ull (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 Argumentex                        Ception ("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.Count; C + +) {Dbparamet                ER parameter; The first time you create a parameter is to use the cache if {parameter = ServiceContext.Database.Provide                    R.dbproviderfactory.createparameter (); 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" ></para m>//<returns></returns> private string Generateinsersql (Idatabase database, DataTable tabl e) {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); }    }

Iv. 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        Sets the context for 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.ARGUMENTN            Ull (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> Generate SQL statements that insert data. </summary>//<param name= "Database" ></param>//<param name= "command" ></param>//<param name= "t Able "></param>//<returns></returns> private string Generateinsersql (Idatabase Databa            SE, 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.ansi Stringfixedlength | | 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 NA Me= "Col"></param>//<returns></returns> private DbParameter CreateParameter (Iprovider provide R, BOOL Isstrtype, DbType DbType, object value, char parprefix, int row, int col) {//If all parameters are generated, the speed is very slow because This only adds a parameter if the data type is a string (contains ' number) and a 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.

[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));                            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 table data volume var 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

Big data in several databases BULK insert "Go"