VB NET Example MySQL BackUp & Restore Simple Application 2017

Import refernce MySQL.Data

pada menu project > add reference > extensions > MySQL.Data then OK

Import Namespace kedalam project

Imports MySql.Data.MySqlClient
Imports System.IO

Deklarasikan variable yang dibutuhkan

    Dim SqlConnection As MySqlConnection
    Dim dt As New DataTable
    Dim cmd As String
    Dim dtseCt As Integer
    Dim da As MySqlDataAdapter

Source Code Koneksi Database

    Public Sub koneksi()
        Try
            SqlConnection = New MySqlConnection("Server=" & TextBox1.Text & "; " _
                                                + "user id=" & TextBox2.Text & ";" _
                                                + "password=" & TextBox3.Text & ";")
            If SqlConnection.State = ConnectionState.Closed Then

                SqlConnection.Open() ' open our connections
            End If
        Catch ex As Exception
            MsgBox("Connection Filed !")
        End Try
    End Sub

Source Code Tombol Konek (Button1)

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Try
            koneksi() ' open our connection
            ' create query to show list of our database into combobox
            cmd = "SELECT DISTINCT TABLE_SCHEMA FROM information_schema.TABLES"
            da = New MySqlDataAdapter(cmd, SqlConnection)
            da.Fill(dt)
            dtseCt = 0

            ' enabling the list of database in the combobox
            ComboBox1.Enabled = True
            ComboBox1.Items.Clear()
            ComboBox1.Items.Add("== Select Database ==")

            While dtseCt < dt.Rows.Count

                ' add database list into combobox
                ComboBox1.Items.Add(dt.Rows(dtseCt)(0).ToString())
                dtseCt = dtseCt + 1
                ' lets try
                'sorry for it, just add the code like this

            End While
            ComboBox1.SelectedIndex = 0
            Button1.Enabled = False
            Button2.Enabled = True
            Button3.Enabled = True
            'close our connections
            SqlConnection.Clone()
            dt.Dispose()
            da.Dispose()

        Catch ex As Exception
            MsgBox("Connection Filed!")
        End Try
    End Sub

Source Code Backup Database MySQL

    Private Sub Button2_Click(sender As Object, ByVal e As EventArgs) Handles Button2.Click
        ' we will backup a mysql database and save it into our local server
        Dim DbFile As String
        Try
            ' create svaFileDialog and OpenFileDialog Component to our project
            SaveFileDialog1.Filter = "SQL Dump File (*.sql)|*.sql|All files (*.*)|*.*"
            SaveFileDialog1.FileName = "Database Backup " + DateTime.Now.ToString("yyyy-MM-dd HH-mm-ss") + ".sql"
            If SaveFileDialog1.ShowDialog = DialogResult.OK Then

                koneksi() ' open our connections
                DbFile = SaveFileDialog1.FileName
                Dim BackupProccess As New Process
                BackupProccess.StartInfo.FileName = "cmd.exe"
                BackupProccess.StartInfo.UseShellExecute = False
                BackupProccess.StartInfo.WorkingDirectory = "C:\xampp\mysql\bin\"
                BackupProccess.StartInfo.RedirectStandardInput = True
                BackupProccess.StartInfo.RedirectStandardOutput = True
                BackupProccess.Start()

                Dim BackupStream As StreamWriter = BackupProccess.StandardInput
                Dim myStreamReader As StreamReader = BackupProccess.StandardOutput
                BackupStream.WriteLine("mysqldump --user=" & TextBox2.Text & " _
                + "" --password=" & TextBox3.Text & " -h " & TextBox1.Text & " " _
                + "" & ComboBox1.Text & " > """ + DbFile + """")

                BackupStream.Close()
                BackupProccess.WaitForExit()
                BackupProccess.Close()
                SqlConnection.Close()
                MsgBox("Backup your MySQL database Created Successfully!", MsgBoxStyle.Information, "Backup MySql Database")
            End If

        Catch ex As Exception
            MsgBox("Nothing to do!")
        End Try
    End Sub

Source Code Restore Database MySQL

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        ' now we will create for restore our database, just copy the source for backup our database before
        Dim DbFile As String
        Try
            ' create svaFileDialog and OpenFileDialog Component to our project
            OpenFileDialog1.Filter = "SQL Dump File (*.sql)|*.sql|All files (*.*)|*.*"
            If OpenFileDialog1.ShowDialog = DialogResult.OK Then

                koneksi() ' open our connections
                DbFile = OpenFileDialog1.FileName
                Dim BackupProccess As New Process
                BackupProccess.StartInfo.FileName = "cmd.exe"
                BackupProccess.StartInfo.UseShellExecute = False
                BackupProccess.StartInfo.WorkingDirectory = "C:\xampp\mysql\bin\"
                BackupProccess.StartInfo.RedirectStandardInput = True
                BackupProccess.StartInfo.RedirectStandardOutput = True
                BackupProccess.Start()

                Dim BackupStream As StreamWriter = BackupProccess.StandardInput
                Dim myStreamReader As StreamReader = BackupProccess.StandardOutput
                BackupStream.WriteLine("mysql --user=" & TextBox2.Text & " --password=" & TextBox3.Text & " -h " & TextBox1.Text & " " & ComboBox1.Text & " < """ + DbFile + """")

                BackupStream.Close()
                BackupProccess.WaitForExit()
                BackupProccess.Close()
                SqlConnection.Close()
                MsgBox("Restore your MySQL database Successfully!", MsgBoxStyle.Information, "Restore MySql Database")
            End If

        Catch ex As Exception
            MsgBox("Nothing to do!")
        End Try
    End Sub