Displaying and Dropping the MySQL Database Tables Structures using Visual Basic.Net
Submitted by joken on Thursday, October 10, 2013 - 16:24.
This tutorial is a continuation of our our previous topic called “Creating MySQL Database tables using Visual Basic. Net”. This time, we're going to focus on displaying and dropping of MySQL Database Tables using Visual Basic.Net.
In this application, we need to add two buttons and a datagridview. Then, rename the first button as “btndescribe” and change the text property to “Describe”, and the other button change the name to “btndrop” and the text property to “Drop”. Next, for the Datagridview1 change the name as “dtgstructure” and place it over the first datagridview.
Take note folks, the data will be lost and could not be recovered after deleting a table. Although it is very easy to drop an existing MySQL table, but you need to be very careful while deleting any existing table.
This time, let’s start adding functionality to our “Describe” button. To do this, add the following code:
Then we can test our application by pressing “F5”. Then after clicking the “Describe” button the expected output will look like as shown below.
Next, for the “Drop” button. Add the following code.
This code will drop the existing table in any database.
And here’s all the code use for this application.
After reviewing all the codes above, you can now test your application by pressing “F5”.
- Private Sub btndescribe_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndescribe.Click
- txttblName.ReadOnly = True
- btnAddtbl.Visible = True
- btnDroptbl.Visible = True
- btnCreateTbl.Visible = False
- dtgstructure.BringToFront()
- Dim sql As String = "DESC " & cbdb.Text & "." & cbtable.Text
- Dim publictable As New DataTable
- Try
- 'bind the connection and query
- With cmd
- .Connection = con
- .CommandText = sql
- End With
- da.SelectCommand = cmd
- da.Fill(publictable)
- ' publictable.Rows.Add("Please Select...")
- dtgstructure.DataSource = publictable
- ' dtgrd.Columns(1).Visible = False
- da.Dispose()
- Catch ex As Exception
- End Try
- con.Clone()
- End Sub

