How to read two rows values at the same time from a SqlDataReader

Question

How can I read values from two consecutive rows from SqlDataReader at the same time ?!

SqlCommand query2 = new SqlCommand("SELECT lon , lat FROM Student Where B_ID = @B_ID ORDER BY Distance ASC");
// some value for @B_ID
            query2.Connection = cn;
SqlDataReader readStd = query2.ExecuteReader();
while (readStd.Read())
            {
                // here I want to calculate the distance between each consecutive rows 
// by taking the latitude and longitude from each row
                    double d = DistanceBetweenPlaces(Convert.ToDouble(readStd.GetValue(0)), Convert.ToDouble(readStd.GetValue(1)), ???, ???);
                    totalDistance = totalDistance + d;
                //}
            }

Why not just read the two rows in succession, storing the values in local variables, and then perform the calculation on that? — DonBoitnott
– DonBoitnott, Commented Mar 11, 2014 at 16:43

Luke Marlin · Accepted Answer · 2014-03-11 16:58:00Z

You can't do that directly. Each call to SqlDataReader.Read advances the reader, allowing you to retrieve the values of the row it is pointing too. Therefore you can't "read two rows at the same time". Refer to the MSDN Documentation.

What you can do, however, is using some temporary values like that :

SqlCommand query2 = new SqlCommand("SELECT lon , lat FROM Student Where B_ID = @B_ID ORDER BY Distance ASC");
    // some value for @B_ID
    query2.Connection = cn;
    SqlDataReader readStd = query2.ExecuteReader();
    double tempLong = 0.0;
    double tempLat = 0.0;
    bool isFirstIteration = true;
    while (readStd.Read())
        {
            if(isFirstIteration)
            {
                isFirstIteration = false;
            }
            else
            {
                double d = DistanceBetweenPlaces(tempLong, tempLat, Convert.ToDouble(readStd.GetValue(0)), Convert.ToDouble(readStd.GetValue(1)));
                totalDistance = totalDistance + d;
            }
            tempLong = Convert.ToDouble(readStd.GetValue(0));
            tempLat = Convert.ToDouble(readStd.GetValue(1));            
        }

Not tested (and maybe wrong because I don't know your method definition), but I think you will get the general idea.

Collectives™ on Stack Overflow

How to read two rows values at the same time from a SqlDataReader

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related