How to scroll to first record again using OracleDataReader class or another class?

Question

I'm migrating a Java application to VB.Net and I try to translate following Java code

Statement stmt 
    = conx.createStatement
        (ResultSet.TYPE_SCROLL_INSENSITIVE
        ,ResultSet.CONCUR_READ_ONLY
        );
ResultSet rsSheet = stmt.executeQuery(sSql);
bStatus = rsSheet.next();
...
bStatus = rsSheet.first();

In this code, a scrollable ResultSet is used. I can read the records returned by executeQuery() function and when I have terminated to read them, I can read it again without interrogating the Database a second time.

You can find some information on ResultSet here https://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html

My translated code is following

Dim cmd = conx.CreateCommand()
cmd.CommandText = sSql
Dim rsSheet as OracleDataReader = cmd.ExecuteReader()
bStatus = rsSheet.Read()
...
bStatus = rsSheet.? 'how to read first record again ?

But I don't find how to do so that OracleDataReader is scrollable ?

I can read the ResultSet from first to last record but I cannot read it again.

The only simple solution that I found to read all these records again is to call ExecuteReader() function a second time.

QUESTIONS

Is OracleDataReader Class scrollable ? How ?

Does another Class exist to do the job ? Which ?

PS: using Linq is not solution because SQL statements are executed in an environnement where Database structure is unknown. It is impossible to create entities.

Answer 1

A DataReader is one way only. Use a DataTable. This is an in memory representation of the result set. You can also use a DataTable to as a DataSource for various controls. You can use Linq on a DataTable.AsEnumerable()

Private Sub OPCode()
    Dim sSql = "Your command text"
    Dim dt As New DataTable
    Using cn As New OracleConnection(ConStr),
            cmd As New OracleCommand(sSql, cn)
        cn.Open()
        dt.Load(cmd.ExecuteReader)
    End Using
    'Code to read data.
End Sub

EDIT

The simplest way to see what is in the DataTable is to display it in a DataGridView if this is WinForms.

DataGridView1.DataSource = dt.

To access a specific row and column.

Dim s = dt.Rows(1)("ColName").ToString

The Rows collection starts with index 0 and the column name is from you Select statement. You then need to convert to the datatype with .ToString or Cint(), CDbl() etc. as this returns an object.