Monday 23 January 2012

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




2 comments:

  1. subelo en un .zip que me marca error T_T
    pero muchas gracias por el codigo me a dado una idea XD

    ReplyDelete
  2. Please send me the working source.The above code gives connection string property error.

    ReplyDelete