How to return multiple objects in a method with c#?

I have a method which retrieves multiple results from my database, I want to return each of these results as an object back to my page however currently it is just returning the last result of the sql command.

Code on my page calling the method:

Personen persoon1 = Personen.GetOneItem(int.Parse(TextBox_id.Text));

Code of the method:

public static Personen GetAllPersonWithID(int id)
{
    SqlConnection con = new SqlConnection();
    con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["RestaurantConnection"].Connection
String;
    SqlCommand cmd = new SqlCommand("SELECT * FROM Personen where reservering_id=" + id, con);
    con.Open();
    SqlDataReader rdr = cmd.ExecuteReader();
    Personen Item = new Personen();
    while (rdr.Read())
    {
        Item.id = Convert.ToInt32(rdr["id"]);
        Item.menu_id = Convert.ToInt32(rdr["menu_id"]);
        Item.reservering_id = Convert.ToInt32(rdr["reservering_id"]);
    }
    con.Close();
    return Item;
}

And it only returns the last result in the object.

Thanks in advance

Jon Skeet
people
quotationmark

Basically, you should return a List<Personen> or some similar collection type. (Consider declaring the return type as IList<Personen> or IEnumerable<Personen> and using List<Personen> as the implementation type.)

Note that currently you are repeatedly overwriting the properties of a single object - you need to fix that too.

For example:

...
List<Personen> list = new List<Personen>();
while (rdr.Read())
{
    Personen item = new Personen();
    item.id = Convert.ToInt32(rdr["id"]);
    item.menu_id = Convert.ToInt32(rdr["menu_id"]);
    item.reservering_id = Convert.ToInt32(rdr["reservering_id"]);
    list.Add(item);
}
...
return list;

Additionally:

  • You should use using statements to close your connection, command and reader
  • You should use parameterized SQL instead of embedding the parameter in your SQL
  • You should follow .NET naming conventions, e.g. MenuID instead of menu_id
  • Consider casting to int or using rdr.GetInt32() instead of calling Convert.ToInt32.
  • If Personen is already a plural noun, change it to Person so that it represents a single person

people

See more on this question at Stackoverflow