I need to write software that manages data from an SQL database.
Tables can be huge (> 500,000 rows).
I have to control the display through the table, which should be used to display the following lines, above the lines, moving to the beginning and to the end.
ADO with its Recordset can easily deal with the MoveNext, MoveFirst, MovePrevious and MoveLast methods.
The problem is that I find it a little slow. Also, for recording (insert, delete, update) I already use ADO.NET.
In ADO.NET, we cannot handle a real cursor. The only available (via DataReader) is the forward cursor.
I cannot load the dataset because it takes considerable time and memory.
My question is: Does anyone know a solution to handle this table using ADO.NET?
This is a desktop application, i.e. ERP.
Edit:
I tried to implement a paging system, it works very well. However, the results of ERP applications are never sorted by id, I understand that using a search engine, the results should be sorted by identifier.
So, if someone knows another method or how to implement a swap system with this limitation, I’m all ears.