MySql 使用MySqlBulkLoader批量插入

数据库批量插入Oracle中有 OracleBulkCopy,SQL当然也有个SqlBulkCopy .这里有介绍就不说，网上有非常详细的例子，大家可去搜索下，可是MySql确没有MySqlBulkCopy这个，网上找了很久也没找到。找到了一个 MySqlBulkLoader

不废话了，直接上代码

 

#region 批量插入数据
        ///// <summary>
        ///// 批量插入收集库件级文书档案信息实体（批量）
        ///// </summary>
        ///// <param name="dataTable">数据表</param>
        ///// <returns></returns>
        public int BulkInsert(DataTable table)
        {
            int insertCount = 0;
            try
            {
                table.TableName = "tb_jj_ws_collect";//数据库中的表名

                string connectionString = db.Database.Connection.ConnectionString;

                if (string.IsNullOrEmpty(table.TableName)) throw new Exception("请给DataTable的TableName属性附上表名称");

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

                string tmpPath = Directory.GetCurrentDirectory() + "\\UpTemp";
                if (!Directory.Exists(tmpPath))
                    Directory.CreateDirectory(tmpPath);
                tmpPath = Path.Combine(tmpPath, "Temp.csv");//csv文件临时目录

                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))
                {
                    try
                    {
                        Stopwatch stopwatch = new Stopwatch();
                        stopwatch.Start();
                        conn.Open();
                        MySqlBulkLoader bulk = new MySqlBulkLoader(conn)
                        {
                            FieldTerminator = ",",
                            FieldQuotationCharacter = '"',
                            EscapeCharacter = '"',
                            LineTerminator = "\r\n",
                            FileName = tmpPath,
                            NumberOfLinesToSkip = 0,
                            TableName = table.TableName,

                        };
                        bulk.Columns.AddRange(columns);//根据标题列对应插入
                        insertCount = bulk.Load();
                        stopwatch.Stop();
                        //Console.WriteLine("耗时:{0}", stopwatch.ElapsedMilliseconds);
                    }
                    catch (MySqlException ex)
                    {
                        throw ex;
                    }
                }
                File.Delete(tmpPath);
            }
            catch (Exception ex)
            {
                OnLogError("批量插入收集库件级文书档案信息实体（批量）时异常。", ex);
            }

            
            return insertCount;
        }


        ///将DataTable转换为标准的CSV  
        /// </summary>  
        /// <param name="table">数据表</param>  
        /// <returns>返回标准的CSV</returns>  
        private static string DataTableToCsv(DataTable table)
         {
             //以半角逗号（即,）作分隔符，列为空也要表达其存在。  
             //列内容如存在半角逗号（即,）则用半角引号（即""）将该字段值包含起来。  
             //列内容如存在半角引号（即"）则应替换成半角双引号（""）转义，并用半角引号（即""）将该字段值包含起来。  
             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());
                 }
                 sb.AppendLine();
             }
             return sb.ToString();
         }
        
        #endregion