Load a billion rows in a DataGridView

Download source - 739 KB

Introduction

This article presents how to load rows in a DataGridView without using its RowCount.

DataGridView's VirtualMode mechanism doesn't deliver what it promises. Whenever you set the RowCount, the DataGridView will create row instances based on it; row object instantiation time can be aggravating when you set it to hundred thousands or millions.

What prompted me to formulate this code is I created my own lookup box, and I sort of wanted to future-proof it, i.e., the program should scale even if you have tens or hundred thousands of rows to look up. On initial code, I used DataGridView's VirtualMode, but there's a problem when the data source is of hundred thousand rows, the same problem shared by another coder from this article.

Using the Code

For clearer delineation between the UI and data access, I put them on separate regions. The query result is assigned on a module-wide visible _ds (DataSet) object.

_ds = Material_LookupList(Filter, 0);

To show the result's columns:

SetGridDisplay();

To allocate screen-visible rows:

AdjustRowCount();

Finally, to display the result's values:

DisplayValues();

The central event in our program scrolling mechanism (vsb is an instance of VScrollBar):

private void vsb_ValueChanged(object sender, EventArgs e)
{
    DisplayValues();
}

This is how we setup the DataGrid's columns:

void SetGridDisplay()
{
    var ftd = new List<string>();

    grd.Columns.Clear();    
    if (this.FieldsToDisplay != null && this.FieldsToDisplay.Length > 0)
    {
        ftd.AddRange(this.FieldsToDisplay);
    }
    else
    {
        foreach (DataColumn c in _ds.Tables["_lookup_result"].Columns)
        {
            if (c.ColumnName == "_lookup_row_number") continue;
            ftd.Add(c.ColumnName);
        }
    }

    foreach (var s in ftd)
    {
        if (s == this.ValueMember && !ShowValueMember) continue;


        string columnName = "ux" + s.Replace(" ", "");


        foreach (DataGridViewColumn dgc in grd.Columns)
        {
            if (dgc.DataPropertyName == s)
                goto goAlreadyAdded;
        }

        grd.Columns.Add(columnName, HeaderizeColumn(s));
        var c = grd.Columns[columnName];
        c.DataPropertyName = s;
        c.Visible = true;

        goAlreadyAdded: ;
    }
}

The following code is the data scrolling mechanism (keyboard, mouse, scrollbar) of the program. The key principle is to just depend on one central logic. In our code, we just use the vertical scroll bar's Value. Accordingly, the keyboard and mouse wheel also requests the scrolling mechanism for VScrollbar's Value.

void DisplayValues()
{
    for (int r = 0; r < grd.Rows.Count; ++r)
    {
        int absRow = r + vsb.Value;
        DataRow[] drx = _ds.Tables["_lookup_result"].Select(
            string.Format("_lookup_row_number = {0}", absRow));
        
        if (drx.Length == 0)
        {
            _ds = Material_LookupList(Filter, absRow);
            drx = _ds.Tables["_lookup_result"].Select(
                string.Format("_lookup_row_number = {0}", absRow));
        }

        if (drx.Length == 1)
        {
            foreach (DataGridViewColumn c in grd.Columns)
            {
                grd.Rows[r].Cells[c.Name].Value = drx[0][c.DataPropertyName];
            }
        }
        else
        {
            foreach (DataGridViewColumn c in grd.Columns)
            {
                grd.Rows[r].Cells[c.Name].Value = null;
            }
        }

    }
}

void grd_KeyDown(object sender, KeyEventArgs e)
{
    if (grd.CurrentCell == null) return;

    if (e.KeyCode == Keys.Down)
    {
        if (grd.CurrentCell.RowIndex == grd.Rows.Count - 1)
        {
            if (vsb.Value <= vsb.Maximum - vsb.LargeChange)
            {
                e.Handled = true;
                ++vsb.Value;
            }
        }

    }
    else if (e.KeyCode == Keys.Up)
    {
        if (grd.CurrentCell.RowIndex == 0)
        {
            if (vsb.Value > 0)
            {
                e.Handled = true;
                --vsb.Value;
            }
        }

    }
    else if (e.KeyCode == Keys.PageDown)
    {
        if (grd.CurrentCell.RowIndex == grd.Rows.Count - 1)
        {
            if (!(vsb.Value + vsb.LargeChange > vsb.Maximum))
            {
                int newValue = vsb.Value + vsb.LargeChange;

                if (vsb.Maximum - newValue > vsb.LargeChange)
                {
                    vsb.Value = newValue;
                }
                else
                {
                    vsb.Value = vsb.Maximum - vsb.LargeChange + 1;
                }
            }
        }
    }
    else if (e.KeyCode == Keys.PageUp)
    {
        if (grd.CurrentCell.RowIndex == 0)
        {
            if (!(vsb.Value - vsb.LargeChange < 0))
            {
                vsb.Value -= vsb.LargeChange;
            }
            else
            {
                vsb.Value = 0;
            }
        }
    }
}

void grd_MouseWheel(object sender, MouseEventArgs e)
{
    if (e.Delta < 0)
    {
        long rowCount = (long)_ds.Tables["_lookup_count"].Rows[0]["_count"];

        if (vsb.Value < rowCount - vsb.LargeChange)
            ++vsb.Value;
    }
    else if (e.Delta > 0)
    {
        if (vsb.Value > 0)
            --vsb.Value;
    }
}

Points of Interest

Interesting/fun/annoying things while writing the code? Off-by-one error :-) Especially on Keys.Down and Keys.PageDown. This prompted me to scale back the code a little; the more optimize the code, but riddled with errors is available in another class (loftyGoalForm1). If there's a use case that the scaled back code can't handle, I will go back to the drawing board and re-visit loftyGoalForm1's logic.

Known Limitations

Two billion rows, because .NET's VScrollbar can only support 32 bit numbers. If I find a 64 bit vertical scrollbar, I will integrate that in my MycFramework's lookup control.