How to display a large number of rows in a DataGrid

Question

I've got a WPF DataGrid which I am populating from a DataTable. Up till now, I never had more than 200 or so records, so the DataGrid was populated flawlessly. However, I must now add support for larger databases. Thus, I thought of showing the user 200 records, and then allowing him to press a button of some sort, to display the next 200 and so on. This is so that I would load the DataGrid faster.

What would be the best approach for this? I have some experience with paging in ASP.NET, but I have never had a requirement like this in WPF.

This is my DataGrid code:

<DataGrid Name="dgResults" 
                  IsReadOnly="True"
                  AutoGenerateColumns="True"
                  AllowDrop="False"
                  CanUserAddRows="False"
                  CanUserDeleteRows="False"
                  CanUserReorderColumns="False"
                  CanUserResizeColumns="False"
                  CanUserResizeRows="False"
                  CanUserSortColumns="False"
                  Margin="15,10,10,10"
                  Visibility="Collapsed" 
                  ItemsSource="{Binding}"/>

Binding:

dgResults.DataContext = dtResults.AsDataView();

Please check [this question][1], there are several answers which can help you. [1]: http://stackoverflow.com/questions/14793759/paged-data-in-a-wpf-grid-control/14987256#14987256 — Woodman, Feb 23 '13 at 16:43
@Woodman - I've taken a look at that, however I'm not sure if paging is the solution I'm after. I just mentioned it as i have experience in ASP.NET. I would prefer a simple "Show next 200 results" button, or something similar. — Dot NET, Feb 23 '13 at 16:45
"a simple "Show next 200 results"" actually means a "paging". so user works with "pages of data". You can google for this keyword for a set of possible solutions. My answer in mentioned topic covers more advanced scenario - Data virtualization, which means that data gets loaded on background seamlessly. — Woodman, Feb 23 '13 at 16:50
@Woodman - So what you're saying is that this can be accomplished through an SQL keyword? — Dot NET, Feb 23 '13 at 16:51
No, surely that wouldn't be enough. Original WPF DataGrid doesn't support paging, so I'm afraid you'll have to add this feature to it manually in some way. — Woodman, Feb 23 '13 at 16:55
I see. Is there possibly another GridView-type control which supports this in WPF? I don't consider myself proficient enough in WPF to accomplish this — Dot NET, Feb 23 '13 at 16:57
You see, this task cannot be solved only in control. Appropriate solution heavily depends on how you retrieve your data, because there can be some many ways, starting with direct connection to database, and up to separate services running on some dedicated server. So answering to your question - I haven't heard about any ready-to-be-used out-of-the-box universal control with such feature available in framework... — Woodman, Feb 23 '13 at 18:36

score 1 · Answer 1 · answered Feb 24 '13 at 09:52

I think you need to solve the problem in your data access layer by implementing paging
something like this using LinqToSql

var query = yourPersistenceManager.Context<YourTable>();
-- apply filters
query = query.Skip(pageNumber * pageSize).Take(pageSize);

Or If using ADO.Net you may use some general paged query like this (implementation in oracle)

select * from 
(
     select yourTable.*, rownum row_number from cc1customer 
     where  rownum <= pageSize*pageNumber 
) where row_number > (pageNumber -1) * pageSize

Similary SELECT TOP on sqlserver and ...

It is generaly recommended to use an ORDER BY to fetch consistent data between pages

And in User Interface Layer you must implement paging, manually on WPF DataGrid
Here is a useful article that may be usful

score 0 · Accepted Answer · answered Feb 23 '13 at 16:58

0

A way of side-stepping the issue is to provide the user with some search criteria with which they can look for specific results. Users, generally at least, don't page through thousands of results unless they have to. You leverage this by returning only the top X results for any given search. You can limit the amount of results your query returns by using the TSQL SELECT TOP 200 * or if you're using linq .Take(200)

answered Feb 23 '13 at 16:58

DiskJunky

4,750
3
37
66

The problem is that I've got a strict requirement behind this. It's apparently okay for users not to use this feature, but the feature must be there for the odd user who wants to swim through the results. – Dot NET Feb 23 '13 at 16:59
@DotNET then I'd still go with the search approach. If a user is looking for something specific provide them the tools to find it. That said, I know that isn't helpful if you're stuck with the requirements. An alternative that might be acceptable would be to provide the user with an "Export to CSV" button that returned all results and the user could then load it up in Excel and do whatever row mining they wanted while still reducing the load on the server. As Woodman said, proper, full-on paging is not a trivial task and would require significant development effort on your part – DiskJunky Feb 23 '13 at 17:05
Thanks for the input. Incidentally I already have an "Export to CSV" button. I plan on adding this functionality aswell soon. – Dot NET Feb 23 '13 at 17:10

How to display a large number of rows in a DataGrid

2 Answers2

Linked