SqlDataReader Force It To Read One Row At A Time

Question

It appears that SqlDataReader reads ahead

while (rdrMainLoop.Read())
{
    sID = rdrMainLoop.GetInt32(0);
    nearDupID = rdrMainLoop.IsDBNull(2) ? 0 : rdrMainLoop.GetInt32(2);
    sqlCmdProducer.CommandText = "update [docSVsys] set [FTSnearDupID] = '" + sID.ToString() + "' where [sID] = '" + (sID + 1).ToString() + "';";
    sqlCmdProducer.ExecuteNonQuery();
}

In this example I am updating the next line.
But when I .Read() the next line SqlDataReader does not read the current value.
I assume it batches ahead for speed.
Is there a way to force SqlDataReader to read one line at a time.
I am good with a dirty read (has to be as I may update that line).

What is the actual problem you are trying to solve? It looks like you should be able to do that in a single statement. — Sebastian Meine, Feb 02 '13 at 20:29
@SebastianMeine Yes I know I could do "that" in a single statement. "That" is just simple code to demonstrate the problem. My problem is that I need current values from the time of .Read(). — paparazzo, Feb 02 '13 at 23:14

score 1 · Accepted Answer · answered Feb 07 '13 at 14:16

1

Used a cursor. A cursor reads the current value IF it is a simple select.
A select with a group by and the update in the loop changes the group by then that will not be reflected in the fetch next.

answered Feb 07 '13 at 14:16

paparazzo

44,497
23
105
176

SqlDataReader Force It To Read One Row At A Time

1 Answers1