This project has moved. For the latest updates, please go here.

Documentation for MySqlBackup.NET 1.5.6

Contents

  1. Background
  2. Major Change in 1.5
  3. Class Structure of MySqlBackup.NET 1.5
  4. Adding MySqlBackup.NET Into Your Project
  5. Examples
  6. Examples in ASP.NET
  7. About Synchronous and Asynchronous Mode
  8. Using MySqlBackup.NET with Progress Bar
  9. Gathering Information about Export/Import Process When You Run MySqlBackup.NET Synchronously
  10. About Importing (Restoring) to Another/New Database

1. Background

This guide is assumed that you have already familiar with MySQL dot net connector (MySql.Data.dll) with minimum knowledge that you are able to perform the four basic operation SELECT, INSERT, UPDATE and DELETE. In case you are not, you can read the walk-through and explanation on Connecting C# to MySQL at: [http://www.codeproject.com/Articles/43438/Connect-C-to-MySQL].


2. Major Change in 1.5

  • New: Able to run in both Asynchronous and Synchronous mode.
  • New: Able to restore to another/new database.
  • New: Reset database, delete all rows and reset Auto-Increment to 1.
  • Fix bug: A SQL syntax error will occur when Importing with dump file that is encrypted with Stored Procedure, Function, Event, Trigger or View.
  • Fix bug: A null exception occur when cancelling a process.

Read more: [Full Change Log]


3. Class Structure of MySqlBackup.NET 1.5

Constructor

  • MySqlBackup()
  • MySqlBackup(string)
  • MySqlBackup(MySqlConnection)
  • MySqlBackup(MySqlCommand)

