c# - 从 MySql 数据库收集数据以与变量进行比较

Question

我基本上正在构建一个多项选择测验，用户将根据他们的表现等获得分数......我将他们的分数保存到我网站上每个单独测试的数据库中。如果他们在多项选择测试的特定通过中获得的分数低于数据库中已分配的值，我不想保存他们的分数。我需要能够提取数据库中当前用户的分数(我的数据库表中默认分数为 0，因此总会有一个值)，将其与他们刚刚收到的分数进行比较，如果更大，则保存将新分数存入数据库。

我无法弄清楚如何提取用户分数并将其分配给一个变量，以便我可以比较两者。我使用用户 ID 作为表中的主键

到目前为止，我有以下内容:

protected void Submit_Click(object sender, EventArgs e)
{
    if (!this.IsValid)
        return;
    int score = 0;
    List<RadioButtonList> list = new List<RadioButtonList>() { RadioButtonList1, RadioButtonList2, RadioButtonList3, RadioButtonList4, RadioButtonList5, RadioButtonList6, RadioButtonList7, RadioButtonList8, RadioButtonList9, RadioButtonList10 };
    foreach (var element in list)
    {
        if (element.SelectedValue == "Correct")
        {
            score++;
        }

    }
    Response.Write("you scored: " + score);
    Button1.Visible = false;

    if (score != 0) ;
    {
        string UserId = User.Identity.GetUserId();

        string MyConString = "SERVER=localhost;" +
        "DATABASE=synther_physics;" +
        "UID=root;" +
        "PASSWORD=rootpass;";
        MySqlConnection connection = new MySqlConnection(MyConString);




        MySqlCommand commandPull = connection.CreateCommand();
        commandPull.CommandText = "SELECT score FROM userscores WHERE Id = UserID";
        int DBscore = 
        connection.Open();
        int RowsAffected = commandPull.ExecuteNonQuery();
        connection.Close();



        if (DBscore < score) ;
        {
            Response.Write("New high score! Well done!");
            MySqlCommand commandReplace = connection.CreateCommand();
            commandReplace.CommandText = "REPLACE INTO userscores (Id, momentsandenergytestscore) VALUES (@Id,@score)";

            commandReplace.Parameters.AddWithValue("@Id", UserId);
            commandReplace.Parameters.AddWithValue("@score", score);

            connection.Open();
            int RowsAffectedReplace = commandReplace.ExecuteNonQuery();
            connection.Close();

        }

        else;
        {
            Response.Write("You didnt beat your previous best of" + DBscore);
        }
    }
}

我实际上尝试提取和比较数据的部分在这里(注意代码不完整):

MySqlCommand commandPull = connection.CreateCommand();
commandPull.CommandText = "SELECT score FROM userscores WHERE Id = UserID";
DBscore = 
connection.Open();
int RowsAffected = commandPull.ExecuteNonQuery();
connection.Close();

Answer 1

您可以使用ExecuteScalar获取分数:

int dbscore = (int)commandPull.ExecuteScalar();

ExecuteScalar executes the query, and returns the first column of the first row in the result set returned by the query.