The Full Source Code of the Class: MySQLBackupRestore.cs

 

using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;
using System.IO;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using XCrypt;

namespace MySql.Data.MySqlClient
{
    class MySqlBackupRestore
    {
        /// <summary>
        /// Get or Set the Full Path + the Filename to Backup or Restore
        /// </summary>
        public string Filename = "";
        public string MySQLConnectionString = "";
        public string EncryptionKey = "";
        public bool DropAndRecreateDatabase = false;
        public bool DropAndRecreateTable = false;
        public bool Construct_SQL_In_One_Line_From_Same_Table = true;
        public bool EncryptBackupFile = false;

        public string myServer = "";
        public string myUser = "";
        public string myPassword = "";
        public string myDatabase = "";
        public string myPort = "";
        public string myOther = "";

        Form f = null;
        ProgressBar progressBar1 = null;

        public MySqlBackupRestore()
        { }

        public MySqlBackupRestore(string MySqlServer, string MySqlUser, string MySqlPwd, string MySqlDatabase, string MySqlPort, string MySqlOther)
        {
            myServer = MySqlServer;
            myUser = MySqlUser;
            myPassword = MySqlPwd;
            myDatabase = MySqlDatabase;
            myPort = MySqlPort;
            myOther = MySqlOther;
        }

        public void Backup(string filename, bool DropRecreateDatabase, bool DropRecreateTable, bool Construct_SQL_In_One_Line_Of_Same_Table, bool EncryptBackupFILE)
        {
            Filename = filename;
            DropAndRecreateDatabase = DropRecreateDatabase;
            DropAndRecreateTable = DropRecreateTable;
            Construct_SQL_In_One_Line_From_Same_Table = Construct_SQL_In_One_Line_Of_Same_Table;
            EncryptBackupFile = EncryptBackupFILE;
            Backup();
        }

        public void Backup(string filename)
        {
            Filename = filename;
            Backup();
        }

        public void Backup()
        {
            List<string> SQLs = new List<string>();

            string myCon = "";

            if (MySQLConnectionString.Length != 0)
                myCon = MySQLConnectionString;
            else
            {
                myCon = "server=" + myServer + ";user=" + myUser + ";password=" + myPassword + ";database=" + myDatabase + ";port=" + myPort + ";";
                if (myOther.Length != 0)
                    myCon += myOther;
            }
            MySqlConnection conn = new MySqlConnection(myCon);

            #region Get all tables' name in database
            string sqlcmd = "show tables;";
            DataTable dtTable = new DataTable();
            MySqlDataAdapter da = new MySqlDataAdapter(sqlcmd, conn);
            da.Fill(dtTable);
            #endregion

            #region Construct: Create Database SQL command
            
            DataTable dtDatabase = new DataTable();
            sqlcmd = "select database();";
            da = new MySqlDataAdapter(sqlcmd, conn);
            da.Fill(dtDatabase);
            string DatabaseName = dtDatabase.Rows[0][0] + "";
            
            if (DropAndRecreateDatabase)
            {
                SQLs.Add("drop database if exists `" + DatabaseName + "`;");
            }

            dtDatabase = new DataTable();
            sqlcmd = "show create database `" + DatabaseName + "`;";
            da = new MySqlDataAdapter(sqlcmd, conn);
            da.Fill(dtDatabase);
            SQLs.Add((dtDatabase.Rows[0][1] + "").Replace("CREATE DATABASE", "create database if not exists") + ";");
            SQLs.Add("use `" + DatabaseName + "`;");
            #endregion

            foreach (DataRow dr in dtTable.Rows)
            {
                string tablename = dr[0] + "";

                #region Backup Each Table's Structure
                if (DropAndRecreateTable)
                {
                    SQLs.Add("drop table if exists `" + tablename + "`;");
                }
                string sql2 = "show create table `" + tablename + "`;";
                DataTable dtCreateTable = new DataTable();
                MySqlDataAdapter da2 = new MySqlDataAdapter(sql2, conn);
                da2.Fill(dtCreateTable);
                string createTable = (dtCreateTable.Rows[0][1] + "").Replace("\n", string.Empty);
                SQLs.Add(createTable.Replace("CREATE TABLE", "create table if not exists") + ";");
                #endregion

                // Delete all rows in table

                SQLs.Add("delete from `" + tablename + "`;");

                #region Get all column's name in table
                DataTable dtColumn = new DataTable();
                string sql3 = "show columns from `" + tablename + "`";
                MySqlDataAdapter da3 = new MySqlDataAdapter(sql3, conn);
                da3.Fill(dtColumn);
                #endregion

                #region Get all rows in table
                DataTable dtRows = new DataTable();
                string sql4 = "select * from `" + tablename + "`";
                MySqlDataAdapter da4 = new MySqlDataAdapter(sql4, conn);
                da4.Fill(dtRows);
                #endregion

                #region Loop each row and construct sql INSERT command

                if (dtRows.Rows.Count != 0)
                {
                    StringBuilder sb = new StringBuilder();

                    if (Construct_SQL_In_One_Line_From_Same_Table)
                        sb.AppendFormat("insert into `" + tablename + "` value (");

                    for (int i = 0; i < dtRows.Rows.Count; i++)
                    {
                        if (!Construct_SQL_In_One_Line_From_Same_Table)
                            sb.AppendFormat("insert into `" + tablename + "` value (");

                        for (int j = 0; j < dtRows.Columns.Count; j++)
                        {
                            string datatype = dtRows.Columns[j].DataType.ToString();

                            string text = "";

                            DateTime dtime = DateTime.Now;
                            if (datatype == "System.DateTime")
                            {
                                if (DateTime.TryParse(dtRows.Rows[i][j] + "", out dtime))
                                {
                                    text = "'" + Convert.ToDateTime(dtRows.Rows[i][j]).ToString("yyyy-MM-dd HH:mm:ss") + "'";
                                }
                                else
                                    text = "null";
                            }
                            else if (dtRows.Rows[i][j] == DBNull.Value)
                                text = "null";
                            else
                            {
                                text = dtRows.Rows[i][j] + "";

                                // Escape special character for MySQL commands

                                text = text.Replace("\\", "\\\\");
                                text = text.Replace("'", "\\'");
                                text = "'" + text + "'";
                            }

                            sb.AppendFormat(text);
                            if (j + 1 != dtRows.Columns.Count)
                                sb.AppendFormat(",");
                        }

                        if (Construct_SQL_In_One_Line_From_Same_Table)
                        {
                            if (i + 1 != dtRows.Rows.Count)
                            {
                                sb.AppendFormat("),(");
                            }
                            else
                            {
                                sb.AppendFormat(");");
                            }
                        }
                        else
                        {
                            sb.AppendFormat(");");
                            SQLs.Add(sb.ToString());
                            sb = new StringBuilder();
                        }
                    }
                    if (Construct_SQL_In_One_Line_From_Same_Table)
                    {
                        SQLs.Add(sb.ToString());
                        sb = new StringBuilder();
                    }
                }
                #endregion
            }

            string[] output = null;
            output = new string[SQLs.Count];

            #region Encryption | Encrypt the output SQL text

            if (EncryptBackupFile)
            {
                XCryptEngine xe = new XCryptEngine();
                xe.InitializeEngine(XCryptEngine.AlgorithmType.Rijndael);
                xe.Key = EncryptionKey;

                for (int i = 0; i < SQLs.Count; i++)
                {
                    output[i] = xe.Encrypt(SQLs[i]);
                }
            }
            else
            {
                for (int i = 0; i < SQLs.Count; i++)
                {
                    output[i] = SQLs[i];
                }
            }

            #endregion

            File.WriteAllLines(Filename, output, Encoding.UTF8);
        }

