Click here to Skip to main content
Click here to Skip to main content
Go to top

Paging Data with DataGridView in VirtualMode

, 12 Mar 2008
Rate this:
Please Sign up or sign in to vote.
This article shows an example of paged data retrieval and the use of DataGridView control in VirtualMode.

Introduction

This article illustrates an example of paged data retrieval and displaying a large amount of data on the DataGridView control in VirtualMode.

Background

Displaying millions of rows of data on a Form consumes lots of memory and can easily make the UI unresponsive. In order for the UI to display large amounts of data and remain responsive, the UI should only load and display data that it needs to show. Paging data makes use of memory more efficiently since additional data is only loaded when needed.

There are other things that must be considered to improve the performance of the DataGridView control. This article does not cover optimization. For more information on optimization, I recommend reading the MSDN article - “Performance Tuning in the Windows Forms DataGridView Control.”

For this article, I will present an example of a Form with only one DataGridView loaded with over a million rows of names. The database script for creating the table and stored procedure is included in the zipped solution. You need to load data to the tables.

Using the Code

There are two parts in the example:

  1. The use of DataGridView VirtualMode, and
  2. Paged data retrieval

How to Use DataGridView VirtualMode

There are three main things that must be done to use DataGridView in VirtualMode:

  1. Set the VirtualMode property to true
  2. Attach an event handler for DataGridView's CellValueNeeded event, and
  3. Set the DataGridView's RowCount property
public partial class Form1 : Form
{
    const int PAGE_SIZE = 5000;

    NameListCache _cache = null;

    public Form1()
    {
        InitializeComponent();

        _cache = new NameListCache( PAGE_SIZE );

        dataGridView1.CellValueNeeded +=
            new DataGridViewCellValueEventHandler( dataGridView1_CellValueNeeded );

        dataGridView1.VirtualMode = true;

        dataGridView1.RowCount = (int)_cache.TotalCount;
    }

    private void dataGridView1_CellValueNeeded
        ( object sender, DataGridViewCellValueEventArgs e )
    {
        _cache.LoadPage( e.RowIndex );

        int rowIndex = e.RowIndex % _cache.PageSize;

        e.Value = _cache.CachedData[rowIndex][e.ColumnIndex];
    }
} 

On the code above, the VirtualMode property of dataGridView1 is set to true and an event handler for CellValueNeeded event is added. To get the row count, the NameListCache instance TotalCount is assigned to dataGridView1.RowCount. The code for NameListCache is explained in the Paged Data Retrieval section.

In Form1's constructor, RowCount is set to the total number of rows. The DataGridView needs to know the total number of rows ahead of time even before all rows are retrieved. If the data retrieval is set to get 5000 rows per page and the total number of rows is 100,000 rows, the DataGridView must already know that it is supposed to display 100,000 rows even when only the first 5000 rows are loaded. The DataGridView control uses the total number of rows in order to know how to set the vertical scrollbar grip.

The DataGridView control requests only data that it needs to display when VirtualMode is set to true. The DataGridView control raises the CellValueNeeded event every time cells are loaded for display. Keep in mind that this happens for each cell. If the table displayed has 5 columns, the CellValueNeeded event is raised 5 times for every row added to the DataGridView. In the CellValueNeeded event handler, the cell requesting for its content is identified using DataGridViewCellValueEventArgs' RowIndex and ColumnIndex properties. The cell's value is assigned to the Value property of DataGridViewCellValueEventArg. The example above shows how a cell's value is taken from _cache.CachedData (the CachedData property refers to an instance of a DataTable). RowIndex and ColumnIndex are used as indexers on CachedData to get the value that the requesting cell must display.

Paged Data Retrieval

Paged data retrieval requires only part of the result set returned. The UI must be able to make multiple calls and specify which part of the total result set is currently requested. Typically, UIs also need to know the total number of rows at the beginning of the sequence of requests.

For this example, I wrote a stored procedure that returns part of the list of names retrieved and an output parameter for the total row count.

create proc [dbo].[GetNames]
    @lastRow bigint,
    @pageSize bigint,
    @totalRowCount bigint output
as
begin

select @totalRowCount = count(*) from _firstNames, _lastNames

select
    FirstName,
    LastName,
    RowNumber
from
(
    select
        fn.[FirstName] as FirstName,
        ln.[Name] as LastName,
        row_number() over( order by FirstName ) as RowNumber
    from
        _firstNames fn, _lastNames ln
) as data
where
    RowNumber between ( @lastRow + 1 ) and ( @lastRow + @pageSize )

