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?

Answer 1

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

Answer 2

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.