Progress bar taking 20 seconds to start when it should start right away after selecting a value of a combobox in C# WindowsForms

Question

I am having an issue with my program. my program will let the user select a dropdown value and after they select the dropdown value the datagridview should load with the data from a table, and then the progress bar should start the percentage 1 to 100%. Now everything in my program works the datagridview loads correctly and everything else in the program. the progress bar also works and loads but the issue comes when the user selects the dropdow combobox the progress bar takes like 15 to 20 seconds to start. I would like it to right away.

Can you see my code and see what could be the issue to why the progress bar is not starting right away?

if you need more information please let me know.

namespace DatagridViewProgressBar
{
    public partial class Form1 : Form
    {

        //datagridview, bindingsource, data_apapter global objects variables
        private DataGridView dataGridView = new DataGridView();
        private BindingSource bindingSource = new BindingSource();
        private SqlDataAdapter dataAdapter = new SqlDataAdapter();
        DataTable dt = new DataTable();


        //class objects
        Databases lemars = new Databases();
        Databases schuyler = new Databases();
        Databases detroitlakeskc = new Databases();


        public Form1()
        {
            InitializeComponent();
            // To report progress from the background worker we set this property
            dbWorker = new BackgroundWorker();
            dbWorker.DoWork += new DoWorkEventHandler(dbWorker_DoWork);
            dbWorker.ProgressChanged += new ProgressChangedEventHandler(dbWorker_ProgressChanged);
            dbWorker.RunWorkerCompleted += new RunWorkerCompletedEventHandler(dbWorker_RunWorkerCompleted);
            dbWorker.WorkerReportsProgress = true;
            dbWorker.WorkerSupportsCancellation = true;

        }

        private void btn_Exit_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void comboBox_Database_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (comboBox_Database.SelectedItem.ToString() == "LeMars21St")
            {

                if (dbWorker.IsBusy != true)
                {
                    dbWorker.RunWorkerAsync();
                }            
            }
        }



        private void GetTableToDataGridView()
        {
            //prgBar_DataGridViewLoading
            DatabaseColumns Obj = new DatabaseColumns();
            String SqlcmdString = @"SELECT invoice, shipment, Project, invoiceDateTB, CreatedDate, typeName, exportedDate, statusName, total, import_status, Time_Completed, ERROR_DESCRIPTION FROM dbo.AllInvoicesInReadyStatus";
            SqlDataReader reader;
            int progress;

            using (SqlConnection conn = new SqlConnection(lemars._LeMarsConnectionString))
            {
                reader = null;
                SqlCommand Sqlcmd = new SqlCommand(SqlcmdString, conn);
                conn.Open();
                reader = Sqlcmd.ExecuteReader();
                if (reader.HasRows)
                {
                    try
                    {

                        dt.Load(reader);

                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            Obj.Invoice = dt.Rows[i]["invoice"].ToString();
                            Obj.Shipment = dt.Rows[i]["shipment"].ToString();
                            Obj.Project = dt.Rows[i]["Project"].ToString();
                            Obj.InvoiceDateTB = Convert.ToDateTime(dt.Rows[i]["invoiceDateTB"]);
                            Obj.CreatedDate = Convert.ToDateTime(dt.Rows[i]["CreatedDate"]);
                            Obj.TypeName = dt.Rows[i]["typeName"].ToString();
                            Obj.ExportedDate = Convert.ToDateTime(dt.Rows[i]["exportedDate"]);
                            Obj.StatusName = dt.Rows[i]["statusName"].ToString();
                            Obj.Total = Convert.ToDecimal(dt.Rows[i]["total"]);
                            Obj.ImportStatus = dt.Rows[i]["import_status"].ToString();
                            if (!Convert.IsDBNull(dt.Rows[i]["Time_Completed"]))
                            {
                                Obj.TimeCompleted = Convert.ToDateTime(dt.Rows[i]["Time_Completed"]);
                            }
                            Obj.ErrorDescription = dt.Rows[i]["ERROR_DESCRIPTION"].ToString();

                            progress = i * 100 / dt.Rows.Count; 
                            dbWorker.ReportProgress(progress);
                            Thread.Sleep(500);
                        }                     
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
        }

        private void dbWorker_DoWork(object sender, DoWorkEventArgs e)
        {
            GetTableToDataGridView();
            dbWorker.ReportProgress(100);
        }

        private void dbWorker_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            progressBar_GetTasks.Value = e.ProgressPercentage;
            // eg: Set your label text to the current value of the progress bar
            lbl_PercentageCount.Text = (progressBar_GetTasks.Value.ToString() + "%");         
        }

        private void dbWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {

            dataGridView_ShowAllData.DataSource = dt;

            if (e.Cancelled)
            {
                 MessageBox.Show("Process Cancelled.");
            }
            else if (e.Error != null)
            {
                MessageBox.Show("Error occurred: " + e.Error.Message);
            }
            else
            {
                MessageBox.Show("Successful Completion.");
            }

            //progressBar_GetTasks.Value = 0;
        }

        private void btn_CancelOperation_Click(object sender, EventArgs e)
        {
            if (dbWorker.IsBusy)
            {
                dbWorker.CancelAsync();
            }
        }
    }
}

