ljl_xyf 发表于 2013-2-7 01:03:44

c#连接mysql中文乱码解决方案(MySql.Data.dll)

今天用C#类连接mysql数据库出现中文乱码具体解决方案如下:
 
用到的mysql表结构:

CREATE TABLE IF NOT EXISTS `tet` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `url` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

用到的mysql表数据:

INSERT INTO `tet` (`id`, `name`, `url`) VALUES
(1, '百度', 'http://www.baidu.com'),
(0, 'google', 'http://www.google.com.hk'),
(3, '400电话', 'http://www.my400800.cn');
 
 
一、查询结果没有问题,在查询条件中如果输入中文查询不到内容,用insert测试为,插入的内容是乱码
    
 
      首先在MySQL Command Line Client里面输入“SHOW VARIABLES LIKE 'character_set_%';”,可看到如下字符:
 
Variable_nameValuecharacter_set_clientutf8character_set_connectionutf8character_set_databaseutf8character_set_filesystembinarycharacter_set_resultsutf8character_set_serverlatin1character_set_systemutf8character_sets_dirC:\Program Files\VertrigoServ\Mysql\share\charsets\ 
可以用如下连接字符串解决中文乱码问题
User Id=test;Host=localhost;Database=eczhou;password=test;charset='gb2312'
 
二、检索结果和插入内容都是乱码
用MySQL Command Line Client里面输入“SHOW VARIABLES LIKE 'character_set_%';”,可看到如下字符:
 
Variable_nameValuecharacter_set_clientutf8character_set_connectionutf8character_set_databaseutf8character_set_filesystembinarycharacter_set_resultsutf8character_set_serverutf8character_set_systemutf8character_sets_dir/usr/share/mysql/charsets/ 
即便是连接字符串改成
User Id=test;Host=localhost;Database=eczhou;password=test;charset=utf8 检索的结果还是乱码
郁闷中。。。。。。。
 
然后用  show variables like 'collation%';显示结果是:
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
 
show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
 
经过不懈努力的查找,终于找到问题了,问题就出在上面红色字体的编码上,我的解决方法如下:
连接字符串修改如下:
server=localhost;uid=root;pwd=root;database=test;Charset=latin1
 
c#代码如下:
 /// <summary>
        /// 执行查询语句,返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string SQLString)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                DataSet ds = new DataSet();
            
                try
                {
                    connection.Open();
                    MySqlCommand M = new MySqlCommand("set names 'latin1'", connection);
                    M.ExecuteNonQuery();
                    //

                    MySqlDataAdapter da = new MySqlDataAdapter(SQLString, connection);
                    da.Fill(ds);
                }
                catch (MySqlException ex)
                {
                    connection.Close();
                    throw new Exception(ex.Message);
                }
                finally
                {
                    connection.Close();
                }
                DataTable dt = ds.Tables;
                foreach (DataRow dr in dt.Rows)
                {
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        dr = Encoding.UTF8.GetString(Encoding.GetEncoding("latin1").GetBytes(dr.ToString()));
                    }


                }

                return ds;
            }
        }
 
一定要注意上面的红色字体。
 
 
 
 
 
页: [1]
查看完整版本: c#连接mysql中文乱码解决方案(MySql.Data.dll)