Bind large table to grid in UI

[SteveD]

In a VB6 WinForms application we used a ComponentOne grid that was bound via
DAO or ADO to a SQL Server table. Via the grid the user can do all the
standard insert/delete rows and modify the data in a row. It was not a
significant problem if the table contained LOTS (e.g. millions) of rows
because the grid handled cursor management via the always-connected DAO or
ADO recordset. Life was good and relatively simple.

Now we want to accomplish the same thing using ADO.NET in a .NET WinForms
application and things don't look so straightforward. ADO.NET uses the
disconnected model. So what is the grid going to be bound to in .NET? It
doesn't seem like a good idea to read all the data from the table (e.g.
millions of rows) into a DataTable (or DataSet), bind the grid to the
DataTable, etc.

I would think that a good solution would be some sort of "automatic paging"
object that sits between the grid and the table. This "automatic paging"
object implements the proper interface so that the grid can bind to it. But
the object would handle connecting to the database, presenting the proper
rows of data to the grid, and updating data in the table as the user makes
changes via the grid. Does such an object exist?

Question: What are the "best practices" for solving this problem under
ADO.NET? Please don't suggest that we re-think the feature and do away with
the grid - we are certain that our users REALLY need to edit potentially
large data tables via a grid. We are not locked into the ComponentOne
FlexGrid, we can use something else if we need to.

 

[Kerry Moorman]

SteveD,

Here is some info on using the datagridview control's virtual mode with
paging:

http://www.codeproject.com/KB/database/WinFormGridviewCaching.aspx

http://msdn.microsoft.com/en-us/library/ms171624.aspx

Kerry Moorman

 

[Cor Ligthert[MVP]]

SteveD,

First of all, there is no Best Practise, as it was like that, you would only
get the Best Practise, however .Net is very extended and therefore there are
many best practises based on the problem. Although a grid with thousands of
rows is commenly called bad practise.

However, there is nobody who forbits you to do that and to catch if a row is
changed and then do an update of the changed row row.

You can even after every change of a row do get changes and then do an
update of that row.

The DataAdapter.Update has simple the possibility of that.

Do not forget in this case to use after the correct update the
dataset.acceptchanges, because that is the situation where it is one of the
situations it is designed for.

Cor

 

[Yuancai \(Charlie\) Ye]

http://www.udaparts.com/devguide.htm