Reading multiple rows from Database - Where am I going wrong?

string connectionString = ConfigurationManager.AppSett开发者_JS百科ings["AllRttpDBConnectionString"];
MySqlConnection connection = new MySqlConnection(connectionString);
MySqlCommand command = connection.CreateCommand();

command.CommandText = "Select * from test where ServiceName like 'T%' " ;

try
{
  connection.Open();
}
catch (Exception e)
{
  Console.WriteLine(e.ToString());
}

try
{
  MySqlDataReader reader;
  reader = command.ExecuteReader();

  while (reader.Read())
  {
    Player.Name = reader["Name"].ToString();
    Player.Number = Convert.ToInt32(reader["Number"].ToString());

    //push to list
    PlayerList.Add(Player);
  }

  connection.Close();
}
catch (Exception e)
{
  connection.Close();
  logger.Info(e.ToString());
}

Above is the code I am using to read multiple rows from a database into a list. However, all my list items have the exact same data (the last row of the database).

I know its probably a really simple, stupid mistake, but I just can't see it.

It looks like you are adding the same object over and over again, changing the values to what was read fromt the current row. You need to use:

Player player = new Player()
player.Name = reader["Name"].ToString(); 
player.Number = Convert.ToInt32(reader["Number"].ToString()); 

//push to list 
PlayerList.Add(player); 

then add it to the collection.

It looks like you are continually modifying a single Player instance.

To fix it, create a new instance for each record:

while (reader.Read())
{
    // I'm guessing about the type here
    Player player = new Player();
    player.Name = reader["Name"].ToString();
    player.Number = Convert.ToInt32(reader["Number"].ToString());

    //push to list
    PlayerList.Add(player);
}

So what is happening is you aren't making a new Player each time you add it to the list.

Your code when you are adding items to the list should look something like this.

PlayerClass NewPlayer = new PlayerClass;
NewPlayer.Name = reader["Name"].ToString();
NewPlayer.Number = Convert.ToInt32(reader["Number"].ToString());

//push to list
PlayerList.Add(NewPlayer);

Let me suggest you a slight improvement on your code:

public IEnumerable<Player> GetPlayers()
{
    string connectionString = ConfigurationManager.AppSettings["AllRttpDBConnectionString"];
    using (var conn = new MySqlConnection(connectionString))
    using (var cmd = conn.CreateCommand())
    {
        conn.Open();
        cmd.CommandText = "SELECT Name, Number FROM test WHERE ServiceName LIKE 'T%';";
        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                yield return new Player 
                {
                    Name = reader.GetString(0),
                    Number = reader.GetInt32(1)
                };
            }
        }
    }
}

And when you need to create a list:

List<Player> playersList = GetPlayers().ToList();

You also need to make sure to properly dispose all disposable resources as in my example.