Question Same row is returned when Getting list from database

[ahalotaibii]

Hello Everyone , I'm trying to get multiple rows from the database and my problem is the last rows is returned for all

 private static List<Messages> GetMessages(string LastID)
 {
    
    
     SqlCommand sqlCommand;
     SqlConnection con;
     con = new SqlConnection("data source = ");

     string sql = @"SELECT TOP 2 Recepient,Body,QueueID FROM v_Messages WHERE QueueID>" + LastID + " order by QueueID ";
     //string sql = @"SELECT TOP 2 [QueueID] FROM v_Messages WHERE QueueID>" + LastID + " order by QueueID ";
  
     List<Messages> Messages = new List<Messages>();
     Messages Message = new Messages();
     var names = new List<string>();

     try
     {
         con.Open();
         using (sqlCommand = new SqlCommand(sql, con))
         {
             using (var rdr = sqlCommand.ExecuteReader())
             {
                 if (rdr.HasRows)
                 {
                     while (rdr.Read())
                     {
                         //names.Add();

                         Messages.QueueID = rdr["QueueID"].ToString();
                         Messages.Recepient = "0" + rdr["Recepient"].ToString().Trim().Substring(3);
                        
                         Messages.Body = rdr["Body"].ToString().Trim();
                      

                         Messages.Add(Message);
                     }
                 }
             }
         }
    
         return Messages;

     }
     catch (Exception ex)
     {
         return Messages;
     }
     finally
     {

         con.Close();
     }
 }

when I try to return single column (List of strings instead of module) the data returned correctly

it's console application

 

[JohnH]

You have only created a new Messages object outside the loop so all data is assigned to that, create it inside the loop, one for each iteration.

Is "Messages" as plural a good type name? Does one object contain multiple "messages"?

 

[Skydiver]

Furthermore, TOP 2 will only select 2 rows, not all rows which you say that you want to get.

And lastly: Use SQL parameters, don't compose queries by blindly appending string values that may not have been sanitized.