CODEWITHSUNDEEP
c#asp.netmysqlc#-4.0phpmyadmin
Saravanan
Saravanan

Reputation: 11612

how to insert unicode values in mysql 5.1 version using c#?

In my asp.net application, i have some data in unicode format(Hindi-Indian language).Now i want to insert this unicode text into mysql(5.1) database.When i run the below C# code, the text stored in my db as like ?????? instead of my unicode text.But when i execute the query through phpmyadmin it was stored correctly.

I already set the db,table and particular field collation as utf8_general_ci. and also set the db character set to utf8.

string mysql_query2 = "SET NAMES 'utf8'";
da.UpdateCommand = new MySqlCommand(mysql_query2, mysqlCon);
da.UpdateCommand.ExecuteNonQuery();


string para_text="हिन्दी भारत";
string para_outerxml="की राष्ट्रभाषा";
string status="जयपुर। हास्य-व्यंग्य";

string sql = "Insert into sand_box (para_text,para_outerxml,status) values('" + para_text + "','" + para_outerxml + "','" + status+")";
da.InsertCommand = new MySqlCommand(sql, mysqlCon);
da.InsertCommand.ExecuteNonQuery();

Also, Please refer my image below.(Table Structure)

enter image description here

Upvotes: 2

Views: 2262

Answers (2)

शेखर
शेखर

Reputation: 17604

You need it to cast into base-64 and insert it as follow

var langText = Convert.ToBase64String(Encoding.UTF8.GetBytes("yourtext"));

And while fetching back convert it to normal string as follows

var Normal=Encoding.UTF8.GetString(Convert.FromBase64String("your base64text"));

Edit 1

So your insert should be like

string mysql_query2 = "SET NAMES 'utf8'";
da.UpdateCommand = new MySqlCommand(mysql_query2, mysqlCon);
da.UpdateCommand.ExecuteNonQuery();


string para_text="हिन्दी भारत";
string para_outerxml="की राष्ट्रभाषा";
string status="जयपुर। हास्य-व्यंग्य";

string sql = "Insert into sand_box (para_text,para_outerxml,status) 
     values('" + Convert.ToBase64String(Encoding.UTF8.GetBytes(para_text)) + "','" 
             + Convert.ToBase64String(Encoding.UTF8.GetBytes(para_outerxml)) + "','" + 
              Convert.ToBase64String(Encoding.UTF8.GetBytes(status))+")";
da.InsertCommand = new MySqlCommand(sql, mysqlCon);
da.InsertCommand.ExecuteNonQuery();

Edit 2

Here is a similar question
how to insert arabic into mysql from asp.net C#

Upvotes: 2

Freelancer
Freelancer

Reputation: 9074

MySQL 5.1 supports two character sets for storing Unicode data:

ucs2, the UCS-2 encoding of the Unicode character set using 16 bits per character.

utf8, a UTF-8 encoding of the Unicode character set using one to three bytes per character.

Upvotes: 0

Related Questions