Answer 1

Doing dt.Load(reader); waits for the data to fully load before continuing, Get rid of that line and replace it with a while(reader.Read()) loop.

    private void GetTableToDataGridView()
    {
        //prgBar_DataGridViewLoading
        DatabaseColumns Obj = new DatabaseColumns();
        String SqlcmdString = @"SELECT invoice, shipment, Project, invoiceDateTB, CreatedDate, typeName, exportedDate, statusName, total, import_status, Time_Completed, ERROR_DESCRIPTION FROM dbo.AllInvoicesInReadyStatus";
        String CountcmdString = @"SELECT count(*) FROM dbo.AllInvoicesInReadyStatus";
        SqlDataReader reader;
        int progress;
        int total;

        using (SqlConnection conn = new SqlConnection(lemars._LeMarsConnectionString))
        {
            reader = null;
            SqlCommand Sqlcmd = new SqlCommand(CountcmdString , conn);
            conn.Open();
            total = (int)Sqlcmd.ExecuteScalar(); //Get the total count.
            Sqlcmd.CommandText = SqlcmdString;
            using(reader = Sqlcmd.ExecuteReader()) //this should be in a using statement
            {
                while(reader.Read())
                {
                    object[] row = new object[reader.VisibleFieldCount];
                    reader.GetValues(row);
                    LoadSingleRowInToTable(dt, row); //I leave this to you to write.

                    //You can just read directly from the reader.
                    Obj.Invoice = reader["invoice"].ToString();
                    Obj.Shipment = reader["shipment"].ToString();
                    Obj.Project = reader["Project"].ToString();
                    Obj.InvoiceDateTB = Convert.ToDateTime(reader["invoiceDateTB"]);
                    Obj.CreatedDate = Convert.ToDateTime(reader["CreatedDate"]);
                    Obj.TypeName = reader["typeName"].ToString();
                    Obj.ExportedDate = Convert.ToDateTime(reader["exportedDate"]);
                    Obj.StatusName = reader["statusName"].ToString();
                    Obj.Total = Convert.ToDecimal(reader["total"]);
                    Obj.ImportStatus = reader["import_status"].ToString();
                    if (!Convert.IsDBNull(reader["Time_Completed"]))
                    {
                        Obj.TimeCompleted = Convert.ToDateTime(reader["Time_Completed"]);
                    }
                    Obj.ErrorDescription = reader["ERROR_DESCRIPTION"].ToString();

                    //Only call report progress when the progress value changes.
                    var newProgress = i * 100 / total;
                    if(progress != newProgress)
                    {
                        progress = newProgress;
                        dbWorker.ReportProgress(progress);
                    }
                    //Thread.Sleep(500); 
                }
            }
        }
    }

UPDATE: Here is a example based on Steve's deleted answer that shows a better solution without using a DataTable.

