MySQL uses mysqlbulkloader batch insertion

2023-01-22   ES  

database batch insertion into Oracle is oraclebulkcopy. Of course, there is also a SQLBULKCOPY. There is an introduction here. There are very detailed examples on the Internet. Essence I found a MySQLBULKLOADER

No nonsense, directly apply the code

#regionbatch insert data///// <summary>
        ///// Batch Inserts into the collection of library -level document file information entities (batch)///// </summary>
        ///// <Param name = "datatable"> Data table </param>
        ///// <returns></returns>
        public int BulkInsert(DataTable table)
            int insertCount = 0;
                table.TableName = "tb_jj_ws_collect";//Database name

                string connectionString = db.Database.Connection.ConnectionString;

                if (string.IsNullOrEmpty(table.TableName)) throw new Exception("Please attach a table name to the Tableename attribute of DataTable");

                if (table.Rows.Count == 0) return 0;

                string tmpPath = Directory.GetCurrentDirectory() + "\\UpTemp";
                if (!Directory.Exists(tmpPath))
                tmpPath = Path.Combine(tmpPath, "Temp.csv");//csv file temporary directory

                string csv = DataTableToCsv(table);
                File.WriteAllText(tmpPath, csv);

                var columns = table.Columns.Cast<DataColumn>().Select(_columns => _columns.ColumnName).ToList();

                using (MySqlConnection conn = new MySqlConnection(connectionString))
                        Stopwatch stopwatch = new Stopwatch();
                        MySqlBulkLoader bulk = new MySqlBulkLoader(conn)
                            FieldTerminator = ",",
                            FieldQuotationCharacter = '"',
                            EscapeCharacter = '"',
                            LineTerminator = "\r\n",
                            FileName = tmpPath,
                            NumberOfLinesToSkip = 0,
                            TableName = table.TableName,

                        bulk.Columns.AddRange(columns);//According to the title column, insertion
                        insertCount = bulk.Load();
                        //console.writeline ("Time: {0}", Stopwatch.lapSedmilliseConds);
                    catch (MySqlException ex)
                        throw ex;
            catch (Exception ex)
                OnLogError("Batch insertion into the collection of library -level document file information entities (batches).", ex);

            return insertCount;

        ///Convert DataTable to standard CSV/// </summary>  
        /// <param name="table">data table</param>  
        /// <returns>Return to the standard CSV</returns>  
        private static string DataTableToCsv(DataTable table)
             //Use the semi -angle comma (that is,) as a separator, which is listed as an empty existence.//column content, if there is a half -angle comma (that is,), use the semi -angle quotation marks (ie "") to contain the value of the field. 

 The content of//The content of the semi -angle quotation (that is, ") should be replaced with a semi -angle dual quotation marks (" ") to the righteousness, and the half -angle quotation marks (that is," ") include this field value.
             StringBuilder sb = new StringBuilder();
             DataColumn colum;
             foreach (DataRow row in table.Rows)
                 for (int i = 0; i<table.Columns.Count; i++)
                     colum = table.Columns[i];
                     if (i != 0) sb.Append(",");
                     if (colum.DataType == typeof(string) && row[colum].ToString().Contains(","))
                         sb.Append("\"" + row[colum].ToString().Replace("\"", "\"\"") + "\"");
                     else sb.Append(row[colum].ToString());
             return sb.ToString();



Random Posts

Three ways to solve the mobile browser HTML audio cannot be automatically played automatically

User Center -Modify User Information Leonjinhai


The stack and queue of algorithm series (1): Design a stack to get the maximum value and minimum value in the stack

MySQL-Common Time Date Function