Restore MySQL Database using vb

This topic shows how to restore MySQL Database using vb programming.
Here you can restore any backup files of MySQL Database using vb programming.
Tools required to restore MySQL Database: ComboBox (cmbDatabase), Button1 (Button1), TextBox1 (txtExpImpPath), TextBox2 (txtWorkingDirectoryPath)

Design the form as shows in the figure to restore mysql database.

Restore MySQL Database using vb – Design Form

MySQL Database – Restore MySQL Database using vb

The MySQL Database powers the most demanding Web, E-commerce and Online Transaction Processing (OLTP) applications.
It is a fully integrated transaction-safe, ACID compliant database with full commit, rollback, crash recovery and row level locking capabilities.

MySQL delivers the ease of use, scalability, and performance that has made MySQL the world’s most popular open source database.
Some of the world’s most trafficked websites like Facebook, Google, ticketmaster, and eBay rely on MySQL for their business critical applications.
Website: http://www.mysql.com

Assign MySQL Database to ComboBox – Restore MySQL Database using vb

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 "
        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 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 "

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

Get the MySQL Connection – Restore MySQL Database using vb

Private Function MySQLNoDatabaseCon()
        strMySQL = ""
        strMySQL = "Driver={MySQL ODBC 3.51 Driver};option=0;port=3306;server= localhost;uid=root;password=root"
        Return strMySQL
    End Function

Private Function MySQLNoDatabaseCon()

strMySQL = ""

strMySQL = "Driver={MySQL ODBC 3.51 Driver};option=0;port=3306;server= localhost;uid=root;password=root"

Return strMySQL

End Function

Restore MySQL Database using vb – Complete Code

Imports System.IO
Imports System.Data.Odbc

Public Class Form1
    Dim strMySQL, myChk, selectQuery As String
    Dim dtseCt As Integer

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        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 "
        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 Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try

            'Database Validation

            Dim dtDtseChk As DataTable = chkDatabase()

            '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")

                Exit Sub
            End If
            txtExpImpPath.Text = 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(txtExpImpPath.Text, drive)

            Dim myProcess As New Process()
            myProcess.StartInfo.FileName = "cmd.exe"

            myProcess.StartInfo.UseShellExecute = False
            myProcess.StartInfo.WorkingDirectory = txtWorkingDirectoryPath.Text

            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.TABLES WHERE TABLE_SCHEMA = '" + cmbDatabase.Text + "'"
        Dim odbcAdptr As New OdbcDataAdapter(selectQuery, conOdbc)
        odbcAdptr.Fill(dt)

        Return dt

    End Function

    Private Function MySQLNoDatabaseCon()
        strMySQL = ""
        strMySQL = "Driver={MySQL ODBC 3.51 Driver};option=0;port=3306;server= localhost;uid=root;password=root"
        Return strMySQL
    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
            txtWorkingDirectoryPath.Text = tempWorkPath.Substring(0, len) + "bin"

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub

End Class

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

Imports System.IO

Imports System.Data.Odbc

Public Class Form1

Dim strMySQL, myChk, selectQuery As String

Dim dtseCt As Integer

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

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 "

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 Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Try

'Database Validation

Dim dtDtseChk As DataTable = chkDatabase()

'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")

Exit Sub

End If

txtExpImpPath.Text = 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(txtExpImpPath.Text, drive)

Dim myProcess As New Process()

myProcess.StartInfo.FileName = "cmd.exe"

myProcess.StartInfo.UseShellExecute = False

myProcess.StartInfo.WorkingDirectory = txtWorkingDirectoryPath.Text

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.TABLES WHERE TABLE_SCHEMA = '" + cmbDatabase.Text + "'"

Dim odbcAdptr As New OdbcDataAdapter(selectQuery, conOdbc)

odbcAdptr.Fill(dt)

Return dt

End Function

Private Function MySQLNoDatabaseCon()

strMySQL = ""

strMySQL = "Driver={MySQL ODBC 3.51 Driver};option=0;port=3306;server= localhost;uid=root;password=root"

Return strMySQL

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

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

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

End Sub

End Class

Rating: 0.0/10 (0 votes cast)

Rating: 0 (from 0 votes)

0November 12, 2012
Restore MySQL Database using C#
0November 12, 2012
List MySQL Database in C#
0November 12, 2012
Change MySQL Username Password
0November 12, 2012
Backup MySQL Database using C#
0November 12, 2012
List MySQL Database using vb

Alexey: That isnihgt's just what I've been looking for. Th...

Griselda: What would many of us do devoid of the brlliiant t...

Alejandra: My spouse and i ended up being rellay excited that...

microsoft: Heya i'm for the primary time here. I found this b...

Dave: Whoa, thnigs just got a whole lot easier....

Lina: Really informative blog post here my frined. I jus...

Setan: Your page is stunning! I have been wianntg to try ...

Devandra: Nice blog, just looking close to some blogs, aaepp...

Coding Angel

Page Rank

Subscribe

Topics

Categories

Recent posts

Most Shared Posts

Alexa Rank

Restore MySQL Database using vb

Design the form as shows in the figure to restore mysql database.

MySQL Database – Restore MySQL Database using vb

Assign MySQL Database to ComboBox – Restore MySQL Database using vb

Get the MySQL Connection – Restore MySQL Database using vb

Restore MySQL Database using vb – Complete Code

Related Posts:

Leave a Reply Cancel reply

Announcement

Recent Comments