c# database selecting

Question

Im having a table called transaktions where i inserted customers billings and customer number. My primary key is an int id that has identity specifikation. My question is how do i select all rows that contains a specific customer_nr and returns all the results?

Right now im doing:

public string getCustomerTransaktions(string CustNr)
{
    using (var cmd = new SqlCommand("select billing_name from [transaktions] where customer_nr = @customer_nr", Connect()))
    {
        cmd.Parameters.AddWithValue("@customer_nr", custNr);
        using (var er = cmd.ExecuteReader())
        {
            if (er.Read())
            {
                return (string)er["billing_name"];
            }
        }
    }
    return "UNKNOWN";
}

This will only print the first row that matches the customer nr, there are atleast 20 rows left. Anyone have any suggestion that will help me? Best Regards

Answer 1

Agree both with @Niklas and @Skurmedel, you need to both use a loop for processing multiple records and collate them together before returning them as a result.

e.g.

public List<string> getCustomerTransaktions(string CustNr)
{
    List<string> names = new List<string>();

    using (var cmd = new SqlCommand("select billing_name from [transaktions] where customer_nr = @customer_nr", Connect()))
    {
        cmd.Parameters.AddWithValue("@customer_nr", custNr);
        using (var er = cmd.ExecuteReader())
        {
            while(er.Read())
            {
                names.Add((string)er["billing_name"]);
            }
        }
    }

    return names;
}

Answer 2

Since you do return ... and your method specification pretty much limits the results to one particular value, you can never return more than one billing name. It will return a name as soon as a row has been read, or your default if no rows were returned.

You should put the values in a list or likewise and return that.