How to handle large result sets in Windows Forms

A typical szenario in a database-centered application is that a user
(accidentally or on purpose) requests data that would result in a very
large result set, may be millions of rows.
I really like the way how MS Access handles that problem. The first
records are displayed as soon as they are available. When the user
scrolls down he or she experiences the display of the further records
as if they were available locally. Jumping to record number 1,000,000
also as little delay, the same applies for jumping to the end.
Is it possible to achieve the same or a similar behavior in ADO.NET
and Windows Forms? Can I still rely on data binding?
My approach would be:
- Using a background thread for the query to keep the application
responsive and give the user an opportunity to abort the action.
- Using a DataReader to fill a DataTable with the first n records.
- Data bind the DataTable.
- Handle scrolling down or jumping to a specified record by reading
forward from the DataReader into the DataTable. That can't be done
in the background thread, I suppose.
The problem is that I need to keep open the connection all the time
(or until the user scrolles to the end) which has a strong impact on
scalability.
I have a basic idea of how to handle paging in ADO.NET.
How do I support sorting in the DataGrid?
I suppose that is a frequent requirement. There should be some kind of
a design pattern - or even a framework or third-party tool that
supports such an implementation. Does anyone know more?
Thank you in advance.
Daniel FaensenHi Dan,
This is a continual issue. I looked into it extensively about 1 and a half
years ago, and I came up with certain workarounds, but nothing too great.
First, a datareader is not the way to go - it's a fire hose cursor and
doesn't make it possible to do very much very effectively, even searching.
Second, you can always use ado through interop services to bind to a
datasource CONNECTED, not disconnected, as ado .net provides using a
datatable. But you lose a lot of the benefits of ado .net, especially it's
disconnected, non-clashing nature, and it's enormous speed when data is in
memory.
But I think the best solution may be a third party grid, like C1, which I
use (many of the others are probably fine also). C1 also has a data access
class that can do what you described re MS Access, but I haven't chosen to
use it, because my users may want to filter rows while they are in the grid
and that requires gathering the full table and manipulating it, perhaps
again and again during the same session.
Even with the third party grid, I try to get my users to select data
filtered first (both re data and/or re columns to be returned), so that is
all they have in the grid at one time - they usually can do this because
they know what they're after. If they want the whole table - my largest is
about 25 cols wide, 1.5 million rows - they wait for the load - which isn't
terrible (approx 75 seconds to load).
I long ago spoke to MS about adding a connected element to ado .net, and
they said they would, but I don't believe it will happen.
HTH,
Bernie Yaeger
"Daniel Faensen" <faensen@.inf.fu-berlin.de> wrote in message
news:e9099bc4.0411050227.40853353@.posting.google.com...
>A typical szenario in a database-centered application is that a user
> (accidentally or on purpose) requests data that would result in a very
> large result set, may be millions of rows.
> I really like the way how MS Access handles that problem. The first
> records are displayed as soon as they are available. When the user
> scrolls down he or she experiences the display of the further records
> as if they were available locally. Jumping to record number 1,000,000
> also as little delay, the same applies for jumping to the end.
> Is it possible to achieve the same or a similar behavior in ADO.NET
> and Windows Forms? Can I still rely on data binding?
> My approach would be:
> - Using a background thread for the query to keep the application
> responsive and give the user an opportunity to abort the action.
> - Using a DataReader to fill a DataTable with the first n records.
> - Data bind the DataTable.
> - Handle scrolling down or jumping to a specified record by reading
> forward from the DataReader into the DataTable. That can't be done
> in the background thread, I suppose.
> The problem is that I need to keep open the connection all the time
> (or until the user scrolles to the end) which has a strong impact on
> scalability.
> I have a basic idea of how to handle paging in ADO.NET.
> How do I support sorting in the DataGrid?
> I suppose that is a frequent requirement. There should be some kind of
> a design pattern - or even a framework or third-party tool that
> supports such an implementation. Does anyone know more?
> Thank you in advance.
> Daniel Faensen