Wednesday 18 January 2012

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

No comments:

Post a Comment