How do I loop through rows with a data reader in C#?
C#asp.netDatatableC# Problem Overview
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 am so sorry, this is solved now. I just wasn't coding right...
And yeah the SqlDataReader.Read() method does what it is supposed to do, again the mistake was mine.
C# Solutions
Solution 1 - C#
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
}
Solution 2 - C#
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())
Solution 3 - C#
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"];
}
Solution 4 - C#
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....
Solution 5 - C#
> How do I loop through rows with a data reader in C#?
IDataReader.Read()
advances the reader to the next row in the resultset.
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 do you want to use to hold that data?
Solution 6 - C#
Actually the Read
method iterating over records in a result set. In your case - over table rows. So you still can use it.
Solution 7 - C#
while (dr.Read())
{
for (int i = 0; i < dr.FieldCount; i++)
{
subjob.Items.Add(dr[i]);
}
}
to read rows in one colunmn
Solution 8 - C#
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();