C # connect to mysql Chinese garbled solution (MySql. Data. dll)

Today, the C # class is used to connect to the mysql database. The specific solution is as follows:

 

Mysql table structure used:

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 table data used:

Insert into 'tet' ('id', 'name', 'url') VALUES
(1, 'Baidu ', 'HTTP: // www.baidu.com '),
(0, 'Google ', 'HTTP: // www.google.com.hk '),
(3, 'phone 400 ', 'HTTP: // www.my400800.cn ');

 

 

I. the query results are correct. If no content can be found in the query conditions when Chinese characters are entered, the insert test is used. The inserted content is garbled.

 

First, enter "show variables like 'character _ set _ % ';" in the MySQL Command Line Client. The following characters are displayed:

 

Variable_name	Value
Character_set_client	Utf8
Character_set_connection	Utf8
Character_set_database	Utf8
Character_set_filesystem	Binary
Character_set_results	Utf8
Character_set_server	Latin1
Character_set_system	Utf8
Character_sets_dir	C: \ Program Files \ VertrigoServ \ Mysql \ share \ charsets \

 

You can use the following connection string to solve Chinese garbled characters.

User Id = test; Host = localhost; Database = eczhou; password = test; charset = 'gb2312'

 

2. garbled search results and inserted content

Enter "show variables like 'character _ set _ % ';" in the MySQL Command Line Client to see the following characters:

 

Variable_name	Value
Character_set_client	Utf8
Character_set_connection	Utf8
Character_set_database	Utf8
Character_set_filesystem	Binary
Character_set_results	Utf8
Character_set_server	Utf8
Character_set_system	Utf8
Character_sets_dir	/Usr/share/mysql/charsets/

 

Even if the connection string is changed

User Id = test; Host = localhost; Database = eczhou; password = test; charset = utf8

Depressed .......

 

Then use show variables like 'collation % '; the result is:

+ ---------------------- + ----------------- +
| 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/|
+ -------------------------- + ---------------------------- +

 

After unremitting efforts, I finally found the problem. The problem lies in the red font code above. My solution is as follows:

Modify the connection string as follows:

Server = localhost; uid = root; pwd = root; database = test;Charset = latin1

 

C # The Code is as follows:

/// <Summary>
/// Execute the query statement and return DataSet
/// </Summary>
/// <Param name = "SQLString"> query statement </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 [0];
Foreach (DataRow dr in dt. Rows)
{
For (int I = 0; I <dt. Columns. Count; I ++)
{
Dr [I] = Encoding. UTF8.GetString (Encoding. GetEncoding ("latin1"). GetBytes (dr [I]. ToString ()));

}

}

Return ds;
}
}

 

Pay attention to the red font above.