Using Progress Report With Export/Backup


Below guide is explained in WinForm.

In this example, we are going to create a few controls on a new WinForm:

export01.png

Code behind,

Add two using statements:
using System.IO;
using MySql.Data.MySqlClient;

Initial code behind of a new WinForm:
public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
    }
}

There are a few variables and components will be used and shared among methods and threads. Therefore, we can declare them at the top level of the class. Example:
public partial class Form1 : Form
{
    MySqlConnection conn;
    MySqlCommand cmd;
    MySqlBackup mb;
    Timer timer1;
    BackgroundWorker bwExport;

    string _currentTableName = "";
    int _totalRowsInCurrentTable = 0;
    int _totalRowsInAllTables = 0;
    int _currentRowIndexInCurrentTable = 0;
    int _currentRowIndexInAllTable = 0;
    int _totalTables = 0;
    int _currentTableIndex = 0;

    bool cancel = false;

    string dumpFile = "C:\\backup.sql";

    public Form1()
    {
        InitializeComponent();
    }
}

A BackgroundWorker (bwExport) is used to handle the Export Process on another Thread. By doing this, the UI of WinForm will not freeze during the Export Process is running.

Initialize components and create event handlers at the Class's constructor.

public Form1()
{
    InitializeComponent();

    mb = new MySqlBackup();
    mb.ExportProgressChanged += mb_ExportProgressChanged;

    timer1 = new Timer();
    timer1.Interval = 50;
    timer1.Tick += timer1_Tick;

    bwExport = new BackgroundWorker();
    bwExport.DoWork += bwExport_DoWork;
    bwExport.RunWorkerCompleted += bwExport_RunWorkerCompleted;
}

void bwExport_DoWork(object sender, DoWorkEventArgs e)
{

}

void mb_ExportProgressChanged(object sender, ExportProgressArgs e)
{

}

void timer1_Tick(object sender, EventArgs e)
{

}

void bwExport_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
{

}

And we have the 2 button's click events:
private void btExport_Click(object sender, EventArgs e)
{
 
}

private void btCancel_Click(object sender, EventArgs e)
{

}

Now we are going to fill in codes into the methods and events handlers.

First, we start from the btExport. When a user click on the [Export] button, the Form executes the following codes:

private void btExport_Click(object sender, EventArgs e)
{
    // Reset variables to initial state
    cancel = false;
    _currentTableName = "";
    _totalRowsInCurrentTable = 0;
    _totalRowsInAllTables = 0;
    _currentRowIndexInCurrentTable = 0;
    _currentRowIndexInAllTable = 0;
    _totalTables = 0;
    _currentTableIndex = 0;

    // Initialize MySqlConnection and MySqlCommand components
    conn = new MySqlConnection(ConnectionString);
    cmd = new MySqlCommand();
    cmd.Connection = conn;
    conn.Open();

    // Start the Timer here
    timer1.Start();

    mb.ExportInfo.IntervalForProgressReport = (int)nmExInterval.Value;

    // This option is required for progress report.
    mb.ExportInfo.GetTotalRowsBeforeExport = true;
    // However, it might takes some times to retrieve the total rows if
    // your database contains thousands millions of rows.

    mb.Command = cmd;

    bwExport.RunWorkerAsync();
}

The timer is used to trigger the update/redraw of the progress bar.

From there, the component of BackgroundWorker - bwExport is called for work. bwExport.RunWorkerAsync() will execute the following codes:

void bwExport_DoWork(object sender, DoWorkEventArgs e)
{
    try
    {
        mb.ExportToFile(dumpFile);
    }
    catch (Exception ex)
    {
        cancel = true;
        CloseConnection();
        MessageBox.Show(ex.ToString());
    }
}

You will notice that a method called CloseConnection() is executed in the Exception Catching block.
If anything bad happens in the process, we need to close the connection between our application and MySQL server.

void CloseConnection()
{
    if (conn != null)
    {
        conn.Close();
        conn.Dispose();
    }

    if (cmd != null)
        cmd.Dispose();
}

During the Export task is running, this event will raise to report it's status:

void mb_ExportProgressChanged(object sender, ExportProgressArgs e)
{
    if (cancel)
    {
        // Calling mb to halt
        mb.StopAllProcess();
        return;
    }

    _currentRowIndexInAllTable = (int)e.CurrentRowIndexInAllTables;
    _currentRowIndexInCurrentTable = (int)e.CurrentRowIndexInCurrentTable;
    _currentTableIndex = e.CurrentTableIndex;
    _currentTableName = e.CurrentTableName;
    _totalRowsInAllTables = (int)e.TotalRowsInAllTables;
    _totalRowsInCurrentTable = (int)e.TotalRowsInCurrentTable;
    _totalTables = e.TotalTables;
}

