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

Question

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

What is Personen? It sounds as if it's already a collection of Person, so you have to fill it in the loop. — Tim Schmelter
– Tim Schmelter, Commented Jun 12, 2014 at 9:47
you need to return a list of personen, you are just returning a single one — Weyland Yutani
– Weyland Yutani, Commented Jun 12, 2014 at 9:48
I'm going to guess you want a List<Personen> and would then need to amend the while (rdr.Read()) content to add to the list for each row. — Adam Houldsworth
– Adam Houldsworth, Commented Jun 12, 2014 at 9:48
Tim, It means People in Dutch. This however isn't really relevant. This method retrieves people's choice of menu and their reservation_number. — roy-willemse
– roy-willemse, Commented Jun 12, 2014 at 9:50

Jon Skeet · Accepted Answer · 2014-06-12 09:57:53Z

6

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

edited Jun 12, 2014 at 9:57

answered Jun 12, 2014 at 9:49

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Ian Over a year ago

beaten by 10 seconds :(

Ian Over a year ago

I'd typically prefer returning an IEnumerable<Personen> over the List though - just in case you only need the first X items etc.

Jon Skeet Over a year ago

@Ian: Do you mean fetching it lazily to start with, using yield return? Yes, that's a possibility.

roy-willemse Over a year ago

This is great however how can I access the information in this list from my page? You can't create a list in code behind

Jon Skeet Over a year ago

@Ian: Well, it does have some other consequences - such as keeping the connection open for the whole time you're using it, and understanding that even argument validation etc is lazy. Given the level of sophistication of the OP's code at the moment, I'd rather not go into more advanced techniques. (I suspect using LINQ to SQL or EF would actually be better, to be honest...)

|

Danny Varod · Accepted Answer · 2014-06-12 09:48:10Z

0

You have various options:

a. Return a composite object containing the specific objects you want to return

b. Use out e.g. void MyMethod(out object obj1, out object obj2);

answered Jun 12, 2014 at 9:48

Danny Varod

18.3k5 gold badges76 silver badges116 bronze badges

Comments

Michael Mairegger · Accepted Answer · 2014-06-12 09:49:30Z

0

Just return a list of persons.

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

Question to think about: Why don't you use a O/R-Mapper?

answered Jun 12, 2014 at 9:49

Michael Mairegger

7,35133 silver badges44 bronze badges

Comments

Atish · Accepted Answer · 2014-06-12 09:51:02Z

0

Try creating an List for items. On each iteration through rdr add the item into items List. Finally return an List of objects

answered Jun 12, 2014 at 9:51

Atish

4,4852 gold badges28 silver badges38 bronze badges

2 Comments

Daniel Kelley Over a year ago

An ArrayList? Seriously?

Daniel Kelley Over a year ago

Even with the update this answer seriously pales in comparison to the others.

Dennis_E · Accepted Answer · 2014-06-12 09:51:03Z

0

Inside the while-loop, you are resetting the properties of the Item you are returning, so only the last one is remembered. There are several options.

You can return a List<Personen> or an IEnumerable<Personen> and use yield return. btw, 'Persoon' would probably be a better name than 'Personen'.

answered Jun 12, 2014 at 9:51

Dennis_E

8,90426 silver badges32 bronze badges

1 Comment

Dennis_E Over a year ago

The problem with Stackoverflow: 5 answers appear before you're finished typing...

aydjay · Accepted Answer · 2014-06-12 09:58:32Z

Below you will retrive all of the people with the supplied ID. You are however leaving youself vulnerable to SQL injection as you aren't using SQL Command parameters.

    public static List<Personen> GetAllPersonWithID(int id)
    {
        SqlConnection con = new SqlConnection();
        con.ConnectionString =   System.Configuration.ConfigurationManager.ConnectionStrings["RestaurantConnection"].ConnectionString;

        SqlCommand cmd = new SqlCommand("SELECT * FROM Personen where reservering_id=" + id, con);
        con.Open();
        SqlDataReader rdr = cmd.ExecuteReader();

        List<Personen> rtnList = new List<Personen>();

        while (rdr.Read())
        {
            Personen personen = new Personen();
            personen.id = Convert.ToInt32(rdr["id"]);
            personen.menu_id = Convert.ToInt32(rdr["menu_id"]);
            personen.reservering_id = Convert.ToInt32(rdr["reservering_id"]);
            rtnList.Add(personen);
        }
        con.Close();
        return rtnList;
    }

I hope that helps.

Ian · Accepted Answer · 2014-06-12 10:03:44Z

0

First you need to change you're method signature to return a collection of items, the simplest way to do this is to use an IEnumerable:

public static IEnumerable<Personen> GetAllPersonWithID(int id){}

Next you need to modify you're method to actually return multiple items. In this case you're overwriting the content of the Item object frequently, and only returning the last. Change it to read

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"]);
    yield return Item;
}

edited Jun 12, 2014 at 10:03

answered Jun 12, 2014 at 9:49

Ian

34.7k30 gold badges127 silver badges211 bronze badges

Comments

Hassan Nisar · Accepted Answer · 2014-06-12 10:46:57Z

You can return List<Personen> for your method. It is recommended to use using statement and command parameter.

public static List<Personen> GetAllPersonWithID(int id)
{
    List<Personen> lstPersonen = new List<Personen>();
    string constring = System.Configuration.ConfigurationManager.ConnectionStrings["RestaurantConnection"].ConnectionString;
    string sql = "SELECT * FROM Personen where reservering_id=@id";

    using(SqlConnection con = new SqlConnection(constring))
    {             
       con.Open();
       using(SqlCommand cmd = new SqlCommand(sql, con))
       {
          cmd.Parameters.AddWithValue("@id", id);
          using(SqlDataReader rdr = cmd.ExecuteReader())
          {
             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"]);
                 lstPersonen.Add(Item);
             }
          }
       }
    }

    return lstPersonen;
}

How to use the method:

//provide reservering_id here
List<Personen> lstPersonen = GetAllPersonWithID(int.Parse(TextBox_reservering_id.Text)); 

//find a person having id equals to value present in `TextBox_id.Text`
Personen persoon1;
foreach(Personen p in lstPersonen)
{
     //p gets each item present in list
     if(p.id == int.Parse(TextBox_id.Text))
     {  
         persoon1 = p;
         break;
     } 
}

Collectives™ on Stack Overflow

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

8 Answers 8

7 Comments

Comments

Comments

2 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

7 Comments

Comments

Comments

2 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related