C# - SQL Database - Sending query to update records however too many update requests cause some to get missed

Question

I've searched high and lo, so apologies if I have missed something.

When I run the source, no errors arise and it looks as if everything is working as it should, however when I inspect the database, only some records have been updated/added and others have been missed.

The rate of updates currently varies between 1 per second to an upwards of 25 per second (some headroom/just incase, but typically around 15).

In this section, I query the database to pull existing values, make my adjustments on those values, then save it back to the database.

Below is the snippet that updates the SQL database, there are about 43 columns being updated (some may remain the same value, but they are being re-added).

Is there any way I can ensure all update requests pass through and succeed in updating?

try
{
    MySqlCommand cmd2 = connection.CreateCommand();
    connection.Open();
    cmd2.CommandType = CommandType.Text;
    cmd2.CommandText = "UPDATE user_information SET exampleValue = @exampleValue WHERE username = @username";
    cmd2.Parameters.AddWithValue("@username", username);
    cmd2.Parameters.AddWithValue("@exampleValue", exampleValue);

    cmd2.ExecuteNonQuery();
    connection.Close();
}
catch (Exception ex)
{
    throw;
}
finally
{
    if (connection.State == ConnectionState.Open)
    {
        connection.Close();
        //this.LoadData();
    }
}

Answer 1

For better comparison, use LTRIM & RTRIM i-e LTRIM(RTRIM(username)) in SQL and in C# use String.Trim() i-e usename.Trim()

LTRIM(RTRIM(username)) will remove extra spaces of username in database eg. ' Smith ' -> 'Smith' and username.Trim() remove spaces of your input

So your replace the lines

cmd2.CommandText = "UPDATE user_information SET exampleValue = @exampleValue WHERE username = @username";
cmd2.Parameters.AddWithValue("@username", username);

with

cmd2.CommandText = "UPDATE user_information SET exampleValue = @exampleValue WHERE LTRIM(RTRIM(username)) = @username";
cmd2.Parameters.AddWithValue("@username", username.Trim());

To avoid case sensitive comparison, use LOWER i-e LOWER(username) in SQL and in C# use String.ToLower(), so username.ToLower()

Now, code will be like that

cmd2.CommandText = "UPDATE user_information SET exampleValue = @exampleValue WHERE LOWER(LTRIM(RTRIM(username))) = @username";
cmd2.Parameters.AddWithValue("@username", username.Trim().ToLower());

Answer 2

Base on your snippet, there's no wrong thing the way you update the data. But one thing i observed is the way you pass the parameter. In that case you did not specify the datatype of your parameter. so i assume that the '@username' and '@exampleValue' is string.

In some ways , if the datatype of your column in your table did not match to the datatype of your parameter. it may cause to fail the update of the data.

It is better if you can provide us the structure of your table to evaluate further your problem.

Regards.