How to add retrieved records from a table in a List/IEnumerable

Question

I'm trying to retrieve records from a table in MS Access 2010 using OleDbDataReader. In some cases the table I am retrieving records from has multiple records for the supplied query. For example: Two tables; Ist table has two columns - one is Name(primary key) the other contains numbers. 2nd table contains many fields; one being the Name field as the foreign key. In some cases my query matches a record that in both tables returns ONE record, but on other cases there is one record from 1st table, but many records from 2nd table. So my datareader only pulls in one of those records that populates the textboxes. So I wanted to find a way to put those multiple records into a listbox and I asked the question on this forum. The answer given was to use LINQ and "put the results in a List/IEnumerable". I wish is was only that simple. I've tried to incorporate List/IEnumerable and and I'm not only going backwards, but becoming more and more confused. My understanding is that I have to create a whole new class, constructor, set of methods, etc and create a list that will take any type(int, string, etc). IS this correct? Does my new class start with

public class myList : IEnumerable<T>
{
    public IEnumerable<T> GetEnumerator() 
    {
        foreach (object o in objects) 
        {
            yield return 0;
        }
    }
}

Inside method in Form class

while (myreader.Read())
{
    foreach (object o in myList) //do something
}

The T being any type whereas I would otherwise use int or string?

Could you please reformat my code? I just tried, but ended up with the `GetEnumerator` method possibly outside of any class, and with the `while` method definitely outside of any method. — O. R. Mapper, Jul 26 '13 at 13:24
Possible duplicate: [Multiples Table in DataReader](http://stackoverflow.com/questions/12969318/multiples-table-in-datareader) — Dustin Kingen, Jul 26 '13 at 13:28
I just looked at that post "Mutiples Table in DatReader"....how is this a duplicate? not even close. — KFP, Jul 26 '13 at 13:35

score 0 · Answer 1 · answered Jul 26 '13 at 15:11

This is getting a bit confusing... So, basically you have a parent child relationship in your tables. I have a very similar tutorial which will hopefully solve your problem. Thats how the data is read

ArrayList rowList = new ArrayList();
SqlDataReader reader = storedProcCommand.ExecuteReader();
while (reader.Read())
{
   object[] values = new object[reader.FieldCount];
   reader.GetValues(values);
   rowList.Add(values);
}

and this is how the values are added to the ListView

orderDetailsList.Items.Clear();

foreach (object[] row in rowList)
{
   string[] orderDetails = new string[row.Length];
   int columnIndex = 0;

   foreach (object column in row)
   {
        orderDetails[columnIndex++] = Convert.ToString(column);
   }

   ListViewItem newItem = new ListViewItem (orderDetails);
   orderDetailsList.Items.Add (newItem);
}

The output is something like this... enter image description here

score 0 · Answer 2 · 2013-07-26T15:21:18.820

If I understand you right, you're looking to get all records from one table (I'll call it Table2) where the name matches the name on one record in another table (Table1)? I do this a lot, using LINQ and a SQL database.

I'm assuming you already have the connection to the database. Have you created a method to retrieve the desired record from Table1? If so, you already have the name. So I would create another method like this:

public List<Table2> GetList(string name)
{
    List<Table2> list = new List<Table2>();
    list = db.Table2.Where(q => q.NameField = name).ToList();
    return list;
}

You can then call this method when populating the controls and databind the list into whatever control you want to display the list. So something like this:

List<Table2> list = new List<Table2>();
list = GetList("This is the name from the Table1 data");
listviewcontrol.DataSource = list;

The method for filling the control with the data varies a bit depending on which control you're using. EDIT: Rwiti has some good code there for adding the data to the listview.

If you want to display just part of the data from the list of Table2 records (for example, a Location field to populate a combobox), I'd suggest something like this:

List<string> locations = new List<string>();
foreach (Table2 record in list)
{
    locations.Add(record.Location);
}
comboBox1.DataSource = locations;

How to add retrieved records from a table in a List/IEnumerable

2 Answers2