Mysql使用MySqlBulkLoader批量插入数据

        https://blog.csdn.net/kisscatforever/article/details/66476768

        

        /// <summary>
        /// Mysql批量插入数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        public int BatchInsert<T>(List<T> list)
        {
            var pdco = PocoData.ForType(list.FirstOrDefault().GetType(), _defaultMapper);
            //转为DataTable
            DataTable dt = new DataTable();
            dt = ConvertToDataTable(list);

            string tableName = pdco.TableInfo.TableName;
            var path = $@"D:/Attdence/";
            var fileName = $@"{path}{tableName}{DateTime.Now:yyMMddHHmmssffff}.csv";
            //dataTable转为Cvs
            ToCsv(dt, tableName, path, fileName);

            var columns = dt.Columns.Cast<DataColumn>().Select(colum => colum.ColumnName).ToList();
            using (MySqlConnection conn = new MySqlConnection(_connectionString))
            {
                
                MySqlBulkLoader bulk = new MySqlBulkLoader(conn)
                {
                    FieldTerminator = ",",
                    FieldQuotationCharacter = '"',
                    EscapeCharacter = '"',
                    LineTerminator = "\r\n",
                    FileName = fileName,
                    NumberOfLinesToSkip = 0,
                    TableName = tableName
                };
                bulk.Columns.AddRange(columns);
                var result = bulk.Load();
                try
                {
                    Action act = () => { File.Delete(fileName); };
                    act.BeginInvoke(null, null);
                }
                catch (Exception e)
                {
                    Log4NetHelp.Error(e);
                }
                return result;
            }
        }

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