OracleDataTable - Asynchronously - BeginFill EndFill Usage

[Pinturiccio]

Please comment the following line in the CreateOracleDataset method:

Code: Select all

oracleDataTable.MissingSchemaAction = MissingSchemaAction.Add;

By default MissingSchemaAction equals to AddWithKey. With AddWithKey the issue should not occur again.

[lewis]

Ok, Thanks for your answer,I going to try it, and will let you know something if is the case.

[lewis]

Hi,sr I commented the code according with you told, however the same behaviour persists, I sent the link with a word file with pictures showing the results, including the same project with the modification you suggested, could yo please try it, thanks in advance:

https://1drv.ms/w/s!Ap7GLUK36zn2kX7PFGKbihd2GzO-

[Pinturiccio]

Please send us the changed project, results of which are displayed on https://1drv.ms/w/s!Ap7GLUK36zn2kX7PFGKbihd2GzO-

Please send it to the following email support at devart dot com or via contact form. If your project has a size of several megabytes or more, you can archive your project and upload it to our ftp server (ftp://ftp.devart.com, credentials: anonymous/anonymous ) or to any file exchange server so that we could download it from there. You can send us the password to the archive via our contact form.

[lewis]

Hi, this is the link for the modified project https://1drv.ms/u/s!Ap7GLUK36zn2kX9UtsrqeXxkuDjF

Thanks in advance

[Pinturiccio]

Could you please provide the DDL script of your table? Looks like there is no primary key in your table. We have reproduced the described behavior when there is no primary key in a table.

Please also note that you use the following code in the OperationComplete and btnNextAsyn_Click operations:

Code: Select all

oracleDataTable.CancelFetch();

CancelFetch cancels the asynchronous fetching, and you cannot continue fetching after this, unlike the SuspendFill method. For more information, please refer to https://www.devart.com/dotconnect/oracl ... Fetch.html

Thus, your code does the following:
1. After you click BeginFill, suppose, 110 records are read.
2. After this, OperationComplete is called, which calls CancelFetch for the DataTable. Then asynchronous fetching stops.
3. Then you click Next Asyn, and some more rows are read. Suppose, it is 100 rows. Fetching is done from scratch, because the first async operation was finished.
4. If there is no primary key in the table, these first 100 rows are added to your 110 rows again. And so on with each Next Asyn button click.
5. If the table has a primary key, these 100 rows won't be added to the table, because such PK values are already present.

We advise you to replace CancelFetch calls with SuspendFill calls, and use a table with a primary key. If there is no primary key in the table, even with SuspendFill calls, the following situation may happen: at some moment, Next Asyn will finish async fetching after reading all the table rows. But the next Next Asyn button click will restart it, and read more rows from the beginning of the table.

[lewis]

in my case, i just execute a simple select * from user_objects in this case i think i dont have a primary available, i know that "Next Asyn will finish async fetching after reading all the table rows. But the next Next Asyn button click will restart it, and read more rows from the beginning of the table" but i don't know how to avoid to user press the "Next Asyn" button again. if i change the "Next Asyn" logic calling a

int i = Fillpage(start, number of records)

https://www.devart.com/dotconnect/mysql ... lPage.html

i can do that, because Fillpage returns the number of records fectched, so if i =0 i know the finish has been reached but right now is no posible, the problem with Fillpage method is slow. How i can do that?

[Pinturiccio]

It is the designed behavior when you cancel fetch and start a new one. The new fetch is started from the beginning, and if your table does not have a primary key, it will add new records to existing one. Thus each Next Asyn button click will add records to your table regardless or record count in database table.

You need to replace CancelFetch with SuspendFill first. In this case, the Next Asyn button click will read records starting from where it stopped reading the previous time. However, if your table does not have a primary key, then, after reading all the data from it, next click on the Next Asyn button will cause reading to start from the beginning.

As a workaround, you may parse the query before the first data reading and execute a separate query, returning the number of rows that the main query must return. After this, you need to check whether the number of rows in your oracleDataTable is equal to the number of rows, returned by the query. This check must be performed each time when user clicks BeginFill or Next Asyn. If the quantity of rows is equal to the number, all the data is already read, and no further loading must be done. Otherwise, perform the same actions that you do.

the problem with Fillpage method is slow

The reason is that asynchronous reading returns control to your code immediately. It may seem that data are fetched quickly. But the data are actually fetched in another thread.

When you use the FillPage method, it doesn't return control till the specified number of rows is fetched. So, it may seem that the FillPage method is slower, but actually the speed of data fetching is the same.

[lewis]

Hi, I thought before to make this "before query", the problem is, i have tables with a millions of records and it will take long time.

[lewis]

My question is how dbForge Studio for Oracle did by using "Go to next Page" button.

[Pinturiccio]

Hi, I thought before to make this "before query", the problem is

The issue occurs when you call the BeginFill method after calling CancelFetch. If you replace CancelFetch with SuspendFill, the reading will continue starting from the place where it stopped. However, if you call the BeginFill method after all the table was already read till the end, the method initializes reading since the beginning. If your table has a primary key, that won't do any harm. But if not, the records, read again from the beginning of the table, will be added to the table. This is why we advise you to add a check whether the number of rows in the DataTable is equal to the number of the corresponding rows in the database.

My question is how dbForge Studio for Oracle did by using "Go to next Page" button.

We are using FillPage in dbForge Studio for Oracle. https://www.devart.com/dotconnect/oracl ... nt32).html

You are using properties StartRecord and MaxRecords, and thus, get a similar behavior to the FillPage method.