Implement progress bar on SQLite database read C#

问题:

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);
    }
}

回答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.

回答2:

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