Public Properties

  • Connection (MySqlConnection) - Gets or Sets the MySqlConnection that used by this instance.
  • DatabaseInfo (Database) - Gets the infomations about the connected database.
    • Properties, Fields, Methods
      • CreateDatabaseSql(string)- Gets the SQL Statement of CREATE DATABASE of this database.
      • DatabaseName (string) - Gets the Name of this Database.
      • Tables (Dictionary<string, Table>) - Gets all the Tables' information. Key = Table name; Value = Table.
      • ServerVersion (string) - Gets the MySQL Server Version's number and name.
      • ServerVersionNo (string)- Gets the MySQL Server Version's number.
      • ServerMajorVersion (double) - Gets the MSQL Server Major Version's number.
      • DefaultDatabaseCharSet (string) - Gets the default character set of current database.
      • TableNames (string[]) - Gets all table's name.
      • StoredProcedure (Dictionary<string,string>) - Gets or Sets Stored Procedures of current database.
      • StoredFunction (Dictionary<string,string>) - Gets or Sets Stored Functions of current database.
      • StoredTrigger (Dictionary<string,string>) - Gets or Sets Stored Triggers of current database;
      • StoredEvents (Dictionary<string,string>) - Gets or Sets Stored Events of current database.
      • StoredView (Dictionary<string,string>) - Gets or Sets Stored Views of current database.
      • GetTotalRows (Long) - Gets the total rows in all tables of current database.
      • (Event) CalculateTotalRowsProgressChanged - Occur when total rows calculation of 1 table has completed.
      • (Event) CalculateTotalRowsCompleted - Occur when total rows calcualtion of all table has completed.
  • ExportInfo (ExportInformations) - Gets or Sets the Informations that define behaviour of Export Process. Read more at Class of ExportInformations below.
    • Properties, Methods and Fields
      • FileName - Gets or Sets the full path and file name (dump file) that will be saved to. The output of the export process.
      • TableCustomSql - Gets or Sets the tables that will be exported and the SQL for selecting the rows of the tables. If the SQL is left blank, the SQL will replace by "SELECT * FROM tablename". Key = tablename, Value = SQL
      • TablesToBeExported - Gets or Sets the tables that will be exported
      • RecordDumpTime - Gets or Sets a value indicates whether the Dump Time should recorded in dump file.
      • AsynchronousMode - Gets or Sets a value indicates whether the Export process should run in Asynchronous Mode.
      • CalculateTotalRowsFromDatabase - Gets or Sets a value indicates whether total rows should be calculated before Export Process starts.
      • AutoCloseConnection - Gets or Sets a value indicates whether the MySqlConnection and MySqlCommand used should close and dispose after export process finished.
      • EnableEncryption - Gets or Sets a value indicates whether the Exported Dump File should be encrypted.
      • EncryptionKey - Gets or Sets the key or password used to encrypt the exported dump file.
      • SaltSize - Gets the length of salt used in encryption.
      • AddCreateDatabase - Gets or Sets a value indicates the SQL statement of "CREATE DATABASE" should added into dump file.
      • ExportTableStructure - or Sets a value indicates the Table Structure (CREATE TABLE) should be exported.
      • ResetAutoIncrement - Gets or Sets a value indicates the value of auto-increment of each table should be reset to 1.
      • ExportRows - Gets or Sets a value indicates the Rows should be exported.
      • MaxSqlLength - Gets or Sets the Maximum Length allowed for multiple INSERT SQL statements to join. Default value is 1MB.
      • ExportStoredProcedures - Gets or Sets a value indicates whether the Stored Procedures should be exported.
      • ExportFunctions - Gets or Sets a value indicates whether the Stored Functions should be exported.
      • ExportTriggers - Gets or Sets a value indicates whether the Stored Triggers should be exported.
      • ExportViews - Gets or Sets a value indicates whether the Stored Views should be exported.
      • ExportEvents - Gets or Sets a value indicates whether the Stored Events should be exported.
      • CompleteArg - ExportCompleteArg (The event arguments of ExportCompleted).
      • ZipOutputFile - Gets or Sets a value indicates whether the exported SQL Dump File should be compressed as Zip File.
  • ImportInfo (ImportInformations) - Gets or Sets the Informations that define behaviour of Import Process.
    • Properties, Methods and Fields
      • FileName - Gets or Sets the full path and file name (dump file) that will be imported.
      • AsynchronousMode - Gets or Sets a value indicates whether the Import process should run in Asynchronous Mode.
      • AutoCloseConnection - Gets or Sets a value indicates whether the MySqlConnection and MySqlCommand used should close and dispose after import process finished.
      • EnableEncryption - Gets or Sets a value indicates whether the Imported Dump File is encrypted.
      • EncryptionKey - Gets or Sets the key or password used to decrypt the exported dump file.
      • SaltSize - Gets the lenght of salt used in encryption.
      • SetTargetDatabase(string, string) - Set the target database name that will be imported to and the default character set. If the database if not exists, it will be created.
      • SetTargetDatabase(string) - Set the target database name that will be imported to. If the database if not exists, it will be created. The default character set of current connecting MySQL server will be used as default character set for this new target database.
      • SetTargetDatabase(string, Enum) - the target database name that will be imported to and the default character set. If the database if not exists, it will be created.
      • CreateTargetDatabaseSql - Gets the CREATE DATABASE SQL statement of current database.
      • TargetDatabase - Gets the name of target database.
      • IgnoreSqlError - Gets or Sets a value indicates whether SQL errors occus in import process should be ignored. If true, all errors (exceptions) will be collected in ImportCompleteArg.Errors.
      • CompleteArg - ImportCompleteArg (Event argument of ImportCompleted)

Public Methods

  • Export() - Execute the export (backup) process.
  • Export(ExportInformations) - Execute the export (backup) process. Sets the ExportInformations used by this export process.
  • CancelExport() - Cancel the current executing export process.
  • Import() - Execute the import process.
  • Import(ImportInformations) - Execute the import (restore) process. Sets the ImportInformations about this import process.
  • CancelImport() - Cancel the current executing import process.
  • DeleteAllRows(bool) - Delete all rows in all tables. Sets a value indicates whether Auto-Increment should reset to 1.
  • DeleteAllRows(bool, string[]) - Delete all rows in all tables. Sets a value indicates whether Auto-Increment should reset to 1. Exclude these tables from rows deletion.
  • EncryptSqlDumpFile(string, string) - Encrypt a SQL Dump File and save as new file.
  • DecryptSqlDumpFile(string, string) - Decrypt a SQL Dump File and save as new file.
  • ExportBlobAsFile(string, string, string, string, string) - Export BLOB data type and save as file.
  • Dispose() - Release all resources used by this instance.
  • Dispose(bool) - Release all resources used by this instance. Determine whether MySqlConnection and MySqlCommand used by this instance should dispose.

