Restore Database using Backup file

 

For this program, we need combobox,  openfiledialog control and 2 buttons. One for listing database names and another for restoring database.

You can select database name wants to restore, from the combobox if it is created earlier. If the database is new , you can type the database name in combobox field.  Then press Restore Button --- > select backup file --- > press   OK.

Imports System.Data.Odbc

Imports System.IO

Dim myChk, strMySQL, selectQuery, WorkingDirectoryPath, ExpImpPath As String

Dim dt As New DataTable

Dim dtseCt As Integer

Private Sub btnClickHere_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClickHere.Click

        getDatabase()

    End Sub

Private Sub getDatabase()

        'Declaration

        Dim conOdbc As New OdbcConnection

        Dim dt As New DataTable

        'Connection String

        myChk = ""

        myChk = MySQLNoDatabaseCon()

        'Open Odbc Connection

        conOdbc = New OdbcConnection(myChk)

        conOdbc.Close()

        conOdbc.Open()

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

        selectQuery = "select  schema_name from information_schema.schemata"

        Dim odbcAdptr As 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

        End While

        cmbDatabase.SelectedIndex = 0

    End Sub

Private Sub btnRestore_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRestore.Click

        Try

            'Database Validation

            If cmbDatabase.Text = "---Select---" Then

                MessageBox.Show("Enter the Database")

                Exit Sub

            Else

                Dim dtDtseChk As DataTable = chkDatabase()

                If dtDtseChk.Rows.Count = 0 Then

                    createDatabase()

                End If

            End If

            Dim dr As DialogResult = MessageBox.Show("Restoring Database will loss the previous Data's. Do you need to Restore?")

            If dr = Windows.Forms.DialogResult.No Then

                Exit Sub

            End If

            'Getting Drives in System

            Dim drives As String()

            Dim drive As String = ""

            'Do Events

            Application.DoEvents()

            'Getting Path

            gettingPath()

            ofd.FileName = ""

            ofd.ShowDialog()

            If DialogResult = Windows.Forms.DialogResult.Cancel Then

                MessageBox.Show("Restoring Database Failed")

                createTableSts = 0

                Exit Sub

            End If

            ExpImpPath = ofd.FileName

            'Getting Drives in System

            drives = Environment.GetLogicalDrives()

            drive = drives(1) + "Temp.sql"

            'Copying File from user selected path to System Created Path

            File.Copy(ExpImpPath, drive)

            'Starting new process

            Dim myProcess As New Process()

            myProcess.StartInfo.FileName = "cmd.exe"

            myProcess.StartInfo.UseShellExecute = False

            myProcess.StartInfo.WorkingDirectory = WorkingDirectoryPath

            myProcess.StartInfo.RedirectStandardInput = True

            myProcess.StartInfo.RedirectStandardOutput = True

            myProcess.Start()

            Dim myStreamWriter As StreamWriter = myProcess.StandardInput

            Dim mystreamreader As StreamReader = myProcess.StandardOutput

            myStreamWriter.WriteLine("mysql -u root -proot " + cmbDatabase.Text + " < " + drive + "")

            myStreamWriter.Close()

            myProcess.WaitForExit()

            myProcess.Close()

            MessageBox.Show("Database Restored Successfully")

            'Deleting Temp File from user selected path to System Created Path

            File.Delete(drive)

        Catch ex As Exception

            MessageBox.Show("Restoring Database Failed")

        End Try

    End Sub

 

Private Function chkDatabase()

        'Declaration

        Dim conOdbc As New OdbcConnection

        Dim dt As New DataTable

        'Connection String

        myChk = ""

        myChk = MySQLNoDatabaseCon()

        'Open Odbc Connection

        conOdbc = New OdbcConnection(myChk)

        conOdbc.Close()

        conOdbc.Open()

        selectQuery = "SELECT * FROM information_schema.schemata WHERE schema_name = '" + cmbDatabase.Text + "'"

        Dim odbcAdptr As New OdbcDataAdapter(selectQuery, conOdbc)

        odbcAdptr.Fill(dt)

        Return dt

    End Function

Private Sub gettingPath()

        'Getting MySQL Path

        'Declaration

        Dim conOdbc As New OdbcConnection

        Dim dt As New DataTable

        Dim len As Integer

        Dim tempWorkPath As String = ""

        Try

            'Connection String

            myChk = MySQLNoDatabaseCon()

            'Open Odbc Connection

            conOdbc = New OdbcConnection(myChk)

            conOdbc.Close()

            conOdbc.Open()

            selectQuery = "select @@datadir"

            Dim odbcAdptr As New OdbcDataAdapter(selectQuery, conOdbc)

            odbcAdptr.Fill(dt)

            tempWorkPath = dt.Rows(0)(0).ToString()

            len = tempWorkPath.Length() - 5

            WorkingDirectoryPath = tempWorkPath.Substring(0, len) + "bin"

        Catch ex As Exception

            MessageBox.Show(ex.Message)

        End Try

    End Sub

Private Sub createDatabase()

        Try

            Dim insertQuery As String

            If cmbDatabase.Text = "---Select---" Then

                MessageBox.Show("Enter Database Name")

                Exit Sub

            End If

            'Declaration

            Dim conOdbc As New OdbcConnection

            Dim dt As New DataTable

            'Connection String

            myChk = MySQLNoDatabaseCon()

            'Open Odbc Connection

            conOdbc = New OdbcConnection(myChk)

            conOdbc.Close()

            conOdbc.Open()

            'Query building

            insertQuery = "CREATE DATABASE IF NOT EXISTS " + cmbDatabase.Text + ""

            Dim odbcCmd As New OdbcCommand(insertQuery, conOdbc)

            odbcCmd.ExecuteNonQuery()

            'MessageBox.Show("Database Created Successfully")

        Catch ex As Exception

            MessageBox.Show(ex.Message)

        End Try

    End Sub