How to know when to stop filling an OracleDataAdapter

Question

I'm using the OPD.NET dll in a project that is accessing oracle.

Users can type in any SQL into a text box, that is then executed against the db. I've been trying to use the OracleDataAdapter to populate a datatable with the resultset, but I want to be able to return the resultset in stages (for large select queries).

An example of my problem is...

If a select query returns 13 rows of data, the code snippet below will execute without issue until the fourth time oda.Fill (start row is 15 which doesn't exist) is called, I presume because it is calling into a reader that has closed or something similar.

It then will throw a System.InvalidOperationException with the message - Operation is not valid due to the current state of the object.

How can I find out how many rows in total the command will eventually contain (so that I don't encounter the exception)?

OracleDataAdapter oda = new OracleDataAdapter(oracleCommand);
oda.Requery = false;

var dts = new DataTable[] { dt };
DataTable dt = new DataTable();

oda.Fill(0, 5, dts);
var a = dts[0].Rows.Count;
oda.Fill(a, 5, dts);
var b = dts[0].Rows.Count;
oda.Fill(b, 5, dts);
var c = dts[0].Rows.Count;
oda.Fill(c, 5, dts);
var d = dts[0].Rows.Count;

Note: I've omitted the connection and oracle command objects for brevity.

EDIT 1: I've just thought I could just wrap the SQL entered by the user in another query and execute it... SELECT COUNT(*) FROM (...intial query in here...) but this isn't exactly a clean solution, and surely there is a method somewhere that I haven't seen?

Thanks in advance.

Answer 1

You could add an Analytic COUNT to your query:

SELECT foo, bar, COUNT(*) OVER () TheCount WHERE ...;

That way the count of the entire query is returned with each row in TheCount, and you could set your loop to terminate accordingly.

Answer 2

For paging in Oracle, see: http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html

There is no way to know the record set count without running a separate count(*) query. This is by design. The DataReader and DataAdapter are forward-only, read only.

If efficiency is a concern (i.e., large record sets), one should let the database do the paging and not ask the OracleDataAdapter to run the full query. Imagine if Google filled a DataTable with all 1M+ results for each user search!! The following article addresses this concern, although the examples are in sql:

http://www.asp.net/data-access/tutorials/efficiently-paging-through-large-amounts-of-data-cs

I've revised my example below to allow paging on any sql query. The calling procedure is responsible for keeping track of the user's current page and page size. If the result set is less than the requested page size, there are no more pages.

Of course, running custom sql from user input is a huge security risk. But that wasn't the question at hand.

Good luck! --Brett

DataTable GetReport(string sql, int pageIndex, int pageSize)
{
    DataTable table = new DataTable();

    int rowStart = pageIndex * pageSize + 1;
    int rowEnd = (pageIndex + 1) * pageSize;

    string qry = string.Format(
@"select * 
from (select rownum ""ROWNUM"", a.*
    from ({0}) a
    where rownum <= :rowEnd)
where ""ROWNUM"" >= :rowStart
", sql);
    try
    {
        using (OracleConnection conn = new OracleConnection(_connStr))
        {
            OracleCommand cmd = new OracleCommand(qry, conn);
            cmd.Parameters.Add(":rowEnd", OracleDbType.Int32).Value = rowEnd;
            cmd.Parameters.Add(":rowStart", OracleDbType.Int32).Value = rowStart;
            cmd.CommandType = CommandType.Text;
            conn.Open();
            OracleDataAdapter oda = new OracleDataAdapter(cmd);
            oda.Fill(table);
        }
    }
    catch (Exception)
    {
        throw;
    }
    return table;        
}

Answer 3

To gain control over Fill DataTable Loop you need own the loop.

Then build your own Function to Fill DataTable using OracleDataReader.

To get Columns information, you can use dataReader.GetSchemaTable

To Fill the Table:

  MyTable.BeginLoadData 
  Dim Values(mySchema.rows.count-1)
  Do while myReader.read
    MyReader.GetValues(Values)
    MyTable.Rows.add(Values)

    'Include here your control over load Count 
  Loop
  MyTable.EndLoadData