List MySQL Databases and Tables

 For this program , we need mainly 2 comboBox, and 2 buttons.

One combox for listing Database's Name named "cmbDatabase" and other for listing Tables Name named "cmbTables".

Imports System.Data.Odbc

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

        cmbDatabase.DropDownStyle = ComboBoxStyle.DropDownList

        cmbTables.DropDownStyle = ComboBoxStyle.DropDownList

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System. EventArgs) Handles Button1.Click

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

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

        Dim odbcAdptr As New OdbcDataAdapter(selectQuery, conOdbc)

        odbcAdptr.Fill(dt)

        dtseCt = 0

        cmbTables.Items.Clear()

        'cmbTables.Items.Add("---Select---")

        While dtseCt < dt.Rows.Count

            cmbTables.Items.Add(dt.Rows(dtseCt)(0).ToString())

            dtseCt = dtseCt + 1

        End While

        cmbTables.SelectedIndex = 0

End Sub

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

Backup of DataBase / Particular Table of Database of MySQL

This program will only back up the database, if you given Database Name  only. In addition if you give Table Name in a Database,  It will back up particular Table of Database. Controls needed is 2 TextBoxes, 1 Button and 1 Savefiledialog control.

Imports System.IO

Imports System.Data.Odbc

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

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        If TextBox1.Text = "" Then

            MsgBox("Enter Database Name for Backup creation")

            Exit Sub

        End If

        gettingPath()

        'Save Dialog Box

        sfd.Filter = "SQL Files |.sql"

        sfd.DefaultExt = ".sql"

        sfd.Title = "Select location to save Backup file"

        DialogResult = sfd.ShowDialog()

        If DialogResult = Windows.Forms.DialogResult.Cancel Then

            MessageBox.Show("Backup Creation Failed")

            Exit Sub

        End If

        ExpImpPath = sfd.FileName

        'Getting Drives in System

        Dim drives As String() = Environment.GetLogicalDrives()

        Dim drive As String = drives(1) + "Temp.sql"

        'Do Events

        Application.DoEvents()

        'Processing

        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

        If TextBox2.Text = "" Then

            'Backup of Whole Database.

            myStreamWriter.WriteLine("mysqldump --routines -u root -proot " + TextBox1.Text + " -r " + drive + "")

        Else

            'Backup of particular Table Name of Database.

            myStreamWriter.WriteLine("mysqldump --routines -u root -proot " + TextBox1.Text + " " + TextBox2.Text + " -r " + drive + "")

            'TextBox1 ---> Database Name, TextBox2 ---> Table Name of database of TextBox1

        End If

        myStreamWriter.Close()

        myProcess.WaitForExit()

        myProcess.Close()

        'Copying File from user selected path to System Created Path

        File.Move(drive, ExpImpPath)

        MessageBox.Show("Backup Created Successfully")

End Sub

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 = "Driver={MySQL ODBC 3.51 Driver};option=0;port=3306;server=  localhost;uid=root;password=root"

            '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