Sequential fetch mode tuning and OracleDataTable editing

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
mrjoltcola
Posts: 24
Joined: Tue 21 Sep 2010 07:17

Sequential fetch mode tuning and OracleDataTable editing

Post by mrjoltcola » Fri 26 Aug 2011 01:42

I have a table editor that has multiple tables. The user can switch between a data grid view of the data and a SQL window and other things.

1) How do I tune the number of rows fetched in sequential mode. I'd like to raise the batch count to say 500 at a time so there is less small fetches on fragile networks.

2) When I open a table for edit, I return an OracleDataTable which is in Open state (live), and it is bound to the grid. Since I have connection sharing, I assume the OracleDataTable holds onto this connection as long as it is open? So my current problem is since my app has connection sharing, I get an error if another operation is attempted while a table is open.

I could simply disallow the user to leave the edit tab, or close the table when he leaves the tab, but I don't want to do that. So I assume my only option is to open a 2nd DB connection? Is there any easy way to manage that? Any way to detect "this connection is busy, don't use it, get another one" ?

Shalex
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Mon 29 Aug 2011 15:39

mrjoltcola wrote:1) How do I tune the number of rows fetched in sequential mode. I'd like to raise the batch count to say 500 at a time so there is less small fetches on fragile networks.
Please use the StartRecord and MaxRecords properties of OracleDataTable to retrive limited resultset. If MaxRecords=0 and FetchAll=false, the size of the result set is determined automatically. Refer to http://www.devart.com/dotconnect/oracle ... Table.html.
mrjoltcola wrote:So my current problem is since my app has connection sharing, I get an error if another operation is attempted while a table is open.
Specify the exact text of the error and send us a small test project with the corresponding DDL/DML script to reproduce the issue in our environment.

mrjoltcola
Posts: 24
Joined: Tue 21 Sep 2010 07:17

Post by mrjoltcola » Mon 29 Aug 2011 17:13

Hi Shalex, thanks.

Can you simply tell me if I return an OracleDataTable for read-only purposes, is it ok for me to close the table? It seemed to work ok for that.

For editing, is there a disconnected option for the datatable? ie. where it opens connections on demand?

Shalex
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Wed 31 Aug 2011 14:26

OracleDataTable opens connection when making select/insert/delete/update operations against database. When current operation is finished, connection is closed. After filling OracleDataTable, interoperation with database is performed only on OracleDataTable.Update().

mrjoltcola
Posts: 24
Joined: Tue 21 Sep 2010 07:17

Post by mrjoltcola » Wed 31 Aug 2011 14:46

Thank you.

So can I ask, does OracleTableTable even require me to open a connection before passing to the table?

My current code does:

Code: Select all

   
   connection.Open();
   var dataTable = new OracleDataTable(select, connection);
Do I need "connection.Open()" for correctness?

Shalex
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Thu 01 Sep 2011 07:14

OracleDataTable opens a connection itself. You do not need to open the connection.

Post Reply