Insert emoji using .NET C# MySQL

Question

Im trying to insert emoji to my mysql. but it shows up as -> "??".

This is what I have done up until now.

My ASPX-page

<meta charset="utf-8">

My database table is set to:

 utf8mb4_unicode_ci

My database column is set to:

 utf8mb4_unicode_ci

My MySQL-connectionString:

server=mysql.server.com;uid=testuser;pwd=1234;database=testdb;convert zero datetime=True;charset=utf8mb4;

However, if I insert emoji directly with a sql-statement in phpMyAdmin it works perfectly

INSERT INTO Notification (id, headline, notificationText, sentDate) 
VALUES (null, 'test', '', NOW())

But when I try to insert by code (.NET C#) it shows up as "??".

private void EmojiQueryTester()
{
    string strSql = "INSERT INTO Notification (id, headline, notificationText, sentDate) " + 
                     " VALUES (null, 'test', '', NOW())";
    string strConnectionString = "mysql.server.com;uid=testuser;pwd=1234;database=testdb;" + 
                                 "convert zero datetime=True;charset=utf8mb4";

    using (var mySqlConnection = new MySqlConnection(strConnectionString))
    {
        mySqlConnection.Open();
        var mySqlCommand = new MySqlCommand(strSql, mySqlConnection);
        mySqlCommand.ExecuteNonQuery();
    }
}

id = 27 is inserted by my .NET code and id = 28 is inserted by phpMyAdmin

I have alså tried inserting other chars as followings, but still no luck:

U+1F601    |    \xF0\x9F\x98\x81     | 

SHOW CREATE TABLE Notification

CREATE TABLE `Notification` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `headline` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `notificationText` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `sentDate` date NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

SELECT notificationText, HEX(notificationText) FROM Notification

This is query of my encodings

SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%' 

Print out of the HEX-string

public static string ConvertStringToHex(String input, System.Text.Encoding encoding)
{
    Byte[] stringBytes = encoding.GetBytes(input);
    StringBuilder sbBytes = new StringBuilder(stringBytes.Length * 2);
    foreach (byte b in stringBytes)
    {
        sbBytes.AppendFormat("{0:X2}", b);
    }
    return sbBytes.ToString();
}

protected void Page_Load(object sender, EventArgs e)
{
    string notification = "";

    DBTool dbT = new DBTool();
    dbT.tester(notification);

    Response.Write(ConvertStringToHex(notification, Encoding.UTF8));
}

output in browser: F09F9880

dbo.HEX(notification): 3F3F

dbo.notification: ??

So output in browser is basicly the corrent HEX for the smiley, however in dbo.HEX(notification) it converts into "3F3F". So basicly it writes in ASCII and not in UTF8 as it should do.

Answer 1

Put this in the connection string:

id=my_user;password=my_password;database=some_db123;charset=utf8mb4;

Console.OutputEncoding = System.Text.Encoding.UTF8;

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

And be sure the column in the table is CHARACTER SET utf8mb4.

For further debugging:

HEX('') in utf8mb4 is these 4 bytes: F09F988B
in utf16:  D83DDE0B

Answer 2

My guess is that your .NET application has UTF-16 encoding. The two ?? corresponds to 2 bytes (16 bits).

Try encoding your string in UTF-8:

string sql = "... VALUES (null, 'test', '', NOW())";
byte [] bytes = Encoding.Default.GetBytes(sql);
string encoded = Encoding.UTF8.GetString(bytes);
// ...
var mySqlCommand = new MySqlCommand(encoded, mySqlConnection);

I'm not 100% sure, but all the rest you've showed us seems consistent.