修改数据表列值，如果它满足一个语句

本文关键字：语句 一个 满足 数据表 如果 修改 | 更新日期: 2023-09-27 18:19:25

我有一个数据表列:用户名，密码和分数。

我在完成测验(QuizForm)后得到分数，然后我应该用用户名和密码登录，如果它比前一个更高，则保存分数。

我这样做了，但我猜这是错误的

static public void SaveScore(string username, string score) 
{
    try 
    {
        DatabaseConnection db = new DatabaseConnection();
        db.Connection.Open();
        SqlCommand cmdWrite = db.GetCommand("insert into Users(Username, Password) values(username, password)");
        SqlCommand cmdRead = db.GetCommand("select * from Users");
        SqlDataReader reader = cmdRead.ExecuteReader();
        if (reader.HasRows) {
            while (reader.Read()) {
                if (username == reader["Username"].ToString() && int.Parse(score) > Convert.ToInt32(reader["Score"])) {
                    cmdWrite.Parameters.AddWithValue(@"Score", score);
                }
            }
        }
        db.Connection.Close();

    }
    catch (SqlException ex) {
        MessageBox.Show(ex.Message);
    }
}

它不是保存分数。有什么好办法吗?

注:GetCommand看起来像这样:

public SqlCommand GetCommand(string CommandText){
        var cmd = new SqlCommand();
        cmd.Connection = Connection;
        cmd.CommandText = CommandText;
        return cmd;
    }

修改数据表列值，如果它满足一个语句

您的cmdWrite不包括Score列:

SqlCommand cmdWrite = db.GetCommand("insert into Users(Username, Password) values(username, password)");

应该是这样的:

SqlCommand cmdWrite = db.GetCommand("insert into Users(Username, Password, Score) values(@username, @password, @score)");

然后:

cmdWrite.Parameters.AddWithValue("@score", score);

问题解决了

在这里:

static public void SaveScore(string username, string score) {
        try {
            DatabaseConnection db = new DatabaseConnection();
            db.Connection.Open();
            var cmd = db.GetCommand("SELECT Score FROM Users WHERE Username = username");
            var oldScore = Convert.ToInt32(cmd.ExecuteScalar());
            var value = Int32.Parse(score);
            if (oldScore < value) {
                cmd = db.GetCommand("UPDATE Users SET Score = @score WHERE Username = @username AND Score < @score");
                cmd.Parameters.AddWithValue("@score", value);
                cmd.Parameters.AddWithValue("@username", username);
                MessageBox.Show("New High Score Was Saved","Congratulations",MessageBoxButtons.OK);
            }
            else {
                MessageBox.Show("High Score Wasn't Reached. Try Again", "Game Over", MessageBoxButtons.OK);
            }
            cmd.ExecuteNonQuery();
            db.Connection.Close();                
        }
        catch (SqlException ex) {
            MessageBox.Show(ex.Message);
        }
    }
相关文章：