MySqlBulkLoader批实现过程:将需要的数据集保存到CSV文件,然后再从文件里面读取。
CsvHelp
1 /// <summary> 2 /// 定义csv文件 3 /// </summary> 4 /// <param name="folder">路径</param> 5 /// <param name="fileName">文件名</param> 6 /// <param name="fileExtension">后缀名</param> 7 /// <returns></returns> 8 public static string EricSunExportData<T>(List<T> Tlist) where T : new() 9 { 10 Type type = typeof(T); 11 //创建csv文件 12 string filePath = CreateFile(AppDomain.CurrentDomain.BaseDirectory, type.Name, "csv"); 13 //向csv文件添加数据 14 return SaveDataToCSVFile<T>(Tlist, filePath) ? filePath : string.Empty; 15 } 16 /// <summary> 17 /// 创建csv文件 18 /// </summary> 19 /// <param name="folder">文件夹</param> 20 /// <param name="fileName">文件名称</param> 21 /// <param name="fileExtension">文件后缀</param> 22 /// <returns>file path</returns> 23 public static string CreateFile(string folder, string fileName, string fileExtension) 24 { 25 FileStream fs = null; 26 string filePath = folder + fileName + "." + fileExtension; 27 try 28 { 29 if (!Directory.Exists(folder)) 30 { 31 Directory.CreateDirectory(folder); 32 } 33 fs = File.Create(filePath); 34 } 35 catch (Exception ex) 36 { 37 } 38 finally 39 { 40 if (fs != null) 41 { 42 fs.Dispose(); 43 } 44 } 45 return filePath; 46 } 47 /// <summary> 48 ///向csv文件添加数据 49 /// </summary> 50 /// <param name="ItList">数据</param> 51 /// <param name="filePath">CSV文件路径</param> 52 /// <returns>success flag</returns> 53 public static bool SaveDataToCSVFile<T>(List<T> ItList, string filePath) where T : new() 54 { 55 bool successFlag = true; 56 StringBuilder strColumn = new StringBuilder(); 57 StringBuilder strValue = new StringBuilder(); 58 StreamWriter sw = null; 59 try 60 { 61 sw = new StreamWriter(filePath); 62 string ItListValue = JsonConvert.SerializeObject(ItList); 63 System.Data.DataTable dt = JsonConvert.DeserializeObject<System.Data.DataTable>(ItListValue); 64 strValue.Append(DataTableToCsv(dt)); //将DataTable转换为标准的CSV 65 string strV = strValue.ToString(); 66 sw.WriteLine(strV.Substring(0, strV.Length - 1)); //write the row value 67 } 68 catch (Exception ex) 69 { 70 successFlag = false; 71 System.IO.File.Delete(filePath); 72 } 73 finally 74 { 75 if (sw != null) 76 { 77 sw.Dispose(); 78 } 79 } 80 return successFlag; 81 } 82 /// <summary> 83 ///将DataTable转换为标准的CSV 84 /// </summary> 85 /// <param name="table">数据table</param> 86 /// <returns>返回标准的CSV</returns> 87 private static string DataTableToCsv(System.Data.DataTable table) 88 { 89 //以半角逗号(即,)作分隔符,列为空也要表达其存在。 90 //列内容如存在半角逗号(即,)则用半角引号(即"")将该字段值包含起来。 91 //列内容如存在半角引号(即")则应替换成半角双引号("")转义,并用半角引号(即"")将该字段值包含起来。 92 StringBuilder sb = new StringBuilder(); 93 System.Data.DataColumn colum; 94 foreach (System.Data.DataRow row in table.Rows) 95 { 96 for (int i = 0; i < table.Columns.Count; i++) 97 { 98 colum = table.Columns[i]; 99 if (i != 0) 100 { 101 sb.Append("▓"); //设置特定的分隔符 102 } 103 if (row[colum].ToString().Contains(",") && row[colum].ToString().Contains("\"")) 104 { 105 sb.Append("\"" + row[colum].ToString().Replace("\"", "\"\"") + "\""); 106 } 107 else if (row[colum].ToString().Contains("\"") || row[colum].ToString().Contains(",")) 108 { 109 sb.Append("\"" + row[colum].ToString().Replace("\"", "\"\"") + "\""); 110 } 111 else 112 { 113 sb.Append(row[colum].ToString()); 114 } 115 } 116 sb.AppendLine(); 117 } 118 return sb.ToString(); 119 }
DbHelperMySQL:
1 /// <summary> 2 /// MySqlBulkLoader批量插入 3 /// </summary> 4 /// <typeparam name="T"></typeparam> 5 /// <param name="table"></param> 6 /// <param name="path"></param> 7 /// <param name="dbkey"></param> 8 /// <returns></returns> 9 public static bool MysqlBulkLoad<T>(string path) 10 { 11 try 12 { 13 Type type = typeof(T); 14 // 反射字体的所有属性 15 PropertyInfo[] ProList = type.GetProperties(); 16 List<string> columns = new List<string>(); 17 foreach (PropertyInfo Info in ProList) 18 { 19 columns.Add(Info.Name); 20 } 21 using (MySqlConnection conn =“MySQL数据库连接地址”)) 22 { 23 conn.Open(); 24 //string[] columns = ProList.Cast<DataColumn>().Select(x => x.ColumnName).ToArray(); //table.Columns.Cast<DataColumn>().Select(c => c.ColumnName).ToArray(); 25 //Stopwatch stopwatch = new Stopwatch(); 26 //stopwatch.Start(); 27 MySqlBulkLoader bulk = new MySqlBulkLoader(conn) 28 { 29 FieldTerminator = "▓", //获取或设置字段结束符。 30 FieldQuotationCharacter = '"', //获取或设置字段引号字符。 31 EscapeCharacter = '"', //获取或设置转义字符。 32 LineTerminator = "\r\n", //获取或设置行结束符。 33 FileName = path, 34 Local = true, //获取或设置一个值,该值指示要加载的文件名是否为 35 NumberOfLinesToSkip = 0, //获取或设置要跳过的行数。 36 TableName = type.Name, //获取或设置表的名称 37 CharacterSet = "utf8mb4" //获取或设置字符集 38 }; 39 bulk.Columns.AddRange(columns.ToArray()); 40 var bulkCount = bulk.Load(); 41 //stopwatch.Start(); 42 if (bulkCount > 0) 43 return true; 44 else return false; 45 } 46 } 47 catch (Exception ex) 48 { 49 //报错The used command is not allowed with this MySQL version 50 // 出现这种现象的原因,是由于 51 //服务器端 local_infile默认为开启状态,客户端 local_infile默认为关闭状态 52 //MySQL8.0 默认不允许加载本地文件数据 53 //变量 infile属性 mysql >set global local_infile = 'ON' 打开权限; 54 //再次查看 infile属性 mysql > show global varaiables like ‘local_infile’; 55 throw new Exception(ex.Message); 56 } 57 finally 58 { 59 System.IO.File.Delete(path); 60 } 61 }
MySqlBulkLoader使用过程中出现的问题
1.Mysql数据库不支持加载本地文件数据;
2.数据库和导入文件分别放在不同服务器上的。
1. 为了数据库的安全,Mysql本地导入文件的配置没有开启,所以使用MySQLBulkLoader批量导入数据库,就需要mysql数据库支持本地导入文件。否则会出现以下情况
解决办法:
2. 数据库和项目是分别放在不同服务器上,会造成以下问题:
解决办法:
标签:string,批量,filePath,colum,插入,ToString,MySqlBulkLoader,new,row From: https://www.cnblogs.com/shaopang/p/17642594.html