CODEWITHSUNDEEP
c#mysql
Ayush
Ayush

Reputation: 42450

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

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.

Upvotes: 1

Views: 14402

Answers (4)

Darin Dimitrov
Darin Dimitrov

Reputation: 1039268

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.

Upvotes: 2

msarchet
msarchet

Reputation: 15242

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

Upvotes: 2

Daniel
Daniel

Reputation: 1793

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.

Upvotes: 7

Jeff Sternal
Jeff Sternal

Reputation: 48623

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

Upvotes: 4

Related Questions