How do I extract data from a table using a SqlDataReader when the number of rows are not predetermined?

Question

I have an application that will allow a user to take a survey on 40 different fruit types stored in a table. The first screen allows the user to choose from a list of fruit he/she would have tried out. While the user is selecting their fruit type I am getting the ID of the fruit in the table for a SQL comparison and storing the different amount of fruit types they selected.

string strFruits = string.Empty;
foreach (System.Web.UI.WebControls.ListItem li in chkFruits.Items)
{
    if (li.Selected == true)
    {
        strFruits += "'" + li.Value + "'" + ",";
        countFruits += 1;
        ActiveCount += 1;
    }
}

I also have a List Object which I use (with the help of a reader) to pull the fruit types from the database once I have been given the IDs. How do I use the List object and the reader to pull an undetermined amount from my table. Normally, If I knew that I only wanted to pull the top three rows I could do:

SqlCommand cmd3 = new SqlCommand(@"SELECT [ID], [Fruits] from [my_tastyfruits]", conn1);
using (SqlDataReader reader1 = cmd3.ExecuteReader())
{
    while (reader1.Read())
    {
        MyFruits foo = new MyFruits();
        foo.ID = reader1.GetInt32(0);
        foo.Fruits = reader1.GetString(1);
        myFruits.Add(foo);
    }
    //assign fruits here
    strFruit1 = MyFruits[0].Fruits;
    strFruit2 = MyFruits[1].Fruits;
    strFruit3 = MyFruits[2].Fruits;
...
}

However since the count is determined by the user I am not sure how to extract the data via the reader.

@woni declaring the variable. I have strFruit 1,2 and 3 declared as strings — Skullomania
– Skullomania, Commented Sep 1, 2014 at 19:46
What do you do with these variables? Are they used to show something on the web page? — dwonisch
– dwonisch, Commented Sep 1, 2014 at 19:53
@woni Yes. I display the data that I had bound the variable to. — Skullomania
– Skullomania, Commented Sep 2, 2014 at 11:58

user2864740 · Accepted Answer · 2014-09-01 19:56:24Z

1

Leave the data in the (MyFruit) Collection and deal with it appropriately later.

For example, iterate it for display as needed;

foreach (var fruits in MyFruits) {
   Console.WriteLine(fruits.Fruits);
}

It is not possible to assign an arbitrary sized list to an arbitrary number of variables at run-time: the number and types of variables are fixed when the code is compiled.

edited Sep 1, 2014 at 19:56

answered Sep 1, 2014 at 19:16

user2864740

62.5k15 gold badges159 silver badges234 bronze badges

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

2 Comments

Skullomania Over a year ago

using this gives me an error which states var does not contain a definition for 'MyFruits' and another which says that I cannot convert type List to 'string'

user2864740 Over a year ago

@Skullomania I fixed one error in my example (fruit should have been fruits), but those error messages confuse me.

Collectives™ on Stack Overflow

How do I extract data from a table using a SqlDataReader when the number of rows are not predetermined?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related