Events

  • ExportProgressChanged - Occur when a row of data is exported or calculation of total rows of a table is completed.
    • Event Arguments:
      • CurrentTableName - String
      • TotalRowsInCurrentTable - Long
      • TotalRowsInAllTables - Long
      • CurrentRowInCurrentTable - Long
      • CurrentRowInAllTable - Long
      • TotalTables - Int
      • CurrentTableIndex - Int
      • PercentageCompleted - Int
      • PercentageGetTotalRowsCompleted - Int
  • ExportCompleted - Occur when Export processs is finished.
    • Event Arguments:
      • TimeStart - DateTime
      • TimeEnd - DateTime
      • TimeUsed - TimeSpan
      • Error - Exception (Error that occur during the process)
      • CompletedType - Enum (Completed, Cancelled, Error)
  • ImportProgressChanged - Occur when a line in the dump file is imported.
    • Event Arguments:
      • CurrentByte - Long
      • TotalBytes - Long
      • Error - Exception (The error information that encounter.)
      • CurrentLineNo - Long
      • ErrorSql - String (The SQL statement that creates the error)
      • PercentageCompleted
  • ImportCompleted - Occur when Import process is finished.
    • Event Arguments:
      • TimeStart - DateTime
      • TimeEnd - DateTime
      • TimeUsed - TimeSpan
      • CurrentLineNo - Long
      • HasErrors - Bool
      • LastError - Exception (The last error (exception) occur in import process.)
      • CompletedType - Enum (Completed, Cancelled, Error)
      • Errors - Dictionary<long, Exception> (The collection of errors. Exceptions will be collected if Ignore SQL Error has set to true in Import Info.)

4. Adding MySqlBackup.NET Into Your Project

