Your Ads Here

Friday, 27 April 2012

MySql Database Backup using C#.NET


It shows how to backup MySql Databases using C#.NET.
keywords: mysql, sql, database, backup, tables, C Sharp, .NET

You have add following Controls
1) SaveFileDialog (sfd)
2) ComboBox (cmbDatabase)
3) TextBox (txtExpImpPath, txtWorkingDirectoryPath)

First load the MySql Database to the comboBox.

 
private void getDatabase()
        {
            //Declaration

            OdbcConnection conOdbc = new OdbcConnection();

            DataTable dt = new DataTable();


            //Connection String

            strMySQL = "";

            strMySQL = "Driver={MySQL ODBC 3.51 Driver};option=0;port=3306;server= localhost;uid=root;password=root";

            myChk = "";

            myChk = strMySQL;


            //Open Odbc Connection

            conOdbc = new OdbcConnection(myChk);

            conOdbc.Close();

            conOdbc.Open();


            selectQuery = "SELECT DISTINCT TABLE_SCHEMA FROM information_schema.TABLES ";

            OdbcDataAdapter odbcAdptr = new OdbcDataAdapter(selectQuery, conOdbc);

            odbcAdptr.Fill(dt);


            dtseCt = 0;

            cmbDatabase.Items.Clear();

            cmbDatabase.Items.Add("---Select---");


            while (dtseCt < dt.Rows.Count)
            {
                cmbDatabase.Items.Add(dt.Rows[dtseCt][0].ToString());

                dtseCt = dtseCt + 1;

            }

            cmbDatabase.SelectedIndex = 0;

        }

Connect the database using...
strMySQL = "";

            strMySQL = "Driver={MySQL ODBC 3.51 Driver};option=0;port=3306;server= localhost;uid=root;password=root";

 Here the Complete code for the Database Backup

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.VisualBasic;
using System.Diagnostics;
using System.IO;
using System.Data.Odbc;


namespace WindowsApplication1
{
    public partial class Form1 : Form
    {
        string strMySQL = null;
        string myChk = null;
        string selectQuery = null;

        int dtseCt = 0;


        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            getDatabase();
        }

        private void getDatabase()
        {
            //Declaration

            OdbcConnection conOdbc = new OdbcConnection();

            DataTable dt = new DataTable();


            //Connection String

            strMySQL = "";

            strMySQL = "Driver={MySQL ODBC 3.51 Driver};option=0;port=3306;server= localhost;uid=root;password=root";

            myChk = "";

            myChk = strMySQL;


            //Open Odbc Connection

            conOdbc = new OdbcConnection(myChk);

            conOdbc.Close();

            conOdbc.Open();


            selectQuery = "SELECT DISTINCT TABLE_SCHEMA FROM information_schema.TABLES ";

            OdbcDataAdapter odbcAdptr = new OdbcDataAdapter(selectQuery, conOdbc);

            odbcAdptr.Fill(dt);


            dtseCt = 0;

            cmbDatabase.Items.Clear();

            cmbDatabase.Items.Add("---Select---");


            while (dtseCt < dt.Rows.Count)
            {
                cmbDatabase.Items.Add(dt.Rows[dtseCt][0].ToString());

                dtseCt = dtseCt + 1;

            }

            cmbDatabase.SelectedIndex = 0;

        }

        private void gettingPath()
        {
            //Getting MySQL Path

            //Declaration

            OdbcConnection conOdbc = new OdbcConnection();

            DataTable dt = new DataTable();

            int len = 0;

            string tempWorkPath = "";

            try
            {
                //Connection String

                strMySQL = "";

                strMySQL = "Driver={MySQL ODBC 3.51 Driver};option=0;port=3306;server= localhost;uid=root;password=root";

                myChk = strMySQL;


                //Open Odbc Connection

                conOdbc = new OdbcConnection(myChk);

                conOdbc.Close();

                conOdbc.Open();


                selectQuery = "select @@datadir";

                OdbcDataAdapter odbcAdptr = new OdbcDataAdapter(selectQuery, conOdbc);

                odbcAdptr.Fill(dt);


                tempWorkPath = dt.Rows[0][0].ToString();

                len = tempWorkPath.Length  - 5;

                txtWorkingDirectoryPath.Text = tempWorkPath.Substring(0, len) + "bin";



            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);

            }

        }

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {

                //Getting Path

                gettingPath();


                //Save Dialog Box

                sfd.Filter = "SQL Files |.sql";

                sfd.DefaultExt = ".sql";

                DialogResult = sfd.ShowDialog();


                if (DialogResult == System.Windows.Forms.DialogResult.Cancel)
                {
                    MessageBox.Show("Backup Creation Failed");

                    return;

                }

                txtExpImpPath.Text = sfd.FileName;


                //Getting Drives in System

                string[] drives = Environment.GetLogicalDrives();

                string drive = drives[1] + "Temp.sql";


                //Do Events

                Application.DoEvents();


                //Processing


                Process myProcess = new Process();

                myProcess.StartInfo.FileName = "cmd.exe";

                myProcess.StartInfo.UseShellExecute = false;

                myProcess.StartInfo.WorkingDirectory = txtWorkingDirectoryPath.Text;

                myProcess.StartInfo.RedirectStandardInput = true;

                myProcess.StartInfo.RedirectStandardOutput = true;

                myProcess.Start();

                StreamWriter myStreamWriter = myProcess.StandardInput;

                StreamReader mystreamreader = myProcess.StandardOutput;

                myStreamWriter.WriteLine("mysqldump --routines -u root -proot " + cmbDatabase.Text + " -r " + drive + "");

                myStreamWriter.Close();

                myProcess.WaitForExit();

                myProcess.Close();


                //Copying File from user selected path to System Created Path

                File.Move(drive, txtExpImpPath.Text);


                MessageBox.Show("Backup Created Successfully");

            }
            catch (Exception ex)
            {
                MessageBox.Show("Backup Creation Failed");

            }

        }
    }
}


Name the file to backup


Backup created successfully


Backup file views like this

 Note: If you have any suggestions or new topic, please free to contact us
Email: sivodayatech@dotnetdevelopertool.com

No comments:

Post a Comment