Load the values into a temporary location. Use the timer to read the values and update/redraw the progress bar and label on the UI/Form.

void timer1_Tick(object sender, EventArgs e)
{
    if (cancel)
    {
        timer1.Stop();
        return;
    }

    pbTable.Maximum = _totalTables;
    if (_currentTableIndex <= pbTable.Maximum)
        pbTable.Value = _currentTableIndex;

    pbRowInCurTable.Maximum = _totalRowsInCurrentTable;
    if (_currentRowIndexInCurrentTable <= pbRowInCurTable.Maximum)
        pbRowInCurTable.Value = _currentRowIndexInCurrentTable;

    pbRowInAllTable.Maximum = _totalRowsInAllTables;
    if (_currentRowIndexInAllTable <= pbRowInAllTable.Maximum)
        pbRowInAllTable.Value = _currentRowIndexInAllTable;

    lbCurrentTableName.Text = "Current Processing Table = " + _currentTableName;
    lbRowInCurTable.Text = pbRowInCurTable.Value + " of " + pbRowInCurTable.Maximum;
    lbRowInAllTable.Text = pbRowInAllTable.Value + " of " + pbRowInAllTable.Maximum;
    lbTableCount.Text = _currentTableIndex + " of " + _totalTables;
}

After the Export is finished running (either finished completely or cancel or error), the BackgroundWorker of bwExport will raise and execute this event:

void bwExport_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
{
    CloseConnection();

    if (cancel)
    {
        MessageBox.Show("Cancel by user.");
    }
    else
    {
        if (mb.LastError == null)
        {
            pbRowInAllTable.Value = pbRowInAllTable.Maximum;
            pbRowInCurTable.Value = pbRowInCurTable.Maximum;
            pbTable.Value = pbTable.Maximum;
            this.Refresh();
            MessageBox.Show("Completed.");
        }
        else
            MessageBox.Show("Completed with error(s)." + Environment.NewLine + Environment.NewLine + mb.LastError.ToString());
    }

    timer1.Stop();
}

During the Export is running, user can cancel / stop the process any time by pressing the [Cancel] button and this will execute this codes:

private void btCancel_Click(object sender, EventArgs e)
{
    cancel = true;
}

Calling mb.ExportCompleted or bw.RunWorkerCompleted

For synchronous process, calling mb.ExportCompleted is not needed. You will know when the job is done.

For asynchronous process (running MySqlBackup on another thread), lets take the following codes as example:

public partial class Form1 : Form
{
    MySqlConnection conn;
    MySqlCommand cmd = new MySqlCommand();
    MySqlBackup mb = new MySqlBackup();
    BackgroundWorker bw = new BackgroundWorker();
 
    public Form1()
    {
        InitializeComponent();
        mb.ExportCompleted += mb_ExportCompleted;
        bw.DoWork += bw_DoWork;
        bw.RunWorkerCompleted += bw_RunWorkerCompleted;
    }
 
    private void button1_Click(object sender, EventArgs e)
    {
        conn = new MySqlConnection("server=localhost;user=root;pwd=1234;database=test;");
        cmd.Connection = conn;
        conn.Open();
        mb.Command = cmd;
        bw.RunWorkerAsync();
    }
 
    void bw_DoWork(object sender, DoWorkEventArgs e)
    {
        mb.ExportToFile("Y:\\backup.sql");
    }
 
    void mb_ExportCompleted(object sender, ExportCompleteArgs e)
    {
        conn.Close();
        conn.Dispose();
        MessageBox.Show("Done - mb_ExportCompleted");
    }
 
    void bw_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
    {
        MessageBox.Show("Done - bw_RunWorkerCompleted");
    }
}

First, mb_ExportCompleted will be called, followed by bw_RunWorkerCompleted.
They are happened almost at the same time. Calling either mb_ExportCompleted or bw_RunWorkerCompleted has no different.

It would have different meaning if you continue to do some other works after MySqlBackup is completed within the BackgroundWorker. For example:

void bw_DoWork(object sender, DoWorkEventArgs e)
{
    mb.ExportToFile("C:\\backup.sql");
 
    // Perform additional tasks
    DoSomething1();
    DoSomething2();
    ZipTheFile("C:\\backup.sql", "C:\\backup.zip");
    DeleteFile("C:\\backup.sql");
}

Then, calling bw_RunWorkerCompleted is better than mb_ExportCompleted which indicates the whole BackgroundWorker is done.

However, this is up to you to decide and depends on how you code your program.

Last edited May 11, 2014 at 10:18 AM by adriancs, version 12