Problems loading a DataGridView with a two dimensional arrayList

Question

Hello,

I am writing an app where I am loading two DataGridViews from a DB. I have successfully done this using a dataAdapter.Fill(DataSet), but the problem I had was that the data coming from the DB has 10s of thousands of records. It is very slow (too slow, about 7 mins) the first time it is ran. The next time it runs the time is more reasonable (about 35 secs). So I thought I would try another approach: Loading up a 2-dimensional arraylists with the data then pass that into the datagridviews. Originally I was thinking this would be a quick test to see the time gain, if any, but it has proven to be more difficult than I thought. Here is what I have:

while(dr.Read())
        {



            for (int l = 0; b && l <= dr.FieldCount-1; l++)
            {
                DataGridViewColumn column = new DataGridViewColumn();
                column.CellTemplate = cellTemplate;
                column.FillWeight = 1;
                column.Name = dr.GetName(l);
                dgv.Columns.Add(column);
            }
            b = false;

            for (int i = 0; i <= dr.FieldCount - 1; i++ )
            {
                row.Add(dr.GetValue(i));
            }

            table.Add(row);

            row = new System.Collections.ArrayList();
        }

        dgv.DataSource = table;

Note, I would create a class to build in the data (a data member for each column, but the columns could change) thus the arrayList idea. The datagridview ends up having the correct columns, with the correct names, and the correct number or rows, however the rows are all blank and it added additional columns relating to the structure of the arrayList. What am I doing wrong? Is there a better way? Any help/feedback would appreciated! :-)

Thanks,

Ben

Answer 1

The most likely reason it ran faster the second time is that the query plan became cached by the database.

Answer 2

Ok... I figure it out..sort of ;-) I ditched the Arraylist idea and just added values directly into the dgv. Here is what I did, if anyone cares :-)

...
    while(dr.Read())
    {
        for (int l = 0; b && l <= dr.FieldCount-1; l++)
        {
            DataGridViewColumn column = new DataGridViewColumn();
            column.CellTemplate = cellTemplate;
            column.FillWeight = 1;
            column.Name = dr.GetName(l);
            dgv.Columns.Add(column);
        }
        b = false;

        dgvRow = new DataGridViewRow();

        for (int i = 0; i <= dr.FieldCount - 1; i++ )
        {

            dgvCell = new DataGridViewTextBoxCell();
            dgvCell.Value = dr.GetValue(i).ToString();
            dgvRow.Cells.Add(dgvCell);
        }

        dgv.Rows.Add(dgvRow);
    }
...

Which leads me to my next question: http://stackoverflow.com/questions/3580237/best-way-to-fill-datagridview-with-large-amount-of-data