end 

The use of a cross-join of first names and last names shown above is for illustration purposes only. This is so I can get a large amount of data for the DataGridView. The stored procedure takes the row number of the last row retrieved and the page size. The main point that must be taken from the stored procedure code shown is that we can call the procedure and retrieve only part of the query result and that the stored procedure's output parameter tells us how many total rows the query returns.

I created a typed-dataset called NameList with a DataAdapter for data access. The NameListCache class encapsulates the paging and caching code.

public class NameListCache
{
    public int PageSize = 5000;
    public long TotalCount;
    public NameList.GetNamesDataTable CachedData = null;

    NameListTableAdapters.GetNamesTableAdapter _adapter =
        new DataGridViewVirtualModePaging.NameListTableAdapters.GetNamesTableAdapter();
    int _lastRowIndex = -1;

    public NameListCache( int pageSize )
    {
        PageSize = pageSize;
        LoadPage( 0 );
    }

    public void LoadPage( int rowIndex )
    {
        int lastRowIndex = rowIndex - ( rowIndex % PageSize );
        if( lastRowIndex == _lastRowIndex ) return;
        _lastRowIndex = lastRowIndex;
        if( CachedData == null ) CachedData = new NameList.GetNamesDataTable();
        CachedData.Rows.Clear();
        long? totalCount = 0;
        _adapter.Fill( CachedData, _lastRowIndex, PageSize, ref totalCount );
        TotalCount = totalCount.Value;
    }
} 

The NameListCache exposes a LoadPage method that takes a row index. The UI can call this method and pass it an integer value of the requested data row's index. The LoadPage method determines if the requested data is already in memory by checking if the lastRowIndex is the same as the _lastRowIndex. The _lastRowIndex member stores the value of the lastRowIndex of the previous request. If it is determined that the previous lastRowIndex is the same as the current lastRowIndex, this means the requested data is currently in CachedData and the function returns without retrieving data from the data source. CachedData stores a reference to the DataTable retrieved from the DataAdapter. Since CachedData is a DataTable, the UI can access CachedData to get cell values for display by using a row and column index.

Points of Interest

I observed (using Windows Task Manager) how much memory the DataGridView consumes with different number of page sizes. When I set the page size between a number as little as 50 and up to 5,000 rows, the application uses about 40 MB. In my case the total number of rows is over 1 million. With 5,000 rows page size, start-up is very fast and there is no noticeable delay when scrolling. I can immediately scroll to the bottom of the list and not see any indication of delay. At 50,000 rows page size, the application uses about 72 MB and performance is not much different compared to having only a 5,000 row page size. At 500,000, application start-up takes over 20 seconds and when moving pages the UI becomes unresponsive for over 20 seconds too. Memory used for 500,000 page size is about 172 MB. When I just load 1 million rows in the grid and not do any paging, it takes more than a minute for the UI to load and the application consumes over 300 MB of memory.

Other References

History

  • Feb 27, 2008 - Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Roy Calvo
Software Developer
Philippines Philippines
No Biography provided

Comments and Discussions

 
GeneralScroll Problem Pinmember545454ffefe4-Dec-09 11:32 
GeneralRe: Scroll Problem PinmemberRoy Calvo4-Dec-09 14:39 
GeneralVisual Basic Net Pinmemberhaherrera27-Aug-09 18:31 
GeneralRe: Visual Basic Net PinmemberRoy Calvo1-Sep-09 5:05 
QuestionThis concept does not work if the sql statement does not have Order By clause... PinmemberMember 33281366-Apr-09 3:54 
AnswerRe: This concept does not work if the sql statement does not have Order By clause... PinmemberRoy Calvo6-Apr-09 7:28 
QuestionRe: This concept does not work if the sql statement does not have Order By clause... PinmemberViji Raj19-Apr-09 18:57 
GeneralError PinmemberMember 33281363-Apr-09 10:27 
GeneralRe: Error PinmemberRoy Calvo4-Apr-09 23:52 
Generaltakes too much time when retrieving data from the network PinmemberMember 33281363-Apr-09 10:07 
GeneralRe: takes too much time when retrieving data from the network PinmemberRoy Calvo4-Apr-09 23:59 
GeneralArithmetic overflow error converting expression to data type int Pinmembercl0306_25-Jun-08 5:57 
GeneralRe: Arithmetic overflow error converting expression to data type int PinmemberRoy Calvo29-Jun-08 17:41 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140916.1 | Last Updated 12 Mar 2008
Article Copyright 2008 by Roy Calvo
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid