Convert Or Cast MySQL Database Column Collation In SQL Code And C#

Software Used: Mysql 5.0 connector/Visual Studio 2010
We encounter a tricky problem when showing mysql database column value with collation of typelatin1_swedish_ci. The value retrieved from the column and shown on a c# string variable is not the same with what is in the database. The ascii characters has been stripped off in the string variable.

Here is a string variable with special character apostrophe stored in a column with latin1swedish collation.

Ex. Take on bank robbers, beat the bad guys or shoot it out with the police in the quest for freedom – all in the name of fun! Doesn’t sound like your average day out, but today we’re making this happen!

string sql = "select convert(description using utf8) 
as description from 
your_table where id='554be79124998120b8c3505d47c88c2d'";

Sample C# Implementation

private void TestDeals()  
{  
    DataTable dt = new DataTable();  
    string conn_s = ConfigurationManager.AppSettings["LocalDB"];  
    string sql = "select convert(description using utf8) as description   
        from your_table where id='554be79124998120b8c3505d47c88c2d'";  
    MySqlConnection conn = null;        
    ASCIIEncoding ascii = new ASCIIEncoding();  
    try  
    {  
        conn = new MySqlConnection(conn_s);  
        conn.Open();  
        MySqlCommand cmd = new MySqlCommand(sql, conn);  
        MySqlDataAdapter da = new MySqlDataAdapter(cmd);  
        da.Fill(dt);  
        //IDataReader read = cmd.ExecuteReader();  
        //dt.Load(read, LoadOption.PreserveChanges);          
        string desc = dt.Rows[0]["description"].ToString();  
        MessageBox.Show(desc);         
    }  
    catch (Exception ex)  
    {  
        throw;  
    }  
    finally  
    {  
       conn.Close();  
    }  
}