Mysql为ODBC和.NET分别提供了特定的数据库驱动程序,分别为:
(1)MySQL Connector/Net -- for connecting to MySQL from .NET
(2)Connector/ODBC - MySQL ODBC driver
我使用的版本是:MySQL Connector/Net 5.0和MySQL Connector/ODBC 3.51,数据库版本是:MySQL - 5.0.22。
近来在使用中发现,假设混合使用这两种数据库驱动程序储存/读取中文的时候,是会出现异常的。
简单的测试代码如下:
简单测试页面
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>简单测试</title>
</head>
<body>
<form id="form1" runat="server">
<div>
Retrieve data via Odbc..
<asp:GridView ID="gridOdbc" runat="server">
</asp:GridView>
<hr />
Retrieve data via Mysql..
<asp:GridView ID="gridMysql" runat="server">
</asp:GridView>
<hr />
Name:<asp:TextBox ID="txtName" runat="server"></asp:TextBox>
<br />
EnglishName:<asp:TextBox ID="txtEnglishName" runat="server"></asp:TextBox>
<hr />
<asp:Button ID="btnOdbc" runat="server" Text="Input data via Odbc" OnClick="btnOdbc_Click" />
<asp:Button ID="btnMysql" runat="server" Text="input data via Mysql" OnClick="btnMysql_Click" />
</div>
</form>
</body>
</html>
代码简单使用两个GridView,通过不同的数据库驱动程序连接数据库,读取数据。数据分别使用两种方式录入。
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>简单测试</title>
</head>
<body>
<form id="form1" runat="server">
<div>
Retrieve data via Odbc..
<asp:GridView ID="gridOdbc" runat="server">
</asp:GridView>
<hr />
Retrieve data via Mysql..
<asp:GridView ID="gridMysql" runat="server">
</asp:GridView>
<hr />
Name:<asp:TextBox ID="txtName" runat="server"></asp:TextBox>
<br />
EnglishName:<asp:TextBox ID="txtEnglishName" runat="server"></asp:TextBox>
<hr />
<asp:Button ID="btnOdbc" runat="server" Text="Input data via Odbc" OnClick="btnOdbc_Click" />
<asp:Button ID="btnMysql" runat="server" Text="input data via Mysql" OnClick="btnMysql_Click" />
</div>
</form>
</body>
</html>
读取数据
//使用ODBC读取数据
private void OdbcGetData()
{
OdbcConnection conn = new OdbcConnection(ConfigurationManager.ConnectionStrings["odbc"].ConnectionString);
string sql = "select * from coding";
OdbcDataAdapter da = new OdbcDataAdapter(sql, conn);
DataSet ds = new DataSet();
da.Fill(ds, "coding");
gridOdbc.DataSource = ds;
gridOdbc.DataBind();
}
//使用NET读取数据
private void MysqlGetData()
{
MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["Mysql"].ConnectionString);
string sql = "select * from coding";
MySqlDataAdapter da = new MySqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
da.Fill(ds, "coding");
gridMysql.DataSource = ds;
gridMysql.DataBind();
}
录入数据
//使用ODBC读取数据
private void OdbcGetData()
{
OdbcConnection conn = new OdbcConnection(ConfigurationManager.ConnectionStrings["odbc"].ConnectionString);
string sql = "select * from coding";
OdbcDataAdapter da = new OdbcDataAdapter(sql, conn);
DataSet ds = new DataSet();
da.Fill(ds, "coding");
gridOdbc.DataSource = ds;
gridOdbc.DataBind();
}
//使用NET读取数据
private void MysqlGetData()
{
MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["Mysql"].ConnectionString);
string sql = "select * from coding";
MySqlDataAdapter da = new MySqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
da.Fill(ds, "coding");
gridMysql.DataSource = ds;
gridMysql.DataBind();
}
录入数据
//使用ODBC录入数据
protected void btnOdbc_Click(object sender, EventArgs e)
{
OdbcConnection conn = new OdbcConnection(ConfigurationManager.ConnectionStrings["odbc"].ConnectionString);
string sql = string.Format("insert into coding(id,name,englishname) values(null,\'{0}\',\'{1}\')", txtName.Text, txtEnglishName.Text);
OdbcCommand cmd = new OdbcCommand(sql, conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
OdbcGetData();
MysqlGetData();
}
//使用NET录入数据
protected void btnMysql_Click(object sender, EventArgs e)
{
MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["Mysql"].ConnectionString);
string sql = string.Format("insert into coding(id,name,englishname) values(null,\'{0}\',\'{1}\')", txtName.Text, txtEnglishName.Text);
MySqlCommand cmd = new MySqlCommand(sql, conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
OdbcGetData();
MysqlGetData();
}
简单输入一些测试数据后可以发现,英文是正常的,而中文是乱码。
//使用ODBC录入数据
protected void btnOdbc_Click(object sender, EventArgs e)
{
OdbcConnection conn = new OdbcConnection(ConfigurationManager.ConnectionStrings["odbc"].ConnectionString);
string sql = string.Format("insert into coding(id,name,englishname) values(null,\'{0}\',\'{1}\')", txtName.Text, txtEnglishName.Text);
OdbcCommand cmd = new OdbcCommand(sql, conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
OdbcGetData();
MysqlGetData();
}
//使用NET录入数据
protected void btnMysql_Click(object sender, EventArgs e)
{
MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["Mysql"].ConnectionString);
string sql = string.Format("insert into coding(id,name,englishname) values(null,\'{0}\',\'{1}\')", txtName.Text, txtEnglishName.Text);
MySqlCommand cmd = new MySqlCommand(sql, conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
OdbcGetData();
MysqlGetData();
}
解决办法正在探索中,也希望各位能提出宝贵意见。