How to show progress of reading from a file and writing to a database

Question

I have the following function which is called from a button click. The function reads the file and then attempts to write to a SQL table:

public void saveCSV()
{
    lblCSVStatus.Text = "";
    worker = new BackgroundWorker { WorkerReportsProgress = true };
    worker.DoWork += (sender, args) =>
    {
        tbTable.Invoke((MethodInvoker)delegate
        {
            tbTable.Enabled = false;
        });

        myConnection = new SqlConnection(cString);
        myConnection.Open();

        /* BEGIN READING CSV FILE */
        StreamReader sr = new StreamReader(tbCSVFileLocation.Text.ToString());
        string line = sr.ReadLine();
        string[] value = line.Split(',');
        DataTable dt = new DataTable();
        DataRow row;

        foreach (string dc in value)
        {
            dt.Columns.Add(new DataColumn(dc));
        }

        while (!sr.EndOfStream)
        {
            value = sr.ReadLine().Split(',');
            if (value.Length == dt.Columns.Count)
            {
                row = dt.NewRow();
                row.ItemArray = value;
                dt.Rows.Add(row);
            }
        }
        /* END READING CSV FILE */

        /* CREATE TABLE IF DOESN'T EXIST AND WRITE (APPEND/OVERWRITE) THE DATA */
        string exists = null;
        try
        {
            SqlCommand cmd = 
                new SqlCommand("SELECT * FROM sysobjects where name = '" + tbTable.Text + "'",
                               myConnection);
            exists = cmd.ExecuteScalar().ToString();
        }
        catch (Exception exce)
        {
            exists = null;
        }

        if (exists == null)
        {
            foreach (DataColumn dc in dt.Columns)
            {
                if (exists == null)
                {
                    SqlCommand createtable =
                        new SqlCommand("CREATE TABLE " + tbTable.Text + " (" + dc.ColumnName + " varchar(MAX))",
                                       myConnection);
                    createtable.ExecuteNonQuery();
                    exists = tbTable.Text;
                }
                else
                {
                    SqlCommand addcolumn =
                        new SqlCommand("ALTER TABLE " + tbTable.Text + " ADD [" + dc.ColumnName + "] varchar(MAX)",
                                       myConnection);
                    addcolumn.ExecuteNonQuery();
                }
            }

            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(myConnection))
            {
                try
                {
                    bulkCopy.DestinationTableName = tbTable.Text;
                    bulkCopy.WriteToServer(dt);
                    lblCSVStatus.Invoke((MethodInvoker)delegate
                    {
                        lblCSVStatus.Text = "Successfully Updated " + tbTable.Text + " Table";
                        lblCSVStatus.ForeColor = System.Drawing.Color.Green;
                    });
                    tbTable.Invoke((MethodInvoker)delegate
                    {
                        tbTable.Enabled = true;
                    });
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message.ToString(),
                                    "Program Error",
                                    MessageBoxButtons.OK,
                                    MessageBoxIcon.Exclamation);
                    lblCSVStatus.Invoke((MethodInvoker)delegate
                    {
                        lblCSVStatus.Text = "Failed to Update " + tbTable.Text + " Table";
                        lblCSVStatus.ForeColor = System.Drawing.Color.Red;
                    });
                    tbTable.Invoke((MethodInvoker)delegate
                    {
                        tbTable.Enabled = true;
                    });
                }
            }
        }
        else
        {
            if (rbAppend.Checked == true)
            {
                using (SqlBulkCopy bulkcopy = new SqlBulkCopy(myConnection))
                {
                    try
                    {
                        bulkcopy.DestinationTableName = tbTable.Text;
                        bulkcopy.WriteToServer(dt);
                        lblCSVStatus.Invoke((MethodInvoker)delegate
                        {
                            lblCSVStatus.Text = "Successfully Updated " + tbTable.Text + " Table";
                            lblCSVStatus.ForeColor = System.Drawing.Color.Green;
                        });
                        tbTable.Invoke((MethodInvoker)delegate
                        {
                            tbTable.Enabled = true;
                        });
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message.ToString(),
                                        "Program Error",
                                        MessageBoxButtons.OK,
                                        MessageBoxIcon.Exclamation);
                        lblCSVStatus.Invoke((MethodInvoker)delegate
                        {
                            lblCSVStatus.Text = "Failed to Update " + tbTable.Text + " Table";
                            lblCSVStatus.ForeColor = System.Drawing.Color.Red;
                        });
                        tbTable.Invoke((MethodInvoker)delegate
                        {
                            tbTable.Enabled = true;
                        });
                    }
                }
            }
            if (rbUpdate.Checked == true)
            {
                SqlCommand truncateTable = new SqlCommand("TRUNCATE TABLE " + tbTable.Text + "",
                                                          myConnection);
                truncateTable.ExecuteNonQuery();
                using (SqlBulkCopy bulkcopy = new SqlBulkCopy(myConnection))
                {
                    try
                    {
                        bulkcopy.DestinationTableName = tbTable.Text;
                        bulkcopy.WriteToServer(dt);
                        lblCSVStatus.Invoke((MethodInvoker)delegate
                        {
                            lblCSVStatus.Text = "Successfully Updated " + tbTable.Text + " Table";
                            lblCSVStatus.ForeColor = System.Drawing.Color.Green;
                        });
                        tbTable.Invoke((MethodInvoker)delegate
                        {
                            tbTable.Enabled = true;
                        });
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message.ToString(),
                                        "Program Error",
                                        MessageBoxButtons.OK,
                                        MessageBoxIcon.Exclamation);
                        lblCSVStatus.Invoke((MethodInvoker)delegate
                        {
                            lblCSVStatus.Text = "Failed to Update " + tbTable.Text + " Table";
                            lblCSVStatus.ForeColor = System.Drawing.Color.Red;
                        });
                        tbTable.Invoke((MethodInvoker)delegate
                        {
                            tbTable.Enabled = true;
                        });
                    }
                }
            } 
         }

         myConnection.Close();

         sr.Close();
    };
    worker.ProgressChanged += (sender, args) =>
    {
        //lblCSVStatus.Text = "Working...";
        pbUpdate.Value = args.ProgressPercentage;
    };

    worker.RunWorkerAsync();
}

I am making use of the BackgroundWorker to show the progress of the action. I am trying to update the ProgressBar pbUpdate value from 0 to 100 based on the action.

How can I modify the code to achieve what I am looking to do?

Answer 1

Once you open the StreamReader, you can get the length from it: sr.Length. After each ReadLine, you can get the current position with sr.Position.

With those two data items you know what percentage has been processed. (However, it doesn't tell you how many lines there are in the file, just that you are xKb through a total of yKb.)