C# Retrieve ALL SQL Query Results

Question

else if (listBox1.SelectedIndex == 1)
{
    String sql2 = "SELECT FirstName, LastName FROM Players WHERE Team = 'Milwaukee Bucks'" +
                  "AND Number < 24";
    // command statement
    command = new SqlCommand(sql2, cnn);
    SqlDataReader reader = command.ExecuteReader();

    // Get table values
    if (reader.Read())
    {
        textBox1.Text = reader.GetString(0).ToString() + " " + reader.GetString(1).ToString();
    }
    cnn.Close();
    command.Dispose();
}

Above is a section of my code. I have a list box with options for the user to choose, and based on which item is selected, a display button will run a query and return the results to a textbox.

However, when I run the code I am only getting one result back. The query returns players on the Bucks who have a number less than 24. There should be multiple of them but I am only getting one in my C# application.

you need to do `while(reader.Read())`, but that will overwrite textBox1 on each loop iteration so I don't think that is the whole solution. — Crowcoder, Jul 21 '20 at 12:44
SqlDataReader does a secuential reading, so every call to `reader.Read()` will return the next record — Cleptus, Jul 21 '20 at 12:44
This answer ([How do I fill a DataTable using DataReader](https://stackoverflow.com/a/21765430/9365244)) gives an example of an alternative method for loading all the data — JayV, Jul 21 '20 at 12:46
By the way, if you have an `if else` for each item in the list then you are doing it wrong. You can dynamically get the selected list item and use its value as a parameter in your query. — Crowcoder, Jul 21 '20 at 12:48
I do have multiple if else's. I had no clue how to do this more efficiently, can you please explain more? — Kajin, Jul 21 '20 at 12:53

score 4 · Accepted Answer · answered Jul 21 '20 at 12:45

You need to use a while loop to read all the lines instead of reading a single line (via Read()).

Microsoft Documentation has an example of how to use the while loop.

StringBuilder sb = new StringBuilder()
while (reader.Read())
{
    sb.AppendLine(reader.GetString(0).ToString() + " " + reader.GetString(1).ToString());
}
textBox1.Text = sb.ToString();

C# Retrieve ALL SQL Query Results

1 Answers1