retrive data from a select into a list of lists?

[Ismael]

Hi, Folks.
I'm trying to use a list of lists to retrieve data from a query in a table.
The query is (simplified) "select C1, C2, C3 from Table1". So there are three columns and an unknown number of retrieved registries.
To deal with it I did:

List<List<string>> TabValues();
List<string> Columns();
List<string> Resul();
List<string> Temp();

Columns.Add("C1");
Columns.Add("C2");
Columns.Add("C3");

TabValues.Add(Columns);
TabValues.Add(Temp);
TabValues.Add(Temp);
TabValues.Add(Temp);

I had to include an empty list (Temp) in TabValues so to receive the data.
After I run the command to get the registries in the table I did:

rdr = Cmd.ExecuteReader();
i=0;

while (rdr.Read())
{
    for (j=0; j<3; j++)
    {
        TabValues.ElementAt(i+1).Insert(0, GetValue(j).ToString());
    }
    
    i++;
}

It didn't work. All lists inside TabValues receive the same values.
In this example I used 3 columns to recover, but I have to make my routine generic, with any number of columns.
Can anyone tell me what is wrong in may code? What is the best solution.
Thanks.

 

[jmcilhinney]

It's not really very clear what you're actually trying to achieve. A list of lists is almost certainly not the best way to go about it though. If you were to provide a meaningful example of the data you a starting with and the structure you expect to end up with, that might help.

Regardless, the reason for the problem is the fact that you are adding the same inner list to the outer list three times, rather than adding three separate lists. Consider this: let's say that I am compiling a list of people and I add you to that list three times. If I then put a red shirt on the first person in that list, would it be a surprise that all three people on the list all had a red shirt on? Of course not, because it's just one person on the list three times. The same thing is happening with your code. You are creating one inner list and adding that to the outer list three times. If you add an item to the first inner list then every inner list will not contain that item, because all three inner lists are the same list.

 

[Ismael]

Hi, jmcilhinney. Now I understood the mistake I make. I'll try to clear what I need. I'm making a routine like this:
ReadTable(string TName, string FromClause, string WhereClause, List<List> string Resul)
Inside the routine a construct the Select clause and pass it to the DB. The results are put into the Resul list (of lists). As it's a generic routin, I don't know how many columns will be tretrived in advance and this number is the amount of lists. In Resul, the first list is the columns to research. With the Resul list I'll populate a DataGridView. I've tried with a 2 dimension array, but the problem was other: I didn't know how many registries would be retrived, and this number is the extension of the array. Is this case I could resolve the problem in a non elegant way: before sending data to the routine I make a Select count(*) in the table. It works, but ...
I think my problem now is: how can I insert data in one position of a list of lists? I tried to use
Resul.ElementAt(pos1).ElementAt(pos2), but I failed. Can you help me with this? Or dou you have other way to resolve my problem?
Thanks a lot.

 

[jmcilhinney]

Why can't you just call Fill on a data adapter to populate a DataTable?

 

[Ismael]

Well, I don't khow how to do this. Could you give an example?

 

[jmcilhinney]

What don't you understand about the examples you found when you looked for yourself? There's lots of information out on the web already. There's no need for us to reproduce that. After doing your research and making an attempt to implement it, you would then post a specific question here if what you try doesn't work. Look first, ask questions later.

 

[Skydiver]

Well, I don't khow how to do this. Could you give an example?

Start here:

DataAdapters and DataReaders - ADO.NET

Learn about the ADO.NET DataReader, which retrieves data from a database, and DataAdapter, which retrieves data from a data source and populates a DataSet.

docs.microsoft.com

 

[Ismael]

Ok, Folks. Thanks for your help. I'll see the examples.