private void dbWorker_DoWork(object sender, DoWorkEventArgs e)
{
    List<DatabaseColumns> data = GetTableToList();
    if (data == null) //data will be null if we canceled.
    {
        e.Cancel = true;
    }
    else
    {
        e.Result = data;
    }
    dbWorker.ReportProgress(100);
}
private void dbWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
{

    if (e.Cancelled)
    {
        MessageBox.Show("Process Cancelled.");
    }
    else if (e.Error != null)
    {
        MessageBox.Show("Error occurred: " + e.Error.Message);
    }
    else
    {
        dataGridView_ShowAllData.DataSource = e.Result; //use the result from thebackground worker, only use if not canceled or errored.
        MessageBox.Show("Successful Completion.");
    }

    //progressBar_GetTasks.Value = 0;
}
private List<DatabaseColumns> GetTableToList()
{
    List<DatabaseColumns> data = new List<DatabaseColumns>();
    //prgBar_DataGridViewLoading
    String SqlcmdString = @"SELECT invoice, shipment, Project, invoiceDateTB, CreatedDate, typeName, exportedDate, statusName, total, import_status, Time_Completed, ERROR_DESCRIPTION FROM dbo.AllInvoicesInReadyStatus";
    String CountcmdString = @"SELECT count(*) FROM dbo.AllInvoicesInReadyStatus";

    using (SqlConnection conn = new SqlConnection(lemars._LeMarsConnectionString))
    {
        SqlCommand Sqlcmd = new SqlCommand(CountcmdString, conn);
        conn.Open();
        var total = (int)Sqlcmd.ExecuteScalar();
        Sqlcmd.CommandText = SqlcmdString;
        int i = 0;
        int progress = 0;
        using (SqlDataReader reader = Sqlcmd.ExecuteReader()) //this should be in a using statement
        {
            while (reader.Read())
            {
                if (dbWorker.CancellationPending)
                {
                    //Exit early if operation was canceled.
                    return null;
                }
                DatabaseColumns Obj = new DatabaseColumns();

                //You can just read directly from the reader.
                Obj.Invoice = reader["invoice"].ToString();
                Obj.Shipment = reader["shipment"].ToString();
                Obj.Project = reader["Project"].ToString();
                Obj.InvoiceDateTB = Convert.ToDateTime(reader["invoiceDateTB"]);
                Obj.CreatedDate = Convert.ToDateTime(reader["CreatedDate"]);
                Obj.TypeName = reader["typeName"].ToString();
                Obj.ExportedDate = Convert.ToDateTime(reader["exportedDate"]);
                Obj.StatusName = reader["statusName"].ToString();
                Obj.Total = Convert.ToDecimal(reader["total"]);
                Obj.ImportStatus = reader["import_status"].ToString();
                if (!Convert.IsDBNull(reader["Time_Completed"]))
                {
                    Obj.TimeCompleted = Convert.ToDateTime(reader["Time_Completed"]);
                }
                Obj.ErrorDescription = reader["ERROR_DESCRIPTION"].ToString();

                //Add the object to the list.
                data.Add(Obj);

                //Only call report progress when the progress value changes.
                var newProgress = i * 100 / total;
                if (progress != newProgress)
                {
                    progress = newProgress;
                    dbWorker.ReportProgress(progress);
                }
                i++;
            }
        }
    }
    return data;
}

Answer 2

Something I had when doing my first backgroundWorker, was the GUI locking up. I made the mistake of trying to hand out every result (primnumbers in a range from 2 to selected bound) as it was found via report progress. If I had a long enough operation (it did not happen if the operation was short) I ended up overloading and locking up the GUI with write operations, making it appear I never actually added multithreading.

Now I noticed this line:

progress = i * 100 / dt.Rows.Count; 

You run the progress code (and update the bar) every loop itteration, even if the percentage did not actually change. If you process 1 million itterations, that is 10000 redraws without the value actually changing. This might slow down later as hits become rarer or the GC starts interferring with optimal performance of the task so the GUI thread has "time to catch up".

You should check if the value actually changed, before writing it to the GUI. Latest in the ProgressReporting Event. But it might be possible to do something back in the loop itself.

I made some example code to showcase what I call the "GUI write overhead" problem:

using System;
using System.Windows.Forms;

namespace UIWriteOverhead
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        int[] getNumbers(int upperLimit)
        {
            int[] ReturnValue = new int[upperLimit];

            for (int i = 0; i < ReturnValue.Length; i++)
                ReturnValue[i] = i;

            return ReturnValue;
        }

        void printWithBuffer(int[] Values)
        {
            textBox1.Text = "";
            string buffer = "";

            foreach (int Number in Values)
                buffer += Number.ToString() + Environment.NewLine;
            textBox1.Text = buffer;
        }

        void printDirectly(int[] Values){
            textBox1.Text = "";

            foreach (int Number in Values)
                textBox1.Text += Number.ToString() + Environment.NewLine;
        }

        private void btnPrintBuffer_Click(object sender, EventArgs e)
        {
            MessageBox.Show("Generating Numbers");
            int[] temp = getNumbers(10000);
            MessageBox.Show("Printing with buffer");
            printWithBuffer(temp);
            MessageBox.Show("Printing done");
        }

        private void btnPrintDirect_Click(object sender, EventArgs e)
        {
            MessageBox.Show("Generating Numbers");
            int[] temp = getNumbers(1000);
            MessageBox.Show("Printing directly");
            printDirectly(temp);
            MessageBox.Show("Printing done");
        }
    }
}