Progressbar for loading data to DataGridView using DataTable

Question

I have a DataGridView in which I load data from a SQL server database. When I load the data it takes quite long time.

I would like to give user information that the data is loading. May I ask you what is the best way connecting Progressbar when data is loading into the DataGridView?

I don't want anyone to make a fully working code for me. I just would like to know how it can be done.

I see someone awarded my question with bounty. I would like to say that at the moment Iam using this code which I would appriciate if it would fit.

DTGdataTable = new DataTable();
SqlDataAdapter SDA = new SqlDataAdapter
SDA.Fill(DTGdataTable);
dataGridView1.DataSource = DTGdataTable ;

Thank you everyone for your time.

Solution 1

If the problem is that it takes long to fetch the data from the database, i have a possible solution for you:

    private void buttonLoad_Click(object sender, EventArgs e)
    {
        progressBar.Visible = true;
        progressBar.Style = ProgressBarStyle.Marquee;
        System.Threading.Thread thread = 
          new System.Threading.Thread(new System.Threading.ThreadStart(loadTable));
        thread.Start();
    }

    private void loadTable()
    {
        // Load your Table...
        DataTable table = new DataTable();
        SqlDataAdapter SDA = new SqlDataAdapter();
        SDA.Fill(table);
        setDataSource(table);
    }

    internal delegate void SetDataSourceDelegate(DataTable table);
    private void setDataSource(DataTable table)
    {
        // Invoke method if required:
        if (this.InvokeRequired)
        {
            this.Invoke(new SetDataSourceDelegate(setDataSource), table);
        }
        else
        {
            dataGridView.DataSource = table;
            progressBar.Visible = false;
        }
    }

Put the method loading the data into another thread and set the datasource when it's finished. There should be an invoke required. If you want to show percentage values in the progressbar, don't use the style 'Marquee' and add another function and delegate you can invoke for setting the value of the progress bar.

If binding the data to the grid is the problem, then you can not put the binding into another thread and you may show a progress-popup that runs in another thread.

I hope this helps.

Solution 2

Try this...This should be the quickest route...

1) Add a button control.
2) Add a datagridview control.
3) Add a single column into the datagridview control.
4) Add a progressbar control.
5) Leave them all with default names.

Under the button1_Click event, add this chunk of code...

int maxnumber = 1000;
progressBar1.Value = 0;
progressBar1.Maximum = maxnumber;
for (int x = 0; x <= maxnumber - 1; x++)
{
    Application.DoEvents();
    dataGridView1.Rows.Add(new string[] {Convert.ToString(x)});
    progressBar1.Value += 1;
    label1.Text = progressBar1.Value.ToString();
}

That's it. Edit to suit your needs. This isn't the complete exact code you want but it should get you started. I've taken the liberty to declare maxnumber to hold the limit of the progressbar. In your case, this should be the row count of your database and it's subtracted by 1 since index always starts with zero :)

The method above is single threaded, which means you still can't do multitasking while your loop still isn't done yet. In some cases, depending on the algorithm of the code, using the single threaded method may be faster than going for the multithreading route. Anyhow, the second method would be using a backgroundworker. This is most commonly preferred by people since the user can still do things while the list loads. Kind of like installers where you can cancel installation even if it's in the middle of doing something. The website below should get you started. The code is in VB.NET but can be easily converted to C# :)

http://social.msdn.microsoft.com/Forums/vstudio/en-US/efd7510c-43ed-47c4-86a3-1fa350eb0d30/fill-datagridview-using-backgroundworker-progressbar?forum=vbgeneral

Solution 3

The problem is all the data load together and return back from database. You need to get the data loading progress. This can be done from database.

1. Get the count to data rows from database. (This query would return a single number, so it would be quicker).

2. Get the data in parts (Divide & conquer strategy), you can use OFFSET and FETCH database queries. (This is return part of data on each call) OFFSET and FETCH is available on different database. In MSSQL, it was introduced in version 2012.

When we are getting the data in parts from server, we can calculate the progress.

Solution 4

Within your code, this line will take up most of the processing time:

SDA.Fill(DTGdataTable);

So I think the most important thing is to keep track of progress while this is executing. To do this, you first need to know how many rows you're expecting. The SQLDataAdapter can't provide this information, so you would need to run an extra COUNT(*) query first to get this number. You can then use this as the Maximum on your ProgressBar.

My actual loading code is based upon Michael Hofmeiers solution, but instead of using the ProgressBar in Marquee mode, I would feed it with real progress data from a Timer Control. So on your form, you add a Timer control (named progressTimer in my example), set its Interval to 100 msec and Enabled to false. The code then becomes:

private DataTable table = null;

private void buttonLoad_Click(object sender, EventArgs e)
{
    // TODO: Select the row count here and assign it to progressBar.Maximum

    progressBar.Visible = true;             
    System.Threading.Thread thread = 
      new System.Threading.Thread(new System.Threading.ThreadStart(loadTable));
    thread.Start();
    progressTimer.Enabled = true;
}

private void loadTable()
{
    // Load your Table...
    this.table = new DataTable();
    SqlDataAdapter SDA = new SqlDataAdapter();
    SDA.Fill(table);
    setDataSource(table);
}

internal delegate void SetDataSourceDelegate(DataTable table);
private void setDataSource(DataTable table)
{
    // Invoke method if required:
    if (this.InvokeRequired)
    {
        this.Invoke(new SetDataSourceDelegate(setDataSource), table);
    }
    else
    {
        progressTimer.Enabled = false;
        dataGridView.DataSource = table;
        progressBar.Visible = false;
    }
}

private void progressTimer_Tick(object sender, EventArgs e)
{
    if (this.table != null)
        progressBar.Value = this.table.Rows.Count;
}

When you run this, your application will stay responsive during the loading, and you'll see the ProgressBar changing to reflect the number of rows loaded. Only at the end (when you set the DataSource on the DataGridView) will the application seem to "hang", but I don't think there's a way to avoid that. You can only do this operation from the main thread, so it's unavoidable that the UI becomes unresponsive. But in my test, the DataGridView easily handles 300K+ rows in about a second, so that shouldn't be that much of a problem.

View more solutions