Insert and Update mysql values c#

Question

I use this code in my twitch bot to count messages and save them to a database a couple times per minute.

So the current code I have does get the values I want from my database, but I cannot update or insert the values correctly. The insert_cmd does execute, but values in the database does not correspond to the values I try to insert. affectedRows does return the correct answer of rows that should have been affected. Also when I write out the insert_cmd string, it does look right.

     private static void update_messages()
    {
        try
        {
            MySql.Data.MySqlClient.MySqlConnection mysql_connection = new MySql.Data.MySqlClient.MySqlConnection();
            mysql_connection.ConnectionString = mysql_connection_string;
            mysql_connection.Open();

            //build query string
            string select_cmd = "SELECT * FROM taperen.messages where username in (";
            foreach(CountData cd in chat_messages)
            {
                //Console.WriteLine(cd.username);
                select_cmd += "\'" + cd.username + "\',";
            }
            if(select_cmd == "SELECT * FROM taperen.messages where username in (")
            {
                mysql_connection.Close();
                return;
            }
            select_cmd = select_cmd.TrimEnd(select_cmd[select_cmd.Length - 1]);
            select_cmd += ");";
            //Console.WriteLine(select_cmd);


            MySql.Data.MySqlClient.MySqlCommand myCommand = mysql_connection.CreateCommand();
            myCommand.CommandText = select_cmd;
            MySql.Data.MySqlClient.MySqlDataReader reader = myCommand.ExecuteReader();


            string insert_cmd = "";
            while (reader.Read())
            {
                string username = reader["username"].ToString();
                int index = chat_messages.FindIndex(x => x.username.Equals(username));

                int current_online_count = chat_messages[index].online_count;
                int current_offline_count = chat_messages[index].offline_count;

                int db_online_count = (int)reader["online_count"];
                int db_offline_count = (int)reader["offline_count"];

                int new_online_count = current_online_count + db_online_count;
                int new_offline_count = current_offline_count + db_offline_count;
                insert_cmd += $"UPDATE `taperen`.`messages` SET `online_count`='{new_online_count}', `online_count`='{new_offline_count}' WHERE `id`='{reader["id"]}';";

                chat_messages.RemoveAt(index);
                //Console.WriteLine(username);
            }
            reader.Close();
            mysql_connection.Close();
            foreach(CountData cd in chat_messages)
            {
                insert_cmd += $"INSERT INTO `taperen`.`messages` (`username`, `online_count`, `offline_count`) VALUES ('{cd.username}', '{cd.online_count}', '{cd.offline_count}');";
            }

            mysql_connection.Open();


            //Console.WriteLine(insert_cmd);

            myCommand.CommandText = insert_cmd;
            int affectedRows = myCommand.ExecuteNonQuery();
            Console.WriteLine(affectedRows);
            myCommand.Dispose();

            mysql_connection.Close();
        }
        catch (MySql.Data.MySqlClient.MySqlException ex)
        {
            Console.WriteLine(ex.Message);
        }
    }

The CountData class looks like this:

public class CountData
{
    public string username { get; set; }
    public int online_count { get; set; }
    public int offline_count { get; set; }
}

The database looks like this:

Also if I do something else stupid in my code I appreciate if you could come with some tips :)

Answer 1

You need to pick out the query generated by your code. And then directly run it into Mysql and then compare what it is returning. It looks mysql return effected rows of last executing query. As you are combining Update first and then Insert, hence the effected rows which are getting for Insert. But you can confirm it by directly running your query. Be sure to comment out the code like this :

// int affectedRows = myCommand.ExecuteNonQuery();

Answer 2

In this line you are setting online_count twice, the second instance should (presumably) be offline_count.

insert_cmd += $"UPDATE taperen.messages SET online_count='{new_online_count}', online_count='{new_offline_count}' WHERE id='{reader["id"]}';";