MySQL的.NET驱动BUG引起DataTable在load时出异常的临时解决方案
MySQL的.NET驱动BUG引起DataTable在load时出异常的临时解决方案
MySQL的.NET驱动BUG引起DataTable在load时出异常的临时解决方案，完整测试用例如下
drop table if exists student;
CREATE TABLE`student`(
`id`int(11)NOT NULL AUTO_INCREMENT,
`name`varchar(20)NOT NULL,
PRIMARY KEY(`id`),
UNIQUE KEY`IX_student`(`name`)
)ENGINE=InnoDb AUTO_INCREMENT=5DEFAULT CHARSET=utf8;
drop table if exists stufav;
CREATE TABLE`stufav`(
`studentId`int(11)NOT NULL,
`favoriteId`int(11)NOT NULL,
PRIMARY KEY(`studentId`,`favoriteId`)
)ENGINE=InnoDb DEFAULT CHARSET=utf8;
insert into student(id,name)values(1,'张三');
insert into student(id,name)values(2,'李四');
insert into student(id,name)values(3,'王五');
insert into student(id,name)values(4,'赵六');
insert into stufav values(2,1);
insert into stufav values(2,11);
insert into stufav values(2,111);
select*from student s left join stufav sf on sf.studentId=s.id
运行结果为
1;张三;NULL;NULL;
2;李四;2;1;
2;李四;2;11;
2;李四;2;111;
3;王五;NULL;NULL;
4;赵六;NULL;NULL;
里面的name是唯一键，查询结果有多个李四。
C#的测试代码如下
using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;
using System.IO;
using System.Data;
using System.Diagnostics;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;
namespace ConsoleApplication1
{
class Program
{
private static string connectionString="server=localhost;database=MySql;user ;
private static void TestDataTableLoadNull()
{
using(MySqlConnection conn=new MySqlConnection(connectionString))
{
try
{
conn.Open();
MySqlCommand cmd=conn.CreateCommand();
cmd.CommandText="select*from student s left join stufav sf on sf.student;
MySqlDataReader reader=cmd.ExecuteReader();
DataTable dt=new DataTable();
dt.Load(reader);
}
catch(Exception ex)
{
throw(ex);
}
finally
{
conn.Close();
}
}
}
static void Main(string[]args)
{
TestMySQL.TestDataTableLoadNullMySql();
}
}
}
异常为：
"未能启用约束。一行或多行中包含违反非空、唯一或外键约束的值。"
临时解决方案有2个
<!--@import url(scrollbar.css);-->
1使用Adapter方式代替DataTable的load方法
MySql Command cmd=conn.CreateCommand();
cmd.CommandText="select*from student s left join stufav sf on sf.student;
MySqlDataAdapter adapter=new MySql DataAdapter();
adapter.SelectCommand=cmd;
DataSet ds=new DataSet();
adapter.Fill(ds);
return ds.Tables[0];
2在查询的sql外面包一个select*
select*from(select*from student s left join stufav sf on sf.student;