Before using this library, you must add a reference of MySQL Dot Net Connector (MySql.Data.DLL). You can get the lastest MySql Dot Net Connector at: [http://dev.mysql.com/downloads/connector/net/].

There are a few easy way to add MySqlBackup.NET in your C# (WinForm, ASP.NET) application. 

1st method, using the Nuget (Note: It is available, but not updated to latest version yet) (https://nuget.org/packages/MySqlBackup.NET). At the Package Manager Console of Nuget in Visual Studio, key in the below command, it will automatically download and install MySqlBackup.NET into your project.

PM> Install-Package MySqlBackup.NET 

2nd method, add all the CS files into your source code directly. Source code available at [Download] section.

Create a folder and add the CS files:

At top of your cs file, add this line:

using MySql.Data.MySqlClient;

Using in VB.NET,

You can use Nuget to help you. If you want to do it manually, you have to compile the library into a DLL and add it as reference into your VB.NET project.


5. Basic Examples

Examples in VB.NET are currently not available.

Example 1: Backup

void Backup()
{
    string constr = "server=localhost;user=root;pwd=qwerty;database=test;";
    string file = "C:\\MyDumpFile.sql";
    MySqlBackup mb = new MySqlBackup(constr);
    mb.ExportInfo.FileName = file;
    mb.Export();
}

Example 2: Restore

Note: The source file can be either a SQL Dump File or a compressed Zip file (contain single dump file)

void Restore()
{
    string constr = "server=localhost;user=root;pwd=qwerty;database=test;";
    string file = "C:\\MyDumpFile.sql";
    MySqlBackup mb = new MySqlBackup(constr);
    mb.ImportInfo.FileName = file;
    mb.Import(); 
}

Example 3: Export and Compress as Zip File

Note: You can use a compressed SQL Dump File in Zip format as source file directly to restore a database.

void Backup()
{
    string constr = "server=localhost;user=root;pwd=qwerty;database=test;";
    string file = "C:\\MyDumpFile.sql";
    MySqlBackup mb = new MySqlBackup(constr);
    mb.ExportInfo.FileName = file;
    mb.ExportInfo.ZipOutputFile = true;
    mb.Export();
}

Example 4: Select tables to backup

void BackupTables()
{
    string constr = "server=localhost;user=root;pwd=qwerty;database=test;";
    string file = "C:\\MyDumpFile.sql";
    MySqlBackup mb = new MySqlBackup(constr);
    mb.ExportInfo.FileName = file;
    mb.ExportInfo.TablesToBeExported = new string[] { "member", "activity", "season" };
    mb.Export();
}

Example 5: Custom columns and rows backup conditions

void BackupTablesCustomSQL()
{
    Dictionary<string, string> dic = new Dictionary<string, string>();
    dic.Add("member", "SELECT * FROM `member` WHERE `membertype` = 1;");
    dic.Add("payment", "SELECT `id`,`total` FROM `payment`;");
    dic.Add("activity", "SELECT * FROM `activity` a INNER JOIN `season` s ON a.`seasonid` = s.`id` WHERE s.`name` = 'Spring';");
    string constr = "server=localhost;user=root;pwd=qwerty;database=test;";
    string file = "C:\\MyDumpFile.sql";
    MySqlBackup mb = new MySqlBackup(constr);
    mb.ExportInfo.FileName = file;
    mb.ExportInfo.TableCustomSql = dic;
    mb.Export();
}

Example 6: Backup with Encryption

void BackupEncrypt()
{
    string constr = "server=localhost;user=root;pwd=qwerty;database=test;";
    string file = "C:\\MyDumpFile.sql";
    MySqlBackup mb = new MySqlBackup(constr);
    mb.ExportInfo.FileName = file;
    mb.ExportInfo.EnableEncryption = true;
    mb.ExportInfo.EncryptionKey = "my secret password";
    mb.Export();
}

Example 7: Resotre with Decryption

void RestoreDecrypt()
{
    string constr = "server=localhost;user=root;pwd=qwerty;database=test;";
    string file = "C:\\MyDumpFile.sql";
    MySqlBackup mb = new MySqlBackup(constr);
    mb.ImportInfo.FileName = file;
    mb.ImportInfo.EnableEncryption = true;
    mb.ImportInfo.EncryptionKey = "my secret password";
    mb.Import(); 
}

Example 8: Encrypt & Decrypt a SQL Dump File

void EncryptDecryptDumpFile()
{
    MySqlBackup mb = new MySqlBackup();
    string file1 = "C:\\MyDumpFileOld.sql";
    string file2 = "C:\\MyDumpFileNew.sql";
    string file3 = "C:\\MyDumpFileBackToOri.sql";
    string password = "my secret password";
    mb.EncryptSqlDumpFile(file1, file2, password);
    mb.DecryptSqlDumpFile(file2, file3, password);
}

Example 9: Backup Table Structures without rows and reset auto-increment to 1

void BackupNoRows()
{
    string constr = "server=localhost;user=root;pwd=qwerty;database=test;";
    string file = "C:\\MyDumpFile.sql";
    MySqlBackup mb = new MySqlBackup(constr);
    mb.ExportInfo.FileName = file;
    mb.ExportInfo.ExportRows = false;
    mb.ExportInfo.ResetAutoIncrement = true;
    mb.Export();
}

Example 10: Full List of Settings for Export (Backup)

There are some more settings which affect the output results of Export Process. All of these settings are configured in ExportInfo. A full list of settings:

void BackupNoRows()
{
    string constr = "server=localhost;user=root;pwd=qwerty;database=test;";
    string file = "C:\\MyDumpFile.sql";
    MySqlBackup mb = new MySqlBackup(constr);
    mb.ExportInfo.FileName = file;
    mb.ExportInfo.AddCreateDatabase = true;
    mb.ExportInfo.AsynchronousMode = true;
    mb.ExportInfo.AutoCloseConnection = true;
    mb.ExportInfo.CalculateTotalRowsFromDatabase = true;
    mb.ExportInfo.EnableEncryption = true;
    mb.ExportInfo.EncryptionKey = "my secret password";
    mb.ExportInfo.ExportEvents = true;
    mb.ExportInfo.ExportFunctions = true;
    mb.ExportInfo.ExportRows = true;
    mb.ExportInfo.ExportStoredProcedures = true;
    mb.ExportInfo.ExportTableStructure = true;
    mb.ExportInfo.ExportTriggers = true;
    mb.ExportInfo.ExportViews = true;
    mb.ExportInfo.MaxSqlLength = 10000000;
    mb.ExportInfo.RecordDumpTime = true;
    mb.ExportInfo.ResetAutoIncrement = true;
    mb.ExportInfo.TableCustomSql = //Shown in example 4
    mb.ExportInfo.TablesToBeExported = //Shown in example 3
    mb.ExportInfo.ExportRows = false;
    mb.ExportInfo.ResetAutoIncrement = true;
    mb.Export();
}

Example 11: Full List of Settings for Import

void Restore()
{
    string constr = "server=localhost;user=root;pwd=qwerty;database=test;";
    string file = "C:\\MyDumpFile.sql";
    MySqlBackup mb = new MySqlBackup(constr);
    mb.ImportInfo.FileName = file;
    mb.ImportInfo.AsynchronousMode = true;
    mb.ImportInfo.AutoCloseConnection = true;
    mb.ImportInfo.EnableEncryption = true;
    mb.ImportInfo.EncryptionKey = "my secret password";
    mb.ImportInfo.IgnoreSqlError = true;
    mb.ImportInfo.SetTargetDatabase("MyNewDatabase", ImportInformations.CharSet.latin1);
    mb.Import(); 
}

Example 12: Export BLOB data and save as files on local drive

private void BackupEncrypt()
{
    string folder = "C:\\exportedfiles";
    string table = "upload";
    string column_Blob = "blobdata";
    string column_FileName = "filename";
    string column_FileSize = "filesize";
    string con = "server=localhost;user=root;pwd=qwerty;database=member;";
    MySqlBackup mb = new MySqlBackup(con);
    mb.ExportBlobAsFile(folder, table, column_Blob, column_FileName, column_FileSize);
}

Example 13: Get all tables' name from database

private string[] GetTableNames()
{
    string con = "server=localhost;user=root;pwd=qwerty;database=test;";
    MySqlBackup mb = new MySqlBackup(con);
    return mb.DatabaseInfo.TableNames;
}

Example 14: Get Create Table sql statement for specific table

private string GetCreateTable(string tableName)
{
    string con = "server=localhost;user=root;pwd=qwerty;database=test;";
    MySqlBackup mb = new MySqlBackup(con);
    return mb.DatabaseInfo.Tables[tableName].CreateTableSql;
}

6. Examples in ASP.NET

MySqlBackup.NET need to access a physical file on disk to perform task. We can use Server.MapPath to provide a physical path for MySqlBackup.NET.

Backup MySQL Database 

void Backup()
{
    string connection = "server=localhost;user=root;pwd=qwerty;database=test;";
    string fileOnDisk = HttpContext.Current.Server.MapPath("~/MyDumpFile.sql");
    // Example Result: C:\inetpub\wwwroot\MyDumpFile.sql
    string fileOnWeb = HttpContext.Current.Request.Url.GetLeftPart(UriPartial.Authority) 
                       + "/MyDumpFile.sql";
    // Example Result: http://www.mywebsite.com/MyDumpFile.sql

    MySqlBackup mb = new MySqlBackup(connection);
    mb.ExportInfo.FileName = fileOnDisk;
    mb.Export();

    // Download the file
    Response.ContentType = "text/plain";
    Response.AppendHeader("Content-Disposition", "attachment; filename=MyDumpFile.sql");
    Response.TransmitFile(fileOnDisk);
    Response.End();
} 

Restore MySQL Database

void Restore()
{
    string connection = "server=localhost;user=root;pwd=qwerty;database=test;";
    string fileOnDisk = HttpContext.Current.Server.MapPath("~/MyDumpFile.sql");
    string fileOnWeb = HttpContext.Current.Request.Url.GetLeftPart(UriPartial.Authority) 
                       + "/MyDumpFile.sql";
    // Upload the file
    if (FileUpload1.HasFile)
    {
        FileUpload1.SaveAs(fileOnDisk);

        MySqlBackup mb = new MySqlBackup(connection);
        mb.ImportInfo.FileName = fileOnDisk;
        mb.Import();
        Response.Write("Import Successfully");
    }
}

7. About Synchronous And Asynchronous Mode

Switching between Asynchronous and Synchronous Mode is a new feature in V1.5.

By default, all process run in Synchronous Mode (Single Thread).

To execute Export (Backup) process in Asynchronous Mode:

void Backup(string connection, string file)
{
    MySqlBackup mb = new MySqlBackup(connection);
    mb.ExportInfo.FileName = file;
    mb.ExportInfo.AsynchronousMode = true;
    mb.Export();
}

Asynchronous Mode prevents WinForm UI to freeze. The animation of Progress Bar continues to move while the export or import process running in background. On the other hand, in Asynchronous Mode, you have the choice to cancel/halt the export or import process and you can't do it in Synchronous Mode.

However, in Web Application or Web Service, there is no differences for the process runs either in Asynchronous or Synchronous Mode. This is because the main thread will still need to be suspended.

If you have a small database, the whole process will be completed in less than 1 second. In such situation, running the process in Asynchronous Mode might not be necessary.


8. Using MySqlBackup.NET with Progress Bar

Progress reporting is a new feature in V1.3. This enable the usage of Progress Bar.

Simple Walk Through

  1. MySqlBackup.NET will raise an Event (ExportProgressChanged or ImportProgressChanged) after a row or a line of data is processed.
  2. The Event carries a bunch of values indicating the current processing point.
  3. These values are loaded into a temporary storage location.
  4. Use a Timer (Timer1) to read the values from temporary storage location and load it into Progress Bar repeatedly.
  5. Upon the process is finished, cancelled or encounter error, another Event will raised (ExportCompleted or ImportCompleted) indicating the termination.
  6. Use another Timer (Timer2) to stop Timer1.

Detail Walk Through

This guide is shown in WinForm.

Start by adding this line at the top of the form class.

using MySql.Data.MySqlClient; 

This is what we have initially with a new WinForm.

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
    }
}

