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

It seems you lack some basics... Read the answers below which are perfect to guide you. — Luis Filipe
– Luis Filipe, Commented Apr 18, 2011 at 10:02

Mike · Accepted Answer · 2011-04-18 09:54:02Z

4

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;
}

answered Apr 18, 2011 at 9:54

Mike

6715 silver badges10 bronze badges

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

Comments

Skurmedel · Accepted Answer · 2011-04-18 09:36:47Z

3

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.

answered Apr 18, 2011 at 9:36

Skurmedel

22.3k5 gold badges57 silver badges68 bronze badges

Comments

Niklas · Accepted Answer · 2011-04-18 09:30:22Z

2

try to change the if to a while

while (er.Read())

answered Apr 18, 2011 at 9:30

Niklas

13.2k24 gold badges82 silver badges124 bronze badges

2 Comments

dumbel Over a year ago

Tried that. The results are the same, only prints the first row

VikciaR Over a year ago

But return in while body, effectively breaks that while.

Varun Goel · Accepted Answer · 2011-04-18 10:08:56Z

1

I guesss a simple solution would be to return the whole ResultSet instead of tring to work around with string. Then iterate through its all items from your calling method

answered Apr 18, 2011 at 10:08

Varun Goel

3393 silver badges15 bronze badges

Collectives™ on Stack Overflow

c# database selecting

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related