Using DataTable.Load() method is not working in case two resultsets returned by DataReader

Question

With the motive of enhancing the performance I am trying to eliminate Dataset use & implement DataReader. Here my Oracle Procedure returning two refcursors & when I am loading the first recordset in to the first DataTable, the next one never gets loaded.

Sample code looks something like this :

DataSet ds = new DataSet();
        using (OracleConnection db = new OracleConnection(conString))
        {
            try
            {
                using (OracleCommand mycom = new OracleCommand())
                {
                    mycom.CommandText = "myPkg.pr_mySP";
                    mycom.Connection = db;
                    mycom.CommandType = CommandType.StoredProcedure;

                    mycom.Parameters.Add("ref_list1", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
                    mycom.Parameters.Add("ref_list2", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
                    //mycom.FetchSize = mycom.FetchSize * 64;

                    db.Open();
                    using (OracleDataReader reader = mycom.ExecuteReader())
                    {

                        DataTable custMapList = new DataTable("dtcustMapList");
                        custMapList.Load(reader);

                        reader.NextResult(); // POST THIS THE SECOND DATATABLE DOESNOT GETTING POPULATED 
                        DataTable custMapSubList = new DataTable("dtcustMapSubList");
                        custMapSubList.Load(reader);

                        ds.Tables.Add(custMapList);
                        ds.Tables.Add(custMapSubList);
                    }
                }
            }
            catch (Exception ex)
            {
                returnString += "Error, " + ex.Message;
            }

I know there are alternative methods like looping using while(reader.Read()) ... & then using reader.NextResult() will work, but in that case I have to change many other codes which I think can be avoided if the above works fine.

Appreciate an early response.

Answer 1

Looking at the reference source for the DataTable.Load method it is clear that the method calls NextResult() before exiting, so you don't need to do it.

 ....
 if(!reader.IsClosed && !reader.NextResult())
      reader.Close();
 ....

And by the way, there is no need to go to the source. Also MSDN says:

The Load method consumes the first result set from the loaded IDataReader, and after successful completion, sets the reader's position to the next result set, if any.

So you just need to remove this line

// reader.NextResult(); // POST THIS THE SECOND DATATABLE DOESNOT GETTING POPULATED