How to know when to stop filling OracleDataAdapter

Question

I am using OPD.NET dll in a project that accesses oracle.

Users can enter any SQL code into a text field, which is then executed against db. I am trying to use OracleDataAdapter to populate a datatable using a result set, but I want to be able to return the result set in stages (for large custom queries).

An example of my problem is ...

If the select query returns 13 lines of data, the code fragment below will be executed without problems until the fourth moment oda.Fill (the initial line is 15, which does not exist) is called, I suppose, because it calls the reader who closed or something like that .

Then it throws a System.InvalidOperationException message with the message - the operation is invalid due to the current state of the object.

How do I know how many lines the command will eventually contain (so that I do not encounter an 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 briefly omitted the connection and oracle command objects for brevity.

EDIT 1: I just thought I could just wrap the SQL entered by the user in another query and execute it ... SELECT COUNT (*) FROM (... intial query here ...) but this is not a completely clean solution, and Of course, is there a method somewhere that I have not seen?

Thanks in advance.

Answer 1

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

There is no way to find out the record set score without running a separate count (*) request. This is by design. DataReader and DataAdapter are read-only, read-only.

If efficiency is a problem (i.e. large sets of records), you need to let the database perform a search call and not query OracleDataAdapter to run a full query. Imagine if Google populated a DataTable with all 1M + results for each user search! The following article addresses this issue, although examples are provided in sql:

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

I reviewed my example below to allow paging for any SQL query. The calling procedure is responsible for tracking the current user page and page size. If the result set is smaller than the requested page size, there are no more pages.

Of course, running custom SQL with user input is a huge security risk. But that was not a question.

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 2

You can add an analytical query to your query:

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

Thus, the counter of the entire request is returned with each line in TheCount, and you can set that your loop ends appropriately.

Answer 3

To gain control of the Fill DataTable Loop, you need your own loop.

Then create your own function to populate the DataTable with OracleDataReader.

To get information about columns, you can use dataReader.GetSchemaTable

Fill in 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