Going through a DataReader twice

Question

I have the following code which does what it's supposed to do:

objSQLCommand = New SqlCommand("select * from table1", objSQLConnection)

objSQLCommand.Connection.Open()
objSQLDataReader = objSQLCommand.ExecuteReader()

While objSQLDataReader.Read()
    objStringBuilder.Append(objSQLDataReader("forename"))
    objStringBuilder.Append("<br /><br />")
    objStringBuilder.Append(objSQLDataReader("surname"))
    objStringBuilder.Append("<br /><br />")
End While

objSQLDataReader.Close()
objSQLCommand.Connection.Close()

But I need to loop through the objSQLDataReader 1 more time. How would I do that?

Is there a reason why you cannot do what you need to do on the second iteration at the same time as doing the first? — adrianbanks
– adrianbanks, Commented Mar 30, 2011 at 14:45
@adrianbanks: On the first iteration, I want the html to have certain structure, on the second iteration I want the html to have another structure. The first structure ontop of the second. — oshirowanen
– oshirowanen, Commented Mar 30, 2011 at 14:49
DataReader is forward only, is there a reason you cannot do whatever you need in one interation? — HadleyHope
– HadleyHope, Commented Mar 30, 2011 at 14:50
@HadleyHope: On the first iteration, I want the html to have certain structure, on the second iteration I want the html to have another structure. The first structure ontop of the second. — oshirowanen
– oshirowanen, Commented Mar 30, 2011 at 14:52

Marc Gravell · Accepted Answer · 2011-03-30 14:54:03Z

11

Three options:

execute the query twice (readers like this are forwards only)
buffer the data locally, then process it twice (the "buffer" could be an object collection, XML, a DataTable (spit), etc)
write both outputs at the same time; i.e. for each row, write the first format to the first output, then the second format to the second output

I'd probably aim at the last option, as it involves no buffering or repetition; however I would move the logic for each method into 2 distinct methods

answered Mar 30, 2011 at 14:54

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

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

3 Comments

HadleyHope Over a year ago

Or consider using a DataSet and DataTable.Rows msdn.microsoft.com/en-us/library/…

Marc Gravell Over a year ago

@Hadley I actually added that already when clarifying "buffering". But I still don't like them much :)

kolexinfos Over a year ago

In my case, I just put them in a generic collection of a type in my EF, then iterate over them again when I need to.

Kon · Accepted Answer · 2011-03-30 14:54:19Z

4

Loop through data reader only once and load your data into some sort of an instantiated collection (e.g. List<MyDataObject>) that you can reference later to loop through again and again, and again.

answered Mar 30, 2011 at 14:54

Kon

27.5k12 gold badges64 silver badges87 bronze badges

Collectives™ on Stack Overflow

Going through a DataReader twice

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