C # Several kinds of database large data bulk inserts (SQL Server, Oracle, SQLite and MySQL) _c# tutorials

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.

<summary>
  ///provides methods of data batch processing.
  ///</summary> Public
  interface Ibatcherprovider:iproviderservice
  {
    ///<summary>
    ///inserts data from the <see cref= "DataTable"/> into the database in bulk.
    ///</summary>
    ///<param name= "DataTable" > <see "DataTable" cref= to be inserted in batches. </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:

<summary>///provides a method for bulk operations for System.Data.SqlClient. </summary> public sealed class Mssqlbatcher:ibatcherprovider {///<summary>///Gets or sets the provider service's
    Context.

    </summary> public servicecontext Servicecontext {get; set;}
    <summary>///inserts data from the <see cref= "DataTable"/> into the database in batches. </summary>///<param name= "DataTable" > <see cref= "DataTable" to be inserted in batches. </param>///<param name= "BatchSize" > The 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 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) {Destinati
            Ontablename = tablename, batchsize = batchsize}) {//Looping 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 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

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

<summary>///a method for bulk operations provided by the Oracle.Data.Access component. </summary> public sealed class Oracleaccessbatcher:ibatcherprovider {///<summary>///get or set
    The context in which the provider service is placed.

    </summary> public servicecontext Servicecontext {get; set;}
    <summary>///inserts data from the <see cref= "DataTable"/> into the database in batches. </summary>///<param name= "DataTable" > <see cref= "DataTable" to be inserted in batches. </param>///<param name= "BatchSize" > The 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>///generates 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 to an array of arrays var data = table.

ToArray ();      Sets 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.

public sealed class Sqlitebatcher:ibatcherprovider {///<summary>///Gets or sets the context of the provider service.

    </summary> public servicecontext Servicecontext {get; set;}
    <summary>///inserts data from the <see cref= "DataTable"/> into the database in batches. </summary>///<param name= "DataTable" > <see cref= "DataTable" to be inserted in batches. </param>///<param name= "BatchSize" > The 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-i = flag.
                Asserttrue ();
                Processcommandparameters (dataTable, command, Row, and a); Command.
              ExecuteNonQuery ();
          });
        } transcation.commit (); The 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 i) {for (var c = 0; C < datatable.columns.c Ount;
        C + +) {DbParameter parameter; The first parameter was created to use the cache if (i) {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>///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

<summary>///a method for bulk operations provided for the Mysql.data component. </summary> public sealed class Mysqlbatcher:ibatcherprovider {///<summary>///Gets or sets the provider service's
    Context.

    </summary> public servicecontext Servicecontext {get; set;}
    <summary>///inserts data from the <see cref= "DataTable"/> into the database in batches. </summary>///<param name= "DataTable" > <see cref= "DataTable" to be inserted in batches. </param>///<param name= "BatchSize" > The 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>///generates 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 || DbType = = 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" & gt;</param>///<param name= "Row" ></param>///<param name= "col" ></param>///&L t;returns></returns> private DbParameter CreateParameter (Iprovider provider, bool Isstrtype, DbType DbType, O Bject value, char parprefix, int row, int col) {//If all parameters are generated, the speed will be slow, so only add parameters if the data type is a string (containing the ' number) and date type (IsS Trtype && 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.

 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 amount 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