Search code examples
c#mysqldatabasecyrillic

Unicode issue with C# and Php

I haven't seen something similar here, but if there is sorry for the duplicate post. Here is my setup>> I use an "outdated" Windows 7 machine with Visual Studio 2010 on it. Please bear with me.

The C# 2010 Express has worked so far so good. Recently I've been working with a MySQL database which is hosted on a WAMP (or XAMP or easyPHP) server. I am connecting the C# application to the MySQL database manually. Now, I am having a problem with a software I'm developing for a Bulgarian project (so it has to support Cyrillic characters).

Here is the code>>

private MySql.Data.MySqlClient.MySqlConnection connection = new MySql.Data.MySqlClient.MySqlConnection(); //connection
private MySql.Data.MySqlClient.MySqlDataAdapter data = new MySql.Data.MySqlClient.MySqlDataAdapter(); //adapter
connection.ConnectionString ="server=localhost;"+ "database=xxxxx;"+ "uid=root;"+ "password=;"; //connection string
try
{
    connection.Open(); //opening the connection
    MySql.Data.MySqlClient.MySqlCommand command = connection.CreateCommand(); //creating a query
    command.CommandText = "SELECT * FROM data"; //initial query for listing the table
    data.SelectCommand = command; //executing the query
    DataSet dataset = new DataSet(); //C# procedures, creating a dataset for gridview
    data.Fill(dataset, "sample_data"); //filling the dataset
    dataGridView1.DataSource = dataset; //setting up the dataset towards the gridview
    dataGridView1.DataMember = "sample_data"; //the data that are going to be sent...

}

And thus, I fill the dataGridView1 with the data from the database. And it is all good, the Cyrillic characters that have been filled up from before are looking good.

Now for the problem>>

If I want to send data into the database I write something like:

string comm1 = "INSERT INTO data (хххх, хххх) VALUES ('Abc', 'Абв')";
command.CommandText = comm1;
command.ExecuteNonQuery();

The first (latin) characters turn out to be fine, but the latter are not. In fact the database is filled up with question-mark signs (?).

What I've tried so far:

  1. I'm using PhpMyAdmin for designing the database. If I try to send the desired Cyrillic characters via a PHP script, they end up written like this: Драган. If you read the DB from a browser they turn up to be fine (probably because of the various unicode supports they have got). But, you can't search thru the DB normally...
  2. Changed the type of the column from Varchar to Text. It doesn't work.
  3. I've changed the collation of the table all the way from UTF-8ci (case insensitive) to Windows Cyrillic 1251 general... and to Bin. It doesn't work.
  4. I've tried changing the database engine. Went from MylSAM, to InnoDB... It doesn't work.
  5. I've tried manipulating the text in the string string commm2 = commm1.Replace("Дра", "\u0414 \u0440 \u0430");. It doesn't work.

  6. I've converted the string

    byte[] comm22 = Convert.FromBase64String(commm1); string commm3 = Encoding.UTF8.GetString(comm22);

. It doesn't work.

  1. Excluding the string altogether... I've inputted the desired text directly in the function itself. It doesn't work.
  2. I've tried adding "@", "/"" in front of the string, in front of the characters. It doesn't work.
Solution

  • i had the same problem with Arabic strings in my php application ,i managed to solve it by executing the following query before sending or receiving data after making sure of tables and database collation setting set to utf8 

            mysqli_set_charset('utf8',$this->connection);
        mysqli_query( $this->connection, 'SET NAMES "utf8" COLLATE "utf8_general_ci"' );

    the problem is now solved but utf8 works for arabic , no need to change anything but executing the SET NAMES query before the (insert/select) query

    hope it helps