Increamental loading in grid using DataTable as DataSource

Question

In my window application there are many screens with grid.
And I have used DataTable as DataSource of the grid and DataTable have some really large data sets (> 50,000), which take a lot time to load data on screen if we load all at a time while loading the UI get un-responsive till all data not get loaded,
So that I have implemented incremental loading in that grid using Background Worker.
Here is the code :

// DoWork Event of the background Wroker.
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
    {
        try
        {
            while (bgstop)
            {                   
                e.Result = addNewRecord(); 
                if (Convert.ToBoolean(e.Result) == false)
                {
                    e.Cancel = true;                       
                    bgstop = false;
                    killBGWorker();
                    break;
                }
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }


    // to add/merge the records in the DataTable
    private bool addNewRecord()
    {
        int flag = 0;
        try
        {
            Thread.Sleep(500); //optional
            DataTable tableAdd = getTableData();
            if (tableAdd.Rows.Count > 0)
            {
                dtRecords.Merge(tableAdd);  // dtRecords is the DataTable which attached to grid
                flag++;
            }
            else
                backgroundWorker1.WorkerSupportsCancellation = true;
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        if (flag > 0)
            return true;
        else
            return false;
    }


    // To get the next slot of Records from the DataBase
    private DataTable getTableData()
    {
        DataTable dt = new DataTable();
        start = nextRows * noOfRows;
        stop = start + noOfRows;
        dt = SQLHelper.getAllRecords(totalRows,noOfRows, start + 1, stop);
        nextRows++;
        return dt;
    }

    // kill the backgroudworker after the all data/records get loaded from  database to grid/DataTable 
    private void killBGWorker()
    {
        backgroundWorker1.WorkerSupportsCancellation = true;
        backgroundWorker1.CancelAsync();
    }

Above code get the first defined number of records (say 200) and after that in the background worker started and start fetching the data in a slot and merge that with grid DataSource till all data (say >50,000 records) get loaded into the grid.
But still have some issue with UI interaction, UI not get hang for 2-3 seconds many time till all records from DataBase get loaded into the grid.

I gone through this but in that example DataModel was used but in my case there is no DataModel they just fetched in DataTable from DataBase and right now we can't move to DataModel.

Is there any other way to achieve incremental Loading with good UI interaction ?
OR
Is there any way to implement IBindingList in current scenario ?

Answer 1

You can achieve that by changing the DataGridView from BindingMode to VirtualMode.

The following changes will re-use as much as possible what you already have and you will see that the DataGridView gets loaded incrementally. I don't know how much records you fetch at once, but you can keep that number low.

Set the property VirtualMode to true. Remove any values from the property DataSource. Add as many Unbounded columns to your DataGridView as you have columns in your DataGrid (this could be done automatic if needed).

Add an eventhandler for CellValueNeeded.

Add the following code to that handler:

private void dataGridView1_CellValueNeeded(object sender, DataGridViewCellValueEventArgs e)
{
    e.Value = dtRecords.Rows[e.RowIndex][e.ColumnIndex];
}

On you backgroundworker1 set the property WorkerReportsProgress to True
Add an eventhandler to your backgroundworker for ProgressChanged.with the following code:

private void backgroundWorker1_ProgressChanged(object sender, ProgressChangedEventArgs e)
{
    this.dataGridView1.RowCount = (int) e.UserState;
}

In your method addNewRecord add below this line:

dtRecords.Merge(tableAdd);  // dtRecords is the DataTable which attached to grid
// added to bring the number of records to the UI thread
backgroundWorker1.ReportProgress(42, dtRecords.Rows.Count);

And with that your datagridview should now load its data incrementally. The trick really is setting the RowCount property. That signals to the datagrid if it can show a record and it adapts its scrollbar to the same.