最近我在做课设,用的c#,其中要求用c#把csv导入mysql
我参照了这个如下网页的方法,写了如下代码:https://jingyan.baidu.com/article/25648fc19faa829191fd0099.html
string constr = "server=localhost;port=3306;user=root;password=root;database=select_course";
MySqlConnection mycon = new MySqlConnection(constr);
MySqlBulkLoader bulkLoader = new MySqlBulkLoader(mycon);
if (mycon != null && mycon.State != ConnectionState.Open)
mycon.Open();
bulkLoader.TableName = "student"; //插入的表的名字
//文件的完整路径;先要把数据写入到csv中;Environment.CurrentDirectory获取当前项目的路径,小编把CSV文件放那的
bulkLoader.FileName = "D:\\D ( Date File 2)\\VS2019\\web experiment 1" + "\\" + "2.csv";
bulkLoader.FieldTerminator = ",";//这个地方字段间的间隔方式,为逗号
int count = bulkLoader.Load();//这个地方是执行
mycon.Close();//最后别忘记关闭数据库
结果报错了:
(详细报错信息,可见最下面的附录1)
然后我检查了变量值
(标蓝的$exception的值详细信息,可见最下面的附录2)
$exception要求把AllowLoadLocalInfile设置成true
之后我在如下的网站中查到了如下的信息:
https://www.cnblogs.com/Thancoo/p/mysql8loaddatadisable.html
于是我把用于登陆mysql的string,后面增加了”AllowLoadLocalInfile=true“
即:
string constr = “server=localhost;port=3306;user=root;password=root;database=select_course;AllowLoadLocalInfile=true”;
然后一遍就跑通了
附录:
附录1:第1张报错图中的详细
System.NotSupportedException
HResult=0x80131515
Message=To use MySqlBulkLoader.Local=true, set AllowLoadLocalInfile=true in the connection string. See https://fl.vu/mysql-load-data
Source=MySqlConnector
StackTrace:
at MySql.Data.MySqlClient.MySqlBulkLoader.d__80.MoveNext() in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlBulkLoader.cs:line 194
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
at MySql.Data.MySqlClient.MySqlBulkLoader.Load() in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlBulkLoader.cs:line 140
at web_experiment_1.Program.ImportStudent() in D:\D ( Date File 2)\VS2019\web experiment 1\web experiment 1\Program.cs:line 195
at web_experiment_1.Program.Main(String[] args) in D:\D ( Date File 2)\VS2019\web experiment 1\web experiment 1\Program.cs:line 56
此异常最初是在此调用堆栈中引发的:
[外部代码]
web_experiment_1.Program.ImportStudent() (位于 Program.cs 中)
web_experiment_1.Program.Main(string[]) (位于 Program.cs 中)
附录2:$exception的值
$exception {“To use MySqlBulkLoader.Local=true, set AllowLoadLocalInfile=true in the connection string. See https://fl.vu/mysql-load-data”} System.NotSupportedException