How can I get the content of 2 columns and show this?

Question

    public void ShowFirstFiveHighScore()
    {
        string query = "SELECT 'key', 'PlayerName', 'HighScore' FROM PlayerPoints ORDER BY HighScore DESC LIMIT 5";

        if (this.OpenConnection() == true)
        {
            MySqlCommand cmd = new MySqlCommand(query, _connection);

            MySqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
               MessageBox.Show(reader.GetString(...);
            }

            cmd.ExecuteNonQuery();
            this.CloseConnection();
        }
    }

I try to show the columns "PlayerName" and "HighScore". Maybe in a MessageBox? Any Help? Thanks.

Answer 1

First of all, you don't need to use single quotes (') with column names when you call them in a SELECT part in your query. You need to use it with character values.

Second of all, MySqlDataReader.GetString method takes int as a parameter which is zero-based column number. That means you can specify your columns with 1 and 2 and show them as you want.

KEY is a reserved word in MySQL. You need to quote it with ` character. However, the best solution is to change the name to a nonreserved word.

Your ExecuteNonQuery is meaningless with SELECT statement. It doesn't do anything at all.

You need to use using statement to dispose your MySqlCommand and MySqlDataReader (also MySqlConnection when you write).

string query = "SELECT `key`, PlayerName, HighScore FROM PlayerPoints ORDER BY HighScore DESC LIMIT 5";
using(MySqlCommand cmd = new MySqlCommand(query, _connection))
using(MySqlDataReader reader = cmd.ExecuteReader())
{
      while (reader.Read())
      {
           MessageBox.Show(string.Format("PlayerName: {0} HighScore: {1}",
                                          reader.GetString(1),
                                          reader.GetString(2)));
      }
}

Thank @Soner. It works. How can I show all five results in a MessageBox? not in five different MessageBoxes?

Then you can concatenate your 5 rows in one string inside your while statement and show it outside of while statement. Like;

string s = "";
while (reader.Read())
{
    s += string.Format("PlayerName: {0} HighScore: {1}\n",
                        reader.GetString(1),
                        reader.GetString(2)));
}
MessageBox.Show(s);