Mysql MySqlBulkLoader在.NET平台下的批量插入

 /// 

        /// 批量导入
        /// 
        /// 
        /// 
        public bool TranBatchImportData(DataSet dataSet)
        {
           string fileServerPath = System.Web.Hosting.HostingEnvironment.MapPath("/");
            if (dataSet != null && dataSet.Tables.Count > 0)
            {
                using (MySqlConnection conn = (MySqlConnection)GetSQLConnection())
                {
                    if (conn.State != ConnectionState.Open)
                    {
                        conn.Open();
                    }
                    foreach (DataTable dt in dataSet.Tables)
                    {
                        if (dt.Rows.Count == 0) continue;

                        string tmpPath = fileServerPath + "Uploads\UpTemp";
                        if (!Directory.Exists(tmpPath))
                            Directory.CreateDirectory(tmpPath);
                        tmpPath = Path.Combine(tmpPath, "Temp.csv");//csv文件临时目录
                        if (File.Exists(tmpPath))
                        {
                            File.Delete(tmpPath);
                        }
                        string csv = DataTableToCsv(dt);
                        File.WriteAllText(tmpPath, csv, new UTF8Encoding(false));


                        int insertCount = 0;
                        //MySqlTransaction tran = conn.BeginTransaction();
                        MySqlBulkLoader bulk = new MySqlBulkLoader(conn)
                        {
                            FieldTerminator = ",",
                            FieldQuotationCharacter = '"',
                            EscapeCharacter = '"',
                            LineTerminator = Environment.NewLine, //"rn",
                            FileName = tmpPath,
                            Local = true,
                            NumberOfLinesToSkip = 0,
                            TableName = dt.TableName,
                            CharacterSet = "UTF8"
                        };
                        try
                        {
                            bulk.Columns.AddRange(dt.Columns.Cast().Select(colum => colum.ColumnName).ToList());
                            insertCount = bulk.Load();
                        }
                        catch (MySqlException ex)
                        {
                            throw ex;
                        }
                        finally
                        {
                            if (File.Exists(tmpPath))
                            {
                                File.Delete(tmpPath);
                            }
                        }

                    }
                    return true;
                }
            }

            return false;

      }


        ///将DataTable转换为标准的CSV  
        ///   
        /// 数据表  
        /// 返回标准的CSV  
        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].Equals(DBNull.Value) ? "NULL" : (""" + row[colum].ToString().Replace(""", """") + """));
                    }
                    else sb.Append(row[colum].Equals(DBNull.Value) ? "NULL" : row[colum].ToString());
                }
                sb.AppendLine();
            }
            return sb.ToString();
        }

这个地方特殊说明一下，平时用的Encoding.UTF8 会导致生成的csv变为UTF-8 BOM的类型，会导致插入数据库的数据出现错误。所以选择采用new UTF8Encoding(false)这种写法。