How can I return multiple string values using while loop+

Question

This is what I want: A method that is within a class that will return iteratively the values of a certain column. This values will the be added to a combobox when the method is invoked. Here is my attempt:

  public string FillCombo()
      {
            string connstring = "Data Source=HP\\SQLEXPRESS;Initial Catalog=Arana;Integrated Security=True";
            string query = "Select * from categorias";
            SqlConnection conn = new SqlConnection(connstring);
            SqlCommand command = new SqlCommand(query, conn);
            SqlDataReader read;
                conn.Open();
                read = command.ExecuteReader();
                while (read.Read())
                {
                    string combodata = read.GetString(1);
                    return (combodata); 
                }
               return null;
        }

however, when this method is invoked, it only returns the first row into de combobox, not the other values.

How would you expect it to return multiple values? What would you expect the caller to see? It sounds like you should return a List<string> or something similar. — Jon Skeet
– Jon Skeet, Commented Nov 29, 2013 at 22:55
This will only ever return one result because you are returning the first item and exiting the function before the data reader gets a chance to read the next item. What's the result you are expecting - are you just attempting to fill a combobox with values from the database? — Charleh
– Charleh, Commented Nov 29, 2013 at 23:03
Instead if using return in your while loop which will return from the method, you could add each data value to a collection outside of your method. — user1548266
– user1548266, Commented Nov 29, 2013 at 23:04

Reactgular · Accepted Answer · 2013-11-29 23:02:49Z

1

It's called yield

http://msdn.microsoft.com/en-us/library/vstudio/9k7k7cf0.aspx

From the manual

public static System.Collections.IEnumerable Power(int number, int exponent)
{
    int result = 1;

    for (int i = 0; i < exponent; i++)
    {
        result = result * number;
        yield return result;
    }
}

yield will send a collection of return results from inside a loop after the loop has completed.

You can close data connections using a try/finally block around the loop.

  public IEnumerable FillCombo()
  {

        SqlConnection conn = new SqlConnection(connstring);
        SqlCommand command = new SqlCommand(query, conn);
        SqlDataReader read;
        conn.Open();
        read = command.ExecuteReader();
        try
        {
            while (read.Read())
            {
                yield return read.GetString(1);
            }
        }
        finally
        {
            read.close();
            conn.close();
        }
    }

A cool and often overlooked feature of C#

edited Nov 29, 2013 at 23:02

answered Nov 29, 2013 at 22:56

Reactgular

55.1k24 gold badges175 silver badges219 bronze badges

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

4 Comments

Reactgular Over a year ago

@MikeChristensen most data connections support IDispose so wrapping the loop in a using statement will close the reader when the function exists.

Mike Christensen Over a year ago

I think if the data reader is closed before you materialize the enumeration, you're gonna get an exception. I think you'd be better off returning a List<String> or String[]

Reactgular Over a year ago

@MikeChristensen what do you think of my edit? Haven't tested it but it should work.

Mike Christensen Over a year ago

Yea, I'd have to test that out too. If the finally block is executed after you iterate through the returned enumeration (which would be cool) then it should work! I'm curious so I'll give it a shot when I have some free time.

gpmurthy · Accepted Answer · 2013-11-29 23:34:58Z

0

Consider using a List of string as an output. The following minor change to your code should help...

public List<string> FillCombo()
{
    List<string> comboList = new List<string>();
    string connstring = "Data Source=HP\\SQLEXPRESS;Initial Catalog=Arana;Integrated Security=True";
    string query = "Select * from categorias";
    SqlConnection conn = new SqlConnection(connstring);
    SqlCommand command = new SqlCommand(query, conn);
    SqlDataReader read;
    conn.Open();
    read = command.ExecuteReader();
    while (read.Read())
    {
        string combodata = read.GetString(1);
        comboList.Add(combodata);
    }
    return comboList;
}

Good Luck!

answered Nov 29, 2013 at 23:34

gpmurthy

2,42721 silver badges21 bronze badges

Collectives™ on Stack Overflow

How can I return multiple string values using while loop+

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related