Best way to fill DataGridView with large amount of data

Question

I have a windows form that has two DataGridViews (DGVs) that will hold 25,000+ records and 21 columns each. I have successfully loaded each with the data from the DB using a DataAdapter and then I tried simply filling the DGVs using for loops. Each method took roughly the same amount of time. The first time the data is filled into the DGVs it takes too long (7+ mins), and then the subsequent times the time is much more reasonable (~30 secs). So my question is, what is the best way to load a DGV with a large amount of data that will take on average <= 1 min? I really like the functionality of DGVs, but if push comes to shove I am willing to use a different technology, even if it means giving up some of that functionality.

Answer 1

There are basically 3 ways to display data in a DataGridView

• Create the rows manually in a loop, as you are currently doing: as you have noticed, it's very inefficient if you have a lot of data

• Use the DataGridView's virtual mode, as suggested by Jonathan in his comment: the DGV only creates as many rows as can be displayed, and dynamically changes their contents when the user scrolls. You need to handle the CellValueNeeded event to provide the required data to the DGV

• Use databinding: that's by far the easiest way. You just fill a DataTable with the data from the database using a DbDataAdapter, and you assign this DataTable to the DGV's DataSource property. The DGV can automatically create the columns (AutoGenerateColumns = true), or you can create them manually (you must set the DataPropertyName of the column to the name of the field you want to display). In databound mode, the DGV works like in virtual mode except that it takes care of fetching the data from the datasource, so you don't have anything to do. It's very efficient even for a large number of rows

Answer 2

If you have a huge amount of rows, like 10 000 and more,

to avoid performance leak - do the following before data binding:

dataGridView1.RowHeadersWidthSizeMode = DataGridViewRowHeadersWidthSizeMode.EnableResizing; 
//or even better .DisableResizing. 
//Most time consumption enum is DataGridViewRowHeadersWidthSizeMode.AutoSizeToAllHeaders
dataGridView1.RowHeadersVisible = false; // set it to false if not needed

after data binding you may enable it.

Answer 3

I think you can use DataReader method instead of DataAdapter. DataReader is very efficient oneway component, because it's only reading data from the source, and you can fill a data table with looping.

Answer 4

Try to use a DataTable. Fill it. Then use a DataView. Assign it to the DataGridView DataSource.

//DataView dataView = new DataView(dataTable);
//this.Grid.DataSource = dataView;

You will get very SMALL response times for big files (25000 records and 21 columns in a sec). My template program took 7 sec to load 100 000 Rows * 100 Columns {with stupid contents -> row number as string}

Answer 5

this solved my problem:

array<DataGridViewRow^>
    ^theRows = nullptr;
if (DG->Rows->Count == 0)//First Compilation
{
    int NUMROWS = xxx;
    theRows = gcnew array<DataGridViewRow^>(NUMROWS);
    for (int nr = 0; nr < DRH->Count; nr++)
        theRows[nr] = gcnew DataGridViewRow();
//Do not remove the two following
    DG->Rows->AddRange(theRows);
    DG->Rows->Clear();
}
else //Update
{

    theRows = gcnew array<DataGridViewRow^>(DG->Rows->Count);
    DG->Rows->CopyTo(theRows, 0);
    DG->Rows->Clear();

}
for(int nr=0;nr<theRows->Length;nr++)
{
    theRows [nr]->SetValues("val1", "val2");
}
DG->Rows->AddRange(theRows);

Answer 6

I'm not sure this is quite what you're asking, but I like to create a subset of data to intitially load, and then include search functionality. This is very easy to do using visual studio 15 and DataSources / data sets. In solution explorer, open your dataset.xsd file. It will be named DataSet.xsd Go to the Data Table in question. Right-click, and add a query. One thing I commonly do is just add "TOP 1000" to my query. So, select * from mytable becomes select TOP 1000 * from mytable

Finally, double-click on your form to find your _load method, and alter the "Fill" to use your new query. This might be best demonstrated with an example:

The first line of code that I commented out is what Vis Stud created by default. The second is the one I added, which will get only the top 1000 records.

        private void Form_Customers_Load(object sender, EventArgs e)
    {
        // TODO: This line of code loads data into the 'stage2DataSet.customers' table. You can move, or remove it, as needed.
        /* this.customersTableAdapter.Fill(this.stage2DataSet.customers); */
        this.customersTableAdapter.FillBy_Top_1000(this.stage2DataSet.customers);


    }