MysqlMySqlBulkLoader在.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= &＃39;"&＃39;,EscapeCharacter = &＃39;"&＃39;,LineTerminator = Environment.NewLine, //"\r\n",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

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