OracleDataReader loses results after examination

Question

I have come across a quirky "feature" in Visual Studio and I was interested in seeing if anyone else had noticed this. Or if it is specific to myself.

I have some methods that perform SQL queries on a database and then return an OracleDataReader

method()
{
OracleCommand cmd = new command(query, connection);
OracleDataReader r = cmd.ExecuteReader();

return r;
}

When I am debugging the code that uses this method. I can click on the non public members to view the rows in the results. However once I have viewed these results trying to perform a reader.Read() on the OracleDataReader does not contain any results. Checking the results in the debugger view shows the reader as Empty.

Any time I do not check the results, the code that executes Read works without any problems.

I've not found evidence of this via Google, but my search skills often leave a lot to be desired. If anyone could confirm this on a system of their own or shed some light on the causes I would greatly appreciate it.

Thanks very much.

Answer 1

ADO.NET objects that derive from IDataReader (like your OracleDataReader) provide connected, forward-only access to the data returned by the query, so when you view the results in the debugging visualizer, you are actually stepping through the real data. When the program runs, the DataReader has iterated past the data and reports (correctly) that it is now empty.

If you would like the flexibility to view the data in the debugger, you might consider using a disconnected, random-access data access class like the DataSet or DataTable.

Answer 2

Is there any example of using DataSets and DataTables with Oracle programmatically?