Implement progress bar on SQLite database read C#

Go To StackoverFlow.com

1

I am reading data from a table in an SQLite3 database into a ListView using the code below

At the moment I am limiting the records to display only 200 as displaying all of them takes way too long. (Some 30,000 records)

I would like to give an option to display all the records and if that option is chosen to be able to load every record.

As I said it takes a fair amount of time and I would like to use a progress bar to show the status of it.

I have never used a progress bar before so I don't know how they work. Would it be the case of adding a few extra lines of code to my code to allow the use of a progress bar or would I have to thread it and use a background worker?

private void cmdReadDatabase_Click(object sender, EventArgs e)
{
    listView4.Columns.Add("Row1", 50);
    listView4.Columns.Add("Row2", 50);
    listView4.Columns.Add("Row3", 50);
    listView4.Columns.Add("Row4", 50);
    listView4.Columns.Add("Row5", 50);
    listView4.Columns.Add("Row6", 50);
    listView4.Columns.Add("Row7", 50);

    try
    {
        string connectionPath = Path.Combine(@"C:\", @"temp\");

        SQLiteConnectionStringBuilder csb = new SQLiteConnectionStringBuilder();
        csb.DataSource = Path.Combine(connectionPath, "database.db");

        SQLiteConnection connection = new SQLiteConnection(csb.ConnectionString);
        connection.Open();

        // Query to read the data
        SQLiteCommand command = connection.CreateCommand();
        string query = "SELECT * FROM table_name LIMIT 200 ";
        command.CommandText = query;
        command.ExecuteNonQuery();

        SQLiteDataAdapter dataAdaptor = new SQLiteDataAdapter(command);
        DataSet dataset = new DataSet();
        dataAdaptor.Fill(dataset, "dataset_name");

        // Get the table from the data set
        DataTable datatable = dataset.Tables["dataset_name"];
        listView4.Items.Clear();

        // Display items in the ListView control
        for (int i = 0; i < datatable.Rows.Count; i++)
        {
            DataRow datarow = datatable.Rows[i];
            if (datarow.RowState != DataRowState.Deleted)
            {
                // Define the list items
                ListViewItem lvi = new ListViewItem(datarow["Row1"].ToString());
                lvi.SubItems.Add(datarow["Row2"].ToString());
                lvi.SubItems.Add(datarow["Row3"].ToString());
                lvi.SubItems.Add(datarow["Row4"].ToString());
                lvi.SubItems.Add(datarow["Row5"].ToString());
                lvi.SubItems.Add(datarow["Row6"].ToString());
                lvi.SubItems.Add(datarow["Row7"].ToString());

                // Add the list items to the ListView
                listView4.Items.Add(lvi);
            }
        }
        connection.Close();
    }
    catch(SQLiteException ex)
    {
        MessageBox.Show(ex.ToString(), "Error", MessageBoxButtons.OK);
    }
}
2012-04-04 17:14
by Daniel
You are running your query twice, you don't need this line command.ExecuteNonQuery();. dataAdaptor.Fill(dataset, "dataset_name"); Will run the quer - Jmyster 2012-04-04 17:42
@Jmyster Thank you for pointing that ou - Daniel 2012-04-04 17:52


1

Id use a DataView so I can get a count of the records I need using the RowStateFilter. Then iterate through the DataView with a foreach instead of a for statement.

        ProgressBar _Bar = new ProgressBar();

        DataView _View = new DataView();
        _View.Table = datatable;
        _View.RowStateFilter = DataViewRowState.CurrentRows;

        _Bar.Minimum = 0;
        _Bar.Maximum = _View.ToTable().Rows.Count;

        foreach (DataRow datarow in _View.ToTable().Rows)
        {
            // Define the list items    
            ListViewItem lvi = new ListViewItem(datarow["Row1"].ToString());
            lvi.SubItems.Add(datarow["Row2"].ToString());
            lvi.SubItems.Add(datarow["Row3"].ToString());
            lvi.SubItems.Add(datarow["Row4"].ToString());
            lvi.SubItems.Add(datarow["Row5"].ToString());
            lvi.SubItems.Add(datarow["Row6"].ToString());
            lvi.SubItems.Add(datarow["Row7"].ToString());

            // Add the list items to the ListView    
            listView4.Items.Add(lvi); 
            _Bar.PerformStep();
        }

The following incraments the progress bar:

_Bar.PerformStep();

Without running this code I suspect you will have issues with the Progressbar actually updating like you think it will. Your process is running on the UI thread so it will be blocking the UI from updating. You might want to look into running this process on another thread. That is a whole other topic.

2012-04-04 17:57
by Jmyster
This works brilliantly, would you recommend threading it? or leaving it as it is - Daniel 2012-04-04 18:24
If it works fine then you are good =) Glad I can hel - Jmyster 2012-04-04 18:27


1

For showing and updating the progress bar, you have to set Maximum and then Increment method whenever you like to show the progress. That would mean adding code for increment where you want. You can either set the Value property or use the Increment function.

To update progress bar, you have to delegate to the progress bar's parent thread just like you have to with other controls.

A simple example would be:

progressBar.Maximum = 200; //you have 200 records
//after retrieving one record, and adding it to list you can do
//you may have to Invoke it on creating thread if InvokeRequired.
progressBar.Increment(1);

You can get lots of samples from internet like so

2012-04-04 17:34
by ata
That is actually working for me but if I want to make it invisible on completion it never seems to get to the very end of the progress bar before becoming invisible Would you know why this is - Daniel 2012-04-04 18:14
so you still having problems - ata 2012-04-04 18:49
Nope, everything is all good now, thanks for the hel - Daniel 2012-04-04 18:56
Ads