- Private Sub btnDroptbl_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDroptbl.Click
- Dim sql As String = "DROP TABLE " & cbdb.Text & "." & cbtable.Text
- Dim result As Integer
- Try
- con.Open()
- With cmd
- .Connection = con
- .CommandText = sql
- result = cmd.ExecuteNonQuery
- If result > 0 Then
- Else
- With Me
- .txttblName.ReadOnly = True
- .btnCreateTbl.Visible = False
- .btnAddtbl.Visible = True
- .btnDroptbl.Visible = True
- .dtgStructNewTbl.SendToBack()
- dtgstructure.Columns.Clear()
- End With
- End If
- End With
- Form1_Load(sender, e)
- Catch ex As Exception
- End Try
- Call Form1_Load(sender, e)
- End Sub
- Imports MySql.Data.MySqlClient
- Public Class Form1
- 'Represents an SQL statement or stored procedure to execute against a data source.
- Dim cmd As New MySqlCommand
- Dim da As New MySqlDataAdapter
- Public total As Integer
- Dim publictable As New DataTable
- 'declare conn as connection and it will now a new connection because
- 'it is equal to Getconnection Function
- Dim con As MySqlConnection = jokenconn()
- Public Function jokenconn() As MySqlConnection
- Return New MySqlConnection("server=localhost;user id=root;password=;database=")
- End Function
- Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
- GroupBox1.Text = "Create new table on database "
- Dim sql As String = "SHOW DATABASES"
- Dim publictable As New DataTable
- Try
- 'bind the connection and query
- With cmd
- .Connection = con
- .CommandText = sql
- End With
- da.SelectCommand = cmd
- da.Fill(publictable)
- With cbdb
- .DataSource = publictable
- .DisplayMember = "Database"
- .ValueMember = "Database"
- End With
- da.Dispose()
- Catch ex As Exception
- End Try
- con.Clone()
- End Sub
- Private Sub btntest_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btntest.Click
- GroupBox1.Text = "Create new table on database " & cbdb.Text
- Dim sql As String = "SHOW TABLES FROM " & cbdb.Text
- Dim publictable As New DataTable
- Try
- 'bind the connection and query
- With cmd
- .Connection = con
- .CommandText = sql
- End With
- da.SelectCommand = cmd
- da.Fill(publictable)
- ' publictable.Rows.Add("Please Select...")
- With cbtable
- .DataSource = publictable
- .DisplayMember = "Tables_in_" & cbdb.Text
- .ValueMember = "Tables_in_" & cbdb.Text
- End With
- ' dtgrd.Columns(1).Visible = False
- da.Dispose()
- Catch ex As Exception
- End Try
- con.Clone()
- End Sub
- Private Sub btnAddtbl_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddtbl.Click
- txttblName.ReadOnly = False
- btnCreateTbl.Visible = True
- btnAddtbl.Visible = False
- btnDroptbl.Visible = False
- dtgstructure.SendToBack()
- End Sub
- Private Sub btnCreateTbl_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreateTbl.Click
- Dim field As String
- Dim type As String
- Dim nlenght As Integer
- Dim nNull As String
- Dim nIndex As String
- Dim nAI As String
- Dim alltxt As String
- Dim result As Integer
- Dim tblAndDB As String = cbdb.Text & "." & txttblName.Text
- Dim TBLCREATE As String = "CREATE TABLE " & tblAndDB & " ( "
- For i As Integer = 0 To Me.dtgStructNewTbl.Rows.Count - 2
- ' field.Add(Me.dtgStructNewTbl.Rows(i).Cells("n_field").Value)
- field = Me.dtgStructNewTbl.Rows(i).Cells("n_field").Value
- type = Me.dtgStructNewTbl.Rows(i).Cells("n_type").Value
- nlenght = Me.dtgStructNewTbl.Rows(i).Cells("n_lenght").Value
- nNull = Me.dtgStructNewTbl.Rows(i).Cells("n_null").Value
- nIndex = Me.dtgStructNewTbl.Rows(i).Cells("n_index").Value
- nAI = Me.dtgStructNewTbl.Rows(i).Cells("n_ai").Value
- alltxt += field & " " & type & "(" & nlenght & ")" & " " & nNull & " " & nIndex & " " & nAI & "," & vbNewLine
- Next
- Try
- 'it removes all the newline and whitespaces
- alltxt = alltxt.Substring(0, alltxt.Length - Environment.NewLine.Length)
- Dim cleanText As String
- 'it removes the the last comma ","
- cleanText = alltxt.Remove(alltxt.Length - 1)
- Dim finalText As String
- 'combination of finalText with table name and Mysql ENGINE
- finalText = TBLCREATE & cleanText & ")ENGINE = MYISAM ;"
- con.Open()
- With cmd
- .Connection = con
- .CommandText = finalText
- result = cmd.ExecuteNonQuery
- If result > 0 Then
- Else
- With Me
- .txttblName.ReadOnly = True
- .btnCreateTbl.Visible = False
- .btnAddtbl.Visible = True
- .btnDroptbl.Visible = True
- .dtgStructNewTbl.SendToBack()
- '.dtgStructNewTbl.Columns.Clear()
- End With
- End If
- End With
- Form1_Load(sender, e)
- Catch ex As Exception
- End Try
- End Sub
- Private Sub btndescribe_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndescribe.Click
- txttblName.ReadOnly = True
- btnAddtbl.Visible = True
- btnDroptbl.Visible = True
- btnCreateTbl.Visible = False
- dtgstructure.BringToFront()
- Dim sql As String = "DESC " & cbdb.Text & "." & cbtable.Text
- Dim publictable As New DataTable
- Try
- 'bind the connection and query
- With cmd
- .Connection = con
- .CommandText = sql
- End With
- da.SelectCommand = cmd
- da.Fill(publictable)
- ' publictable.Rows.Add("Please Select...")
- dtgstructure.DataSource = publictable
- ' dtgrd.Columns(1).Visible = False
- da.Dispose()
- Catch ex As Exception
- End Try
- con.Clone()
- End Sub
- Private Sub btnDroptbl_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDroptbl.Click
- Dim sql As String = "DROP TABLE " & cbdb.Text & "." & cbtable.Text
- Dim result As Integer
- Try
- con.Open()
- With cmd
- .Connection = con
- .CommandText = sql
- result = cmd.ExecuteNonQuery
- If result > 0 Then
- Else
- With Me
- .txttblName.ReadOnly = True
- .btnCreateTbl.Visible = False
- .btnAddtbl.Visible = True
- .btnDroptbl.Visible = True
- .dtgStructNewTbl.SendToBack()
- dtgstructure.Columns.Clear()
- End With
- End If
- End With
- Form1_Load(sender, e)
- Catch ex As Exception
- End Try
- Call Form1_Load(sender, e)
- End Sub
- End Class
Add new comment
- 243 views