Declare some fields for storing the progress value, an instance of MySqlBackup and 2 timers.

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        // Temporary Storage Location
        string CurrentTableName = "";
        long TotalRowsInCurrentTable = 0;
        long TotalRowsInAllTables = 0;
        long CurrentRowInCurrentTable = 0;
        long CurrentRowInAllTable = 0;
        int TotalTables = 0;
        int CurrentTableIndex = 0;
        int PercentageComplete = 0;
        int PercentageGetTotalRowsCompleted = 0;

        MySqlBackup mb;

        Timer timerRead;
        Timer timerStop;

        bool stopTimer1 = false;

        public Form1()
        {
            InitializeComponent();
        }
    }
}

Build the Backup method. 

void Backup()
{
    string constr = "server=localhost;user=root;pwd=qwerty;database=test;";
    string file = "C:\\MyDumpFile.sql";
    MySqlBackup mb = new MySqlBackup(constr);
    mb.ExportInfo.FileName = file;
    mb.ExportProgressChanged += new MySqlBackup.exportProgressChange(mb_ExportProgressChanged);
    mb.ExportCompleted += new MySqlBackup.exportComplete(mb_ExportCompleted);
    timerRead.Start();
    mb.Export();
}

void mb_ExportCompleted(object sender, ExportCompleteArg e)
{
    throw new NotImplementedException();
}

