Reading multiple rows with SQL reader

Question

So here is the code I currently have, when reading one result from a table works fine,

SqlDataReader reader = cmd.ExecuteReader();

while (reader.Read())
{
    newValue = reader.GetString(0);
    newPageID = reader.GetInt32(1);
}

However, the problem comes when there are multiple rows that are being returned. The way I tried to do this was like so,

int counter = 0;
List<Trigger> TriggerValues = new List<Trigger>();

while (reader.Read())
{
    TriggerValues.Add(new Trigger(reader.GetString(counter), reader.GetInt32(counter+1)));
    counter++;

}

But this does not work, which I think is because reader will only return one row. Is there a simple way to modify what I have here to read in new objects row by row? Thanks.

"But this does not work" -- what results are you seeing? how does it "not work"? — rory.ap
– rory.ap, Commented Apr 17, 2015 at 14:08
Looks right to me. You sure that multiple rows are being returned? — tier1
– tier1, Commented Apr 17, 2015 at 14:08
I have edited your title. Please see, "Should questions include “tags” in their titles?", where the consensus is "no, they should not". — John Saunders
– John Saunders, Commented Apr 17, 2015 at 14:08
If I did not overlook something severe what you posted should work. Maybe your query simply does only return one row? — usr
– usr, Commented Apr 17, 2015 at 14:08
datareader reads one row per each iteration until reading whole rows, you need to use it correctly or use a dataadaptor to fill the whole rows in a source like datatable or dataset or etc — void
– void, Commented Apr 17, 2015 at 14:11

Ilia Maskov · Accepted Answer · 2015-04-17 14:09:27Z

8

Look at GetString(0) GetInt32(1), 0 and 1 are the column index, not row. So you should leave it and remove counter

List<Trigger> TriggerValues = new List<Trigger>();

while (reader.Read())
{
    TriggerValues.Add(new Trigger(reader.GetString(0), reader.GetInt32(1)));
}

edited Apr 17, 2015 at 14:09

answered Apr 17, 2015 at 14:08

Ilia Maskov

1,91816 silver badges27 bronze badges

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

3 Comments

usr Over a year ago

Good spot given that no error information was in the question.

user3407039 Over a year ago

Ah this works perfectly. I was thinking that they referred to rows.

user3407039 Over a year ago

Was just waiting for the time limit xD

entropic · Accepted Answer · 2015-04-17 14:09:58Z

4

Everytime you execute reader.Read(), you are loading a new row. The indices you are supplying to the .GetString() and .GetInt32() functions is a column index, not a row index.

You simply need to call

List<Trigger> TriggerValues = new List<Trigger>();
while (reader.Read())
{
    TriggerValues.Add(new Trigger(reader.GetString(0), reader.GetInt32(1)));
}

answered Apr 17, 2015 at 14:09

entropic

1,68314 silver badges27 bronze badges

Collectives™ on Stack Overflow

Reading multiple rows with SQL reader

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related