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

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?

Answer

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.

Advertisement

Answer