void mb_ExportProgressChanged(object sender, ExportProgressArg e)
{
    throw new NotImplementedException();
}

During event of ExportProgressChanged, load values into temporary storage location:

void mb_ExportProgressChanged(object sender, ExportProgressArg e)
{
    PercentageGetTotalRowsCompleted = e.PercentageGetTotalRowsCompleted;
    PercentageComplete = e.PercentageCompleted;
    CurrentTableName = e.CurrentTableName;
    TotalRowsInCurrentTable = e.TotalRowsInCurrentTable;
    TotalRowsInAllTables = e.TotalRowsInAllTables;
    CurrentRowInCurrentTable = e.CurrentRowInCurrentTable;
    CurrentRowInAllTable = e.CurrentRowInAllTable;
    TotalTables = e.TotalTables;
    CurrentTableIndex = e.CurrentTableIndex;
}

Use a Timer (timerRead) to read the values into Progress Bar.

public Form1()
{
    InitializeComponent();
    timerRead = new Timer();
    timerRead.Interval = 100; // Refresh Progress Bar 10 times in 1 second
    timerRead.Tick += new EventHandler(timerRead_Tick);
}

void timerRead_Tick(object sender, EventArgs e)
{
    progressBar_PercentComplete.Maximum = 100;
    progressBar_PercentComplete.Value = PercentageComplete;

    progressBar_Table.Maximum = TotalTables * 10;
    progressBar_Table.Value = CurrentTableIndex * 10;

    progressBar_RowsDB.Maximum = (int)TotalRowsInAllTables;
    progressBar_RowsDB.Value = (int)CurrentRowInAllTable;

    progressBar_RowsTable.Maximum = (int)TotalRowsInCurrentTable;
    progressBar_RowsTable.Value = (int)CurrentRowInCurrentTable;
}

