C# Mysql UTF8 Encoding

Question

I have a mysql database with utf8_general_ci encoding ,

i'm connecting to the same database with php using utf-8 page and file encode and no problem but when connection mysql with C# i have letters like this غزة

i editit the connection string to be like this

server=localhost;password=root;User Id=root;Persist Security Info=True;database=mydatabase;Character Set=utf8

but the same problem .

Solution 1

Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword; CharSet=utf8;

Note! Use lower case value utf8 and not upper case UTF8 as this will fail.

See http://www.connectionstrings.com/mysql

Solution 2

could you try:

Server=localhost;Port=3306;Database=xxx;Uid=x xx;Pwd=xxxx;charset=utf8;"

Edit: I got a new idea:

//To encode a string to UTF8 encoding
string source = "hello world";
byte [] UTF8encodes = UTF8Encoding.UTF8.GetBytes(source);

//get the string from UTF8 encoding
string plainText = UTF8Encoding.UTF8.GetString(UTF8encodes);

good luck

more info about this technique http://social.msdn.microsoft.com/forums/en-us/csharpgeneral/thread/BF68DDD8-3D95-4478-B84A-6570A2E20AE5

Solution 3

You might need to use the "utf8mb4" character set for the column in order to support 4 byte characters like this: "λ𝛌 "

The utf8 charset only supports 1-3 bytes per character and thus can't support all unicode characters.

See http://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8mb4.html for more details.

Solution 4

CHARSET should be uppercase

Server=localhost;Port=3306;Database=xxx;Uid=x xx;Pwd=xxxx;CHARSET=utf8;

Solution 5

Just in case some come here later.

I needed to create a Seed method using Mysql with EF6, to load a SQL file. After running it I got weird characters on database like ? replacing é, ó, á

SOLUTION: Make sure I read the file using the right charset: UTF8 on my case.

     var path = System.AppDomain.CurrentDomain.BaseDirectory;
     var sql = System.IO.File.ReadAllText(path + "../../Migrations/SeedData/scripts/sectores.sql", Encoding.UTF8);

And then M.Shakeri reminder:

CHARSET=utf8 on cxn string in web.config. Using CHARSET as uppercase and utf8 lowercase.

Hope it helps.

R.

View more solutions

Comments

[T4mer]

this was the connection string generated by the wizard

[x06265616e]

Ahh well, check out the link i provided, it has all the MySQL Connection Strings

[T4mer]

i checked it , when using small utf data are returned in the correct encoding , when it's UTF no data returned

[T4mer]

that was a bit helpful it's latin1_general_ci how this would be useful ?

[Robert H]

If you set your collation to latin1_general_ci in mySQL you should be able to pull the correct data from your database in C# and PHP - worth a shot anyways

[Bryan Legend]

The GetBytes->GetString didn't do anything for me with this problem.

[Korenron]

I search for a solution for 2 days! simple is the best , Thanks!

[Rick James]

غزة is Mojibake for غزة, which can be represented in either utf8 or utf8mb4. Still, it is generally better to shoot for utf8mb4.