Managing a data table

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, u开发者_开发问答pdate), 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.

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.

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.

The actual implementation will vary depending on the specific technology you are building the app in, but you probably should implement this using a paging strategy.

That is, grab some manageable number of rows at a time (10-50) and display them the user and give the user controls to move to the next/prev page of rows. At no time should you pull down the whole table, since the user is very unlikely to actually every go through all 500K rows in one sitting anyway so pulling them all is a colossal waste of resources and will likely cause major resource contention in your app.