Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader Missing Results

Question

I have a very simple method that utilizes Oracle.ManagedDataAccess to query datatables in Oracle. The code is below.

private System.Data.DataTable ByQuery(Oracle.ManagedDataAccess.Client.OracleConnection connection, string query)
{
    using(var cmd = new Oracle.ManagedDataAccess.Client.OracleCommand())
    {
        cmd.Connection = connection;
        cmd.CommandText = query;
        cmd.CommandType = System.Data.CommandType.Text;
        var dr = cmd.ExecuteReader();
        dr.Read();
        var dataTable = new System.Data.DataTable();
        dataTable.Load(dr);
        var recordCount = dataTable.Rows.Count;
        return dataTable;
    }
}

Using a very simple query such as:

SELECT * FROM NKW.VR_ORDER_LI WHERE CTRL_NO = 10

Returns 32 rows of data. However when I run the exact same query from Oracle SQL Developer using the exact same user account I'm using in my connection string for the C# App, I get 33 results.

I'm consistently missing a single row of data.

I've tried querying a different CTRL_NO:

SELECT * FROM NKW.VR_ORDER_LI WHERE CTRL_NO = 17

.Net returns 8 results. Oracle Sql Developer returns 9 results.

I've tried removing the WHERE statement and just getting all results.

Still 1 row difference between the two.

I've tried googling for an answer but haven't been successful. Any help or advice would be appreciated.

UPDATE 1:

I've determined that I'm always missing the first result that I see in Oracle SQL Developer when I run the exact same query from my C# App.

UPDATE 2:

As suggested I took the DataTable out of the equation.

int rowCount = 0;
while(dr.Read())
{
  rowCount++;
}

rowCount skipping the DataTable still results in a missing record.

UPDATE 3:

Tested against a completely different table, NKW.VR_ORDER_LI is actually a view. Still had the same results for some reason I end up with one less row of results from the ExecuteReader() than I do from within SQL Developer.

I suggest you to avoid datatable fill and use the reader approach, that way A) you gain more performance B) you control any parsing exceptions that may be happening behind the curtain. — Pablo Recalde
– Pablo Recalde, Commented Aug 17, 2017 at 17:57
I've also determined that it is always throwing out the first row of the results. Whatever the query shows in Oracle SQL Developer as the first record, that is the record missing from the dataTable.Rows. — TGardner
– TGardner, Commented Aug 17, 2017 at 17:58

TGardner · Accepted Answer · 2017-08-17 18:21:29Z

1

I ended up figuring out my issue from this thread:

Datareader skips first result

So the culprit in all of this was this part of the code:

var dr = cmd.ExecuteReader();
dr.Read();
var dataTable = new System.Data.DataTable();
dataTable.Load(dr);

The first dr.Read() was'nt neccessary. Getting rid of this line of code solved the problem.

Final fix:

var dr = cmd.ExecuteReader();
var dataTable = new System.Data.DataTable();
dataTable.Load(dr);

I also went back to using the DataTable because it is more consistent with how we interact with transactional data throughout our project at the current time.

answered Aug 17, 2017 at 18:21

TGardner

1763 silver badges8 bronze badges

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

Collectives™ on Stack Overflow

Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader Missing Results

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related