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.

I am not certain, but IIRC Datareader's use a high-speed sequential streaming pipeline model. That would imply that you have to completely consume one dataset before you can move on to the next. — RBarryYoung, Jun 13 '16 at 15:44

Steve · Accepted Answer · 2016-06-16T14:22:06.923

8

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

edited Jun 16 '16 at 14:22

answered Jun 13 '16 at 15:42

Steve

213,761
22
232
286

I hope DataTable.Load() approach is as good as looping row by row & retrieving values by keeping in a while loop. – Biki Jun 14 '16 at 08:37
If you are looking for the max performance then probably you could use your own loop over the DataReader. A lot depends on where and what are you doing with the data retrieved. There are lot of questions on performances of DataAdapter.Fill vs DataTable.Load, here for example: http://stackoverflow.com/questions/334658/is-datareader-quicker-than-dataset-when-populating-a-datatable – Steve Jun 14 '16 at 10:16

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

1 Answers1