插入和更新mysql值c#

Question

我在twitch中使用这段代码来计数消息，并每分钟将它们保存到数据库中几次。

因此，我的当前代码确实从我的数据库中获得了我想要的值，但是我不能正确地更新或插入这些值。insert_cmd确实执行，但是数据库中的值与我试图插入的值不对应。affectedRows确实返回了应该受到影响的行的正确答案。另外，当我写出insert_cmd字符串时，它看起来确实是正确的。

     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);
        }
    }

CountData类如下所示：

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

数据库如下所示：

​

此外，如果我在代码中做了其他愚蠢的事情，我很感激您能提供一些提示:)

Answer 1

在这一行中，您要设置两次online_count，第二个实例(大概)应该是offline_count。

insert_cmd += $"UPDATE taperen.messages SET online_count='{new_online_count}'，online_count='{new_offline_count}‘，其中id=’{reader“}}‘；

Answer 2

您需要选择由代码生成的查询。然后直接将它运行到Mysql中，然后比较它返回的内容。它看起来mysql返回上次执行查询的受影响行。当您先将Update和Insert组合在一起时，就会得到要插入的受影响的行。但是您可以通过直接运行查询来确认它。一定要像这样注释掉代码：

// int affectedRows = myCommand.ExecuteNonQuery()；