how to make the datagridview load faster with Multiple Row query - c# SQL

Question

The code:

private void suppPopulate()
        {
            byCode.Text = "Supplier Code";
            byDesc.Text = "Supplier";
            DGViewListItems.Columns.Add("custcode", "Customer Code");
            DGViewListItems.Columns.Add("customer", "Customer");

            DGViewListItems.Columns[0].ReadOnly = true;
            DGViewListItems.Columns[0].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;

            DGViewListItems.Columns[1].ReadOnly = true;
            DGViewListItems.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;

            using (SqlConnection con = db.Connect())
            {
                SqlDataReader rd;
                SqlCommand cmd = new SqlCommand("SELECT DISTINCT custcode, custname FROM Customers WHERE type = 'V';", db.Connect());
                rd = cmd.ExecuteReader();
                int i = 0;
                if (rd.HasRows)
                {
                    while (rd.Read())
                    {
                        DGViewListItems.Rows.Add();
                        DGViewListItems.Rows[i].Cells["custcode"].Value = rd["custcode"].ToString().Trim();
                        DGViewListItems.Rows[i].Cells["customer"].Value = rd["custname"].ToString().Trim();
                    }
                }
                cmd.Connection.Close();
            }
        }

The SSMS output:

The output form loads slowly, the rows for the query are over 1000 so I think the cause of the slow load is the number of returned rows? If yes, how do make the load of the datagridview faster?

If the problem is a slow painting speed, turn on double buffering — Caius Jard, Jan 04 '22 at 13:01

PaulF · Accepted Answer · 2022-01-04T12:52:37.743

4

NOTE - this answer relates to the first part of the original question regarding why only a single row of the DataGrid being populated.

Incrementing the counter "i" at the bottom of the while loop looks like it may fix the problem.

You are adding rows, but only updating the first.

edited Jan 04 '22 at 12:52

answered Jan 04 '22 at 11:57

PaulF

6,673
2
18
29

2

That would definitely help. Regarding performance it would be a good idea to call `SuspendLayout` before starting the update and `ResumeLayout` after. – Palle Due Jan 04 '22 at 12:03
@PaulF ohh yeah right didnt notice the increment was not implemented. But do you have solution for the slow load of the table? – Richard Johnson Jan 04 '22 at 12:21
1

See the comment by @PalleDue. – PaulF Jan 04 '22 at 12:23
1

See also this answer : https://stackoverflow.com/questions/4649995/how-to-suspend-a-datagridview-while-updating-its-columns - there is a link to MSDN about improving performance – PaulF Jan 04 '22 at 12:48
2

And this is why we just more simply load a datatable and bind it to the grid – Caius Jard Jan 04 '22 at 13:27
1

The datatable works fine, thanks guys. – Richard Johnson Jan 06 '22 at 04:15

how to make the datagridview load faster with Multiple Row query - c# SQL

1 Answers1