        public void Restore(string filename)
        {
            Filename = filename;
            Restore();
        }

        public void Restore(string filename, bool Decryption, string EncryptionKEY)
        {
            Filename = filename;
            EncryptBackupFile = Decryption;
            EncryptionKey = EncryptionKEY;
            Restore();
        }

        public void Restore()
        {
            string myCon = "";

            if (MySQLConnectionString.Length != 0)
                myCon = MySQLConnectionString;
            else
            {
                myCon = "server=" + myServer + ";user=" + myUser + ";password=" + myPassword + ";port=" + myPort + ";";
                if (myOther.Length != 0)
                    myCon += myOther;
            }
            MySqlConnection conn = new MySqlConnection(myCon);

            conn.Open(); // Test connection
            conn.Close();

            string[] sqls = File.ReadAllLines(Filename, Encoding.UTF8);

            if (EncryptBackupFile)
            {
                XCryptEngine xe = new XCryptEngine();
                xe.InitializeEngine(XCryptEngine.AlgorithmType.Rijndael);
                xe.Key = EncryptionKey;
                for (int i = 0; i < sqls.Length; i++)
                {
                    sqls[i] = xe.Decrypt(sqls[i]);
                }
            }

            NewProgressForm();
            progressBar1.Maximum = sqls.Length;
            progressBar1.Value = 0;
            
            f.Show();

            MySqlCommand cmd = new MySqlCommand();
            cmd.Connection = conn;
            conn.Open();

            // Start Restoring the Database
            foreach (string s in sqls)
            {
                progressBar1.Value += 1;
                cmd.CommandText = s;
                cmd.ExecuteNonQuery();
            }

            f.Close();
        }

        void NewProgressForm()
        {
            progressBar1 = new ProgressBar();
            progressBar1.Location = new System.Drawing.Point(25, 25);
            progressBar1.Size = new System.Drawing.Size(150, 20);
            f = new Form();
            f.Size = new System.Drawing.Size(220, 120);
            f.Text = "Progress...";
            f.StartPosition = FormStartPosition.CenterScreen;
            f.Controls.Add(progressBar1);
            f.ShowIcon = false;
        }
    }
}

 

Last edited Sep 28, 2011 at 1:57 AM by adriancs, version 1