Backup/Restore with OpenFile and SaveFile

Oct 14, 2014 at 11:41 AM
Edited Oct 14, 2014 at 12:45 PM
I'm currently making a project for my thesis and I've decided to add a MySQL Backup/Restore feature for my system.

I tried to edit the code (posted on this site) for MySQL backup/restore and added an OpenFile and SaveFile so that the file won't be in a fixed path but rather, you'll be able to choose an SQL file to be restored and the path where the backup file will be created.
    Private Sub la_backup_Click(sender As Object, e As EventArgs) Handles la_backup.Click
        Try
            If txt_mysql_host.BackColor = Color.LightGreen = False Then
                MessageBox.Show("Please connect to a MySQL database first.", "Login", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Else
                Dim ConString As String = "SERVER = '" & ServerMySQL & "' ; PORT = '" & PortMySQL & "' ; USERID = '" & UsernameMySQL & "'; PASSWORD = '" & PassMySQL & "'; DATABASE = '" & DBNameMySQL & "'"
                Dim BackupFile As String
                Dim fileSaver As SaveFileDialog = New SaveFileDialog()
                fileSaver.Filter = "SQL files | *.sql"

                If fileSaver.ShowDialog() = Windows.Forms.DialogResult.OK Then
                    BackupFile = fileSaver.FileName

                    Using sConnection As New MySqlConnection(ConString)
                        Using sqlCommand As New MySqlCommand()
                            Using sqlBackup As New MySqlBackup(sqlCommand)
                                sqlCommand.Connection = sConnection
                                sConnection.Open()
                                sqlBackup.ExportToFile(BackupFile)
                                MessageBox.Show("MySQL database backup has been created.", "MySQL Backup", MessageBoxButtons.OK, MessageBoxIcon.Information)
                                sConnection.Close()
                            End Using
                        End Using
                    End Using
                Else
                    MessageBox.Show("No backup file was created.", "MySQL Restore", MessageBoxButtons.OK, MessageBoxIcon.Information)
                End If
            End If

        Catch ex As Exception
            MsgBox(ex.ToString())
        Finally
            sConnection.Close()
        End Try

    End Sub
    Private Sub la_restore_Click(sender As Object, e As EventArgs) Handles la_restore.Click
        Try
            If txt_mysql_host.BackColor = Color.LightGreen = False Then
                MessageBox.Show("Please connect to a MySQL database first.", "Login", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            Else
                Dim ConString As String = "SERVER = '" & ServerMySQL & "' ; PORT = '" & PortMySQL & "' ; USERID = '" & UsernameMySQL & "'; PASSWORD = '" & PassMySQL & "'; DATABASE = '" & DBNameMySQL & "'"
                Dim RestoreFile As String

                Dim fileOpener As OpenFileDialog = New OpenFileDialog()
                fileOpener.Filter = "SQL files | *.sql"
                If fileOpener.ShowDialog() = Windows.Forms.DialogResult.OK Then
                    RestoreFile = fileOpener.FileName
                    Using sConnection As New MySqlConnection(ConString)
                        Using sqlCommand As New MySqlCommand()
                            Using sqlBackup As New MySqlBackup(sqlCommand)
                                sqlCommand.Connection = sConnection
                                sConnection.Open()
                                sqlBackup.ImportFromFile(RestoreFile)
                                MessageBox.Show("MySQL database has been restored.", "MySQL Restore", MessageBoxButtons.OK, MessageBoxIcon.Information)
                                sConnection.Close()
                            End Using
                        End Using
                    End Using
                Else
                    MessageBox.Show("No restore file was chosen.", "MySQL Restore", MessageBoxButtons.OK, MessageBoxIcon.Information)
                End If
            End If

        Catch ex As Exception
            MsgBox(ex.ToString())
        Finally
            sConnection.Close()
        End Try
    End Sub
I only messed around a bit with it for a couple of minutes so I would like to asked if there's a better way to do this since we will be graded based on the efficiency of our codes.

Thanks in advance.
Coordinator
Oct 14, 2014 at 2:03 PM
Edited Oct 14, 2014 at 2:07 PM
I can share my comments:

~~~~~~~~~
For checking connection string, you can extract that part as another sub method. That block of code can be used in both Export and Import.
You can write new method something like CheckMySqlConnectionString() and return a value of TRUE of FALSE.
IF TRUE means the connection string is valid.

~~~~~~~~~
Rewrite the connection checking logic. Don't use Color.LightGreen.
Inside the new method of CheckMySqlConnectionString(), execute this:
sConnection.Open()
sConnection.Close()
then return TRUE indicates the connection string is valid.
use TRY CATCH to trap error which indicates the connection string is not valid, then return a FALSE value.

~~~~~~~~~
Extract this part to another new method,
Dim ConString As String = "SERVER = '" & ServerMySQL & "' ; PORT = '" & PortMySQL & "' ; USERID = '" & UsernameMySQL & "'; PASSWORD = '" & PassMySQL & "'; DATABASE = '" & DBNameMySQL & "'"
you can call it GetConnectionString()
use String.Format to join the strings. the reason is String.Format use less memory, string joining use more memory.
GetConnectionString() can be reused in both Backup & Restore

~~~~~~~~~
The reason for extracting method:
  1. easier code maintenance. next time when you want to update the code, you only have to modify once at the extract method.
  2. more readable & easier understandable by human
~~~~~~~~~
My last sharing:
How your code will be graded in college/university has a high chance totally different in real business production.
So, whatever your professor tells you how you should write your code. Follow it in doing his assignments to get good grade, but whatever the guideline is,
it's just another person's personal flavor, not absolute.