using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient; //Add reference to MySQL 5.0.5
  

namespace UTF8Bug
{
    public sealed class UTF8Test
    {
        /// <summary>
        /// Update Connexion string as needed
        /// </summary>
        private const string _CnxStr = "server=;user id=root;password=;database=UTF8Test;pooling=true;charset=utf8";


        /// <summary>
        /// Insert data in UTF8Test database then Read inserted data and compare with data to insert
        /// </summary>
        /// <returns></returns>
        public static string InsertData()
        {
            string szRet = "Insert Failed";
            MySqlConnection myCnx = null;

            try
            {
                string szTestValue = "test:éàçùêûôöïèë";
                string sp_Name = "sp_MaTable_Add";
                string sp_MonParam = "?_MonParam";

                myCnx = new MySqlConnection(_CnxStr);
                MySqlCommand myCommand = new MySqlCommand(sp_Name, myCnx);
                myCommand.CommandType = System.Data.CommandType.StoredProcedure;

                myCommand.Parameters.Add(new MySqlParameter(sp_MonParam, MySqlDbType.LongBlob));

                //This Failed (Inserted data are truncated !)
                myCommand.Parameters[sp_MonParam].Value = szTestValue;
                //This Success (uncomment to test)
                //myCommand.Parameters[sp_MonParam].Value = System.Text.Encoding.UTF8.GetBytes(szTestValue);

                myCommand.Connection.Open();
                object myRes = myCommand.ExecuteScalar();
                if (null != myRes)
                {
                    int iNewID = Convert.ToInt32(myRes);
                    string szSelect = "select * from MaTable where IDRef=" + iNewID;
                    myCommand = new MySql.Data.MySqlClient.MySqlCommand(szSelect, myCnx);
                    DataSet myDS = null;
                    using (MySql.Data.MySqlClient.MySqlDataAdapter sqlDA = new MySql.Data.MySqlClient.MySqlDataAdapter(myCommand))
                    {
                        myDS = new DataSet();
                        sqlDA.Fill(myDS);
                    }
                    if (null != myDS)
                        if (myDS.Tables.Count > 0)
                            if (myDS.Tables[0].Rows.Count > 0)
                            {
                                string szReadValue = System.Text.Encoding.UTF8.GetString((byte[])myDS.Tables[0].Rows[0]["MonChamp"]);
                                if (0 == string.Compare(szTestValue, szReadValue, false))
                                    szRet = "Insert OK";
                            }
                }
            }
            catch (Exception exx)
            {
                szRet += "  Err=" + exx.Message;
            }
            finally
            {
                if (null != myCnx) myCnx.Close();  
            }
            return szRet;
        }
    }
}