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

Question

string connectionString = ConfigurationManager.AppSettings["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.

Answer 1

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

Answer 2

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.

Answer 3

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

Answer 4

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.