1 public bool TranBatchImportData(DataSet dataSet) 2 { 3 string fileServerPath = System.Web.Hosting.HostingEnvironment.MapPath("/"); 4 if (dataSet != null && dataSet.Tables.Count > 0) 5 { 6 using (MySqlConnection conn = (MySqlConnection)GetSQLConnection()) 7 { 8 if (conn.State != ConnectionState.Open) 9 { 10 conn.Open(); 11 } 12 foreach (DataTable dt in dataSet.Tables) 13 { 14 if (dt.Rows.Count == 0) continue; 15 16 string tmpPath = fileServerPath + "Uploads\\UpTemp"; 17 if (!Directory.Exists(tmpPath)) 18 Directory.CreateDirectory(tmpPath); 19 tmpPath = Path.Combine(tmpPath, "Temp.csv");//csv文件临时目录 20 if (File.Exists(tmpPath)) 21 { 22 File.Delete(tmpPath); 23 } 24 string csv = DataTableToCsv(dt); 25 File.WriteAllText(tmpPath, csv, new UTF8Encoding(false)); 26 27 28 int insertCount = 0; 29 //MySqlTransaction tran = conn.BeginTransaction(); 30 MySqlBulkLoader bulk = new MySqlBulkLoader(conn) 31 { 32 FieldTerminator = ",", 33 FieldQuotationCharacter = '"', 34 EscapeCharacter = '"', 35 LineTerminator = Environment.NewLine, //"\r\n", 36 FileName = tmpPath, 37 //Local = true, 38 NumberOfLinesToSkip = 0, 39 TableName = dt.TableName, 40 CharacterSet = "utf8" 41 }; 42 try 43 { 44 bulk.Columns.AddRange(dt.Columns.Cast<DataColumn>().Select(colum => colum.ColumnName).ToList()); 45 insertCount = bulk.Load(); 46 } 47 catch (MySqlException ex) 48 { 49 throw ex; 50 } 51 finally 52 { 53 if (File.Exists(tmpPath)) 54 { 55 File.Delete(tmpPath); 56 } 57 } 58 59 } 60 return true; 61 } 62 } 63 64 return false; 65 }View Code
1 private static string DataTableToCsv(DataTable table) 2 { 3 //以半角逗号(即,)作分隔符,列为空也要表达其存在。 4 //列内容如存在半角逗号(即,)则用半角引号(即"")将该字段值包含起来。 5 //列内容如存在半角引号(即")则应替换成半角双引号("")转义,并用半角引号(即"")将该字段值包含起来。 6 StringBuilder sb = new StringBuilder(); 7 DataColumn colum; 8 foreach (DataRow row in table.Rows) 9 { 10 for (int i = 0; i < table.Columns.Count; i++) 11 { 12 colum = table.Columns[i]; 13 if (i != 0) sb.Append(","); 14 if (colum.DataType == typeof(string) && row[colum].ToString().Contains(",")) 15 { 16 sb.Append(row[colum].Equals(DBNull.Value) ? "NULL" : ("\"" + row[colum].ToString().Replace("\"", "\"\"") + "\"")); 17 } 18 else sb.Append(row[colum].Equals(DBNull.Value) ? "NULL" : row[colum].ToString()); 19 } 20 sb.AppendLine(); 21 } 22 return sb.ToString(); 23 }View Code
避坑
1、插入NULL到数据库时,使用“NULL”:sb.Append(row[colum].Equals(DBNull.Value) ? "NULL" : row[colum].ToString());
2、生成csv时,注意保存得格式,windows保存utf8时会保存成utf8-bom,保存时使用new UTF8Encoding(false):File.WriteAllText(tmpPath, csv, new UTF8Encoding(false));
3、设置MySqlBulkLoader.CharacterSet = "utf8"和数据库连接一致value="server=*********;database=********;uid=root;pwd=*********;charset='utf8';SslMode=****"