MySqlBulkLoader使用操作配置

MySqlBulkLoader使用操作配置

1:在my.ini配置文件中增加一行secure_file_priv=，然后重启mysql服务 参考：https://www.cnblogs.com/KHZ521/archive/2004/01/13/14066258.html

linux版本是my.cnf，一般会放在/etc/my.cnf，/etc/mysql/my.cnf
windows下的是my.ini，一般会在安装目录的根目录 如：C:\Program Files (x86)\mysql-8.0.18-winx64\my.ini

 

2.设置支持文件导入，参考文章（未设置会一直提示The used command is not allowed with this MySQL version）
https://www.cnblogs.com/xiaohuomiao/p/10646266.html

3.代码里面：MySqlBulkLoader.Local=true 不然会提示文件找不到,即使文件已存在(File xxxx not found(OS errno 2 - No such file or directory))

4.数据库链接语句增加;AllowLoadLocalInfile=true(未设置会一直提示The used command is not allowed with this MySQL version)

 

上代码：

 1 public int BulkExecuteNonQuery(string tableName, DataTable table)
 2         {
 3             var tempDir = Path.Combine(Directory.GetCurrentDirectory(), "wwwroot", "temp");
 4             if (!Directory.Exists(tempDir))
 5             {
 6                 Directory.CreateDirectory(tempDir);
 7             }
 8             var tmpPath = Path.Combine(tempDir, ASKJ.Utility.UtilityHelper.GetNewGuid() + ".csv");
 9             if (!File.Exists(tmpPath))
10             {
11                 File.Create(tmpPath).Dispose();
12             }
13             LoggerManager.Debug(GetType().Name, $"tmpPath:{tmpPath}");
14             try
15             {
16 
17                 var csv = DbManager.DataTableToCsv(table);
18                 StreamWriter sw = new StreamWriter(tmpPath, false, UTF8Encoding.UTF8);  //要与mysql的编码方式对象, 数据库要utf8, 表也一样
19                 sw.Write(csv);
20                 sw.Close();
21                 using (var conn = DbFactory.CreateConnection(_connectionString, ProviderType) as MySqlConnection)
22                 {
23                     MySqlBulkLoader bulk = new MySqlBulkLoader(conn)
24                     {
25                         FieldTerminator = ",",
26                         FieldQuotationCharacter = '"',
27                         EscapeCharacter = '"',
28                         LineTerminator = "\r\n",
29                         FileName = tmpPath,
30                         Local = true,
31                         NumberOfLinesToSkip = 0,
32                         TableName = tableName,
33                     };
34                     //bulk.Columns.AddRange(table.Columns.Cast<DataColumn>().Select(colum => colum.ColumnName).ToArray()); 
35                     var insertCount = bulk.Load();
36                     return insertCount;
37                 }
38             }
39             catch (Exception ex)
40             {
41                 LoggerManager.Error(GetType().Name, ex.Message, ex);
42                 return -1;
43             }
44             finally
45             {
46                 try
47                 {
48                     File.Delete(tmpPath);
49                 }
50                 catch
51                 {
52 
53                 }
54             }
55 
56         }
 1 /// <summary>
 2         ///将DataTable转换为标准的CSV
 3         /// </summary>
 4         /// <param name="table">数据表</param>
 5         /// <returns>返回标准的CSV</returns>
 6         public static string DataTableToCsv(DataTable table)
 7         {
 8             //以半角逗号（即,）作分隔符，列为空也要表达其存在。
 9             //列内容如存在半角逗号（即,）则用半角引号（即""）将该字段值包含起来。
10             //列内容如存在半角引号（即"）则应替换成半角双引号（""）转义，并用半角引号（即""）将该字段值包含起来。
11             StringBuilder sb = new StringBuilder();
12             DataColumn colum;
13             foreach (DataRow row in table.Rows)
14             {
15                 for (int i = 0; i < table.Columns.Count; i++)
16                 {
17                     colum = table.Columns[i];
18                     if (i != 0) sb.Append(",");
19                     if (colum.DataType == typeof(string) && row[colum].ToString().Contains(","))
20                     {
21                         sb.Append("\"" + row[colum].ToString().Replace("\"", "\"\"") + "\"");
22                     }
23                     else sb.Append(row[colum].ToString());
24                 }
25                 sb.AppendLine();
26             }
27             return sb.ToString();
28         }

 

posted @ 2021-01-11 16:39  AntColony  阅读(1143)  评论(0)    收藏  举报