Managing a data table

Question

I have to write a software that manipulates data from a SQL database.

The tables can be huge (> 500,000 lines).

I must manage the display through a table which should be used to display the next lines, above lines, move to the beginning and to the end.

ADO with his Recordset can do that easily with the methods MoveNext, MoveFirst, MovePrevious and MoveLast.

The trouble is that I find that a little slow. Also, for writes (insert, delete, update), I'm already using ADO.NET.

In ADO.NET, we can not handle a real cursor. The only available (through a DataReader) is forwardonly cursor.

I can not load a dataset too because it takes considerable time and memory.

My question is: Does anyone knows a solution to handle this kind of table with ADO.NET?

It is a desktop application, an ERP to be precise.

Edit:

I tried to implement the paging system, it works pretty well. However, the results of the ERP applications are never sorted by id, I understand that with the paging system, results have to be sorted by ID.

So, if anyone know another method, or how to implement the paging system with this constraint, I'm all ears.

Answer 1

Try looking at paging... It's the concept of only grabbing that you want to display.

You don't want to use a cursor, because that would require keeping a connection open between requests (generally considered a bad idea in web apps).

Here's something I came across that might help: http://www.asp.net/data-access/tutorials/efficiently-paging-through-large-amounts-of-data-cs

EDIT: Well, the cited example won't be as much help for a desktop application, but the concept of paging is still valid.

Answer 2

Using ADO.NET, you can use a DataAdapter to fill pages of data at a time, essentially just what you'd display on the screen. When you click to the end, it goes and gets exactly the data you need to show that last screen. It's very fast, and should be exactly what you need.