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