You may add another method to stop the Export process just in case you need to cancel it.

public void CancelBackup()
{
    mb.CancelExport();
} 

Upon the process is finished or terminated, the final event raise. Call the second timer to stop the first timer. 

void mb_ExportCompleted(object sender, ExportCompleteArg e)
{
    timerStop = new Timer();
    timerStop.Interval = 150;
    timerStop.Tick += new EventHandler(timerStop_Tick);
    timerStop.Start();
}

void timerStop_Tick(object sender, EventArgs e)
{
    timerStop.Stop();
    timerRead.Stop();
}

We do not stop the first timer immediately. This will allow the first timer able to catch up the last value raised. Or else, the Progress Bar might (might not) showing 99% completed in stead of 100%. Notice that the interval time for 2nd timer is a bit longer than 1st timer. The reason is the same.


9. Gathering Information After Export/Import Process Completed

After the process completed, some useful information is stored in ExportCompleteArg and ImportCompleteArg.

For Export process, ExportCompleteArg is part of event argument of ExportCompleted. However, if you do not raise the event, you still can access the values in the ExportInformation.

Event of ExportCompleted must be applied in order to get notified when it is finished if you run MySqlBackup.NET in Asynchronous Mode.

Example of accessing ExportCompleteArg:

void BackupWithEvent(string connection, string file)
{
    MySqlBackup mb = new MySqlBackup(connection);
    mb.ExportInfo.FileName = file;
    mb.ExportCompleted += new MySqlBackup.exportComplete(mb_ExportCompleted);
    mb.Export();
}

void mb_ExportCompleted(object sender, ExportCompleteArg e)
{
    ShowMessage(e);
}

void BackupWithNoEvent(string connection, string file)
{
    MySqlBackup mb = null;
    try
    {
        mb = new MySqlBackup(connection);
        mb.ExportInfo.FileName = file;
        mb.Export();
    }
    catch { }
    ShowMessage(mb.ExportInfo.CompleteArg);
}

void ShowMessage(ExportCompleteArg e)
{
    StringBuilder sb = new StringBuilder();
    sb.Append("Export " + e.CompletedType.ToString() + "\r\n");
    sb.Append("Time Start: " + e.TimeStart.ToString() + "\r\n");
    sb.Append("Time End: " + e.TimeEnd.ToString() + "\r\n");
    sb.Append("Time Used: " + e.TimeUsed.Minutes + " m " + e.TimeUsed.Seconds + " s " + e.TimeUsed.Milliseconds + " ms\r\n\r\n");
    if (e.Error != null)
    {
        sb.Append("Error Message:\r\n\r\n");
        sb.Append(e.Error.ToString());
    }
    else
    {
        sb.Append("File save at: " + mb.ExportInfo.FileName);
    }
    MessageBox.Show(sb.ToString());
}

This is same to ImportCompleteArg.

If you have set IgnoreSqlError to true during Import, you can get all the SQL Errors (Exceptions) in

ImportCompleteArg.Errors

or

ImportProgressArg.Error

ImportProgressArg.ErrorSql


10. About Importing (Restoring) to Another/New Database

In V1.5, you can Restore to another database (maybe it is not exists) other than the original database specified (or not specified ) in the dump file. Here is the example:

void Restore()
{
    string constr = "server=localhost;user=root;pwd=qwerty;";
    string file = "C:\\MyDumpFile.sql";
    MySqlBackup mb = new MySqlBackup(constr);
    mb.ImportInfo.FileName = file;
    mb.ImportInfo.SetTargetDatabase("MyNewDatabase", ImportInformations.CharSet.latin1);
    mb.Import(); 
}

If your database involved with Unicode characters, you have to carefully select the right Default Database Character Set while setting the Target Database. You should always choose Default Character Set that used by the source database of the dump file. Or else, you will experience encoding problems and unable to use the database properly. In some cases, you might even cannot INSERT Unicode characters correctly into the database.

Last edited Dec 14, 2012 at 6:18 AM by adriancs, version 27