C# - ExecuteNonQuery does not return the right affectedRows on an INSERT with an ON DUPLICATE clause

Question

Using HeidiSQL, I update a table with multiple duplicates using this query:

INSERT INTO ach_progress (user_id, app_id, ach_name, unlock_time) 
VALUES  (123456, 123456, '3', '2024-04-23 13:59:31'), 
        (234567, 234567, '4', '2024-04-23 15:05:36') 
ON DUPLICATE KEY UPDATE ach_name = ach_name

It returns "0 affected rows".

With the same query in C# (using the MySQL connector 8.0.23 in a .NET Core 3.1 app):

public static int Insert(string s_Query)
{
    int i_Result = 0;
    using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(myConnectionString))
    {
        using (MySqlCommand myCommand = new MySqlCommand(s_Query, conn))
        {
            conn.Open();
            i_Result = myCommand.ExecuteNonQuery();
        }
    }
    return (i_Result);
}

I get i_Result = number of tried inserts.

Unfortunately I need to know if I inserted rows for the rest of my program.

Running the query directly works but the issue seems to stem from ExecuteNonQuery.

I've tried searching answers for the same issue but probably could not phrase it well as it did not return any result.

Answer 1

When you create the connection to mysql, set UseAffectedRows true in the connection string.

As per the manual, UseAffectedRows

When true, the connection reports changed rows instead of found rows.

The default value is false for this setting.