How do I loop through rows with a data reader in C#?

Question

I know I can use while(dr.Read()){...} but that loops every field on my table, I want to retrieve all the values from the first row, and then second... and so on.

Let's say I have a table like this:

ID--------------Value1--------------Value2------------------Value3
1               hello               hello2                  hello3
2               hi1                  hi2                      hi3

first I want to get, hello, hello2 and hello3 and then go to the second row and get all the values.

Is there a way to achieve this? I hope somebody understand what I mean.

I don't understand what you mean. A DataReader already returns the rows one row at a time, and you can access each column in any order, possibly from the first to the last if that's what you want. Could you update your question to give some concrete code, the expected output, and the output you actually get? — user743382
– user743382, Commented Dec 3, 2011 at 20:58

Rich O'Kelly · Accepted Answer · 2011-12-03 20:59:04Z

89

That's the way the DataReader works, it's designed to read the database rows one at a time.

while(reader.Read()) 
{
  var value1 = reader.GetValue(0); // On first iteration will be hello
  var value2 = reader.GetValue(1); // On first iteration will be hello2
  var value3 = reader.GetValue(2); // On first iteration will be hello3
}

answered Dec 3, 2011 at 20:59

Rich O'Kelly

41.8k9 gold badges87 silver badges114 bronze badges

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

Comments

jgillich · Accepted Answer · 2013-08-09 10:00:00Z

45

int count = reader.FieldCount;
while(reader.Read()) {
    for(int i = 0 ; i < count ; i++) {
        Console.WriteLine(reader.GetValue(i));
    }
}

Note; if you have multiple grids, then:

do {
    int count = reader.FieldCount;
    while(reader.Read()) {
        for(int i = 0 ; i < count ; i++) {
            Console.WriteLine(reader.GetValue(i));
        }
    }
} while (reader.NextResult())

edited Aug 9, 2013 at 10:00

jgillich

77.2k7 gold badges61 silver badges89 bronze badges

answered Dec 3, 2011 at 20:59

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

1 Comment

shawn Over a year ago

I understand how to loop through the reader and get all the values, with Console.Writeline(reader.GetValue(I)); this works fine, I need to add it to an array how to do I get the Mystringarray[I] = reader.GetValue(I) it only adds the first one index 0 of the array and does not continue to add it to the other

Tudor · Accepted Answer · 2011-12-03 21:03:49Z

23

Or you can try to access the columns directly by name:

while(dr.Read())
{
    string col1 = (string)dr["Value1"];
    string col2 = (string)dr["Value2"];
    string col3 = (string)dr["Value3"];
}

answered Dec 3, 2011 at 21:03

Tudor

62.6k13 gold badges105 silver badges147 bronze badges

Comments

marc_s · Accepted Answer · 2011-12-03 20:59:47Z

10

There is no way to get "the whole row" at once - you need to loop through the rows, and for each row, you need to read each column separately:

using(SqlDataReader rdr = cmd.ExecuteReader())
{
    while (rdr.Read())
    {
        string value1 = rdr.GetString(0);
        string value2 = rdr.GetString(1);
        string value3 = rdr.GetString(2);
    }
}

What you do with those strings that you read for each row is entirely up to you - you could store them into a class that you've defined, or whatever....

answered Dec 3, 2011 at 20:59

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Comments

canon · Accepted Answer · 2025-05-09 18:17:57Z

4

How do I loop through rows with a data reader in C#?

IDataReader.Read() advances the reader to the next row in the resultset, returning true if it found a row.

while(reader.Read()){
    /* do whatever you'd like to do for each row. */
}

So, for each iteration of your loop, you'd do another loop, 0 to reader.FieldCount, and call reader.GetValue(i) for each field.

The bigger question is what kind of structure you'd want to hold that data.

edited May 9 at 18:17

answered Dec 3, 2011 at 20:59

canon

41.9k10 gold badges77 silver badges102 bronze badges

Comments

TylerH · Accepted Answer · 2025-04-23 13:34:21Z

1

Actually the Read method iterates over records in a result set. In your case - over table rows. So you still can use it.

edited Apr 23 at 13:34

TylerH

21.3k84 gold badges84 silver badges121 bronze badges

answered Dec 3, 2011 at 20:58

IUnknown

22.5k4 gold badges41 silver badges71 bronze badges

Comments

TylerH · Accepted Answer · 2025-04-23 13:35:01Z

1

Use

while (dr.Read())
{
    for (int i = 0; i < dr.FieldCount; i++)
    {
        subjob.Items.Add(dr[i]);
    }
}

to add rows from one column.

edited Apr 23 at 13:35

TylerH

21.3k84 gold badges84 silver badges121 bronze badges

answered Mar 26, 2015 at 8:05

irfan majeed

619 bronze badges

Comments

PeterJ · Accepted Answer · 2014-07-27 04:21:00Z

-1

Suppose your DataTable has the following columns try this code:

DataTable dt =new DataTable();
txtTGrossWt.Text = dt.Compute("sum(fldGrossWeight)", "").ToString() == "" ? "0" : dt.Compute("sum(fldGrossWeight)", "").ToString();
txtTOtherWt.Text = dt.Compute("sum(fldOtherWeight)", "").ToString() == "" ? "0" : dt.Compute("sum(fldOtherWeight)", "").ToString();
txtTNetWt.Text = dt.Compute("sum(fldNetWeight)", "").ToString() == "" ? "0" : dt.Compute("sum(fldNetWeight)", "").ToString();
txtFinalValue.Text = dt.Compute("sum(fldValue)", "").ToString() == "" ? "0" : dt.Compute("sum(fldValue)", "").ToString();

edited Jul 27, 2014 at 4:21

PeterJ

3,79334 gold badges56 silver badges73 bronze badges

answered Jul 14, 2014 at 11:53

Netaji Chavan

1

Collectives™ on Stack Overflow

How do I loop through rows with a data reader in C#?

8 Answers 8

Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related