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: 
  1.     Private Sub btndescribe_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndescribe.Click
  2.  
  3.         txttblName.ReadOnly = True
  4.         btnAddtbl.Visible = True
  5.         btnDroptbl.Visible = True
  6.         btnCreateTbl.Visible = False
  7.         dtgstructure.BringToFront()
  8.  
  9.         Dim sql As String = "DESC " & cbdb.Text & "." & cbtable.Text
  10.         Dim publictable As New DataTable
  11.         Try
  12.  
  13.             'bind the connection and query
  14.             With cmd
  15.                 .Connection = con
  16.                 .CommandText = sql
  17.             End With
  18.  
  19.             da.SelectCommand = cmd
  20.             da.Fill(publictable)
  21.             '  publictable.Rows.Add("Please Select...")
  22.             dtgstructure.DataSource = publictable
  23.             ' dtgrd.Columns(1).Visible = False
  24.             da.Dispose()
  25.  
  26.         Catch ex As Exception
  27.             MsgBox(ex.Message)
  28.  
  29.         End Try
  30.         con.Clone()
  31.  
  32.     End Sub
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. 
  1. Private Sub btnDroptbl_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDroptbl.Click
  2.         Dim sql As String = "DROP TABLE " & cbdb.Text & "." & cbtable.Text
  3.         Dim result As Integer
  4.         Try
  5.             con.Open()
  6.             With cmd
  7.                 .Connection = con
  8.                 .CommandText = sql
  9.                 result = cmd.ExecuteNonQuery
  10.                 If result > 0 Then
  11.                     MsgBox("Error in dropping Field!")
  12.                 Else
  13.                     MsgBox(cbtable.Text & " has Successfully dropped!")
  14.                     With Me
  15.                         .txttblName.ReadOnly = True
  16.                         .btnCreateTbl.Visible = False
  17.                         .btnAddtbl.Visible = True
  18.                         .btnDroptbl.Visible = True
  19.                         .dtgStructNewTbl.SendToBack()
  20.                         dtgstructure.Columns.Clear()
  21.                     End With
  22.  
  23.  
  24.                 End If
  25.             End With
  26.  
  27.  
  28.             Form1_Load(sender, e)
  29.  
  30.         Catch ex As Exception
  31.             MsgBox(ex.Message)
  32.         End Try
  33.         con.Close()
  34.         Call Form1_Load(sender, e)
  35.  
  36.  
  37.     End Sub
And here’s all the code use for this application. 
  1. Imports MySql.Data.MySqlClient
  2. Public Class Form1
  3.     'Represents an SQL statement or stored procedure to execute against a data source.
  4.     Dim cmd As New MySqlCommand
  5.     Dim da As New MySqlDataAdapter
  6.     Public total As Integer
  7.     Dim publictable As New DataTable
  8.     'declare conn as connection and it will now a new connection because 
  9.     'it is equal to Getconnection Function
  10.     Dim con As MySqlConnection = jokenconn()
  11.  
  12.     Public Function jokenconn() As MySqlConnection
  13.         Return New MySqlConnection("server=localhost;user id=root;password=;database=")
  14.     End Function
  15.     Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  16.         GroupBox1.Text = "Create new table on database "
  17.         Dim sql As String = "SHOW DATABASES"
  18.         Dim publictable As New DataTable
  19.         Try
  20.  
  21.             'bind the connection and query
  22.             With cmd
  23.                 .Connection = con
  24.                 .CommandText = sql
  25.             End With
  26.  
  27.             da.SelectCommand = cmd
  28.             da.Fill(publictable)
  29.  
  30.             With cbdb
  31.                 .DataSource = publictable
  32.                 .DisplayMember = "Database"
  33.                 .ValueMember = "Database"
  34.             End With
  35.  
  36.             da.Dispose()
  37.  
  38.         Catch ex As Exception
  39.             MsgBox(ex.Message)
  40.  
  41.         End Try
  42.         con.Clone()
  43.  
  44.  
  45.     End Sub
  46.  
  47.     Private Sub btntest_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btntest.Click
  48.         GroupBox1.Text = "Create new table on database " & cbdb.Text
  49.         Dim sql As String = "SHOW TABLES FROM " & cbdb.Text
  50.         Dim publictable As New DataTable
  51.         Try
  52.  
  53.             'bind the connection and query
  54.             With cmd
  55.                 .Connection = con
  56.                 .CommandText = sql
  57.             End With
  58.  
  59.             da.SelectCommand = cmd
  60.             da.Fill(publictable)
  61.             '  publictable.Rows.Add("Please Select...")
  62.             With cbtable
  63.                 .DataSource = publictable
  64.                 .DisplayMember = "Tables_in_" & cbdb.Text
  65.                 .ValueMember = "Tables_in_" & cbdb.Text
  66.  
  67.             End With
  68.             ' dtgrd.Columns(1).Visible = False
  69.             da.Dispose()
  70.  
  71.         Catch ex As Exception
  72.             MsgBox(ex.Message)
  73.  
  74.         End Try
  75.         con.Clone()
  76.  
  77.     End Sub
  78.  
  79.     Private Sub btnAddtbl_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddtbl.Click
  80.  
  81.         txttblName.ReadOnly = False
  82.         btnCreateTbl.Visible = True
  83.         btnAddtbl.Visible = False
  84.         btnDroptbl.Visible = False
  85.         dtgstructure.SendToBack()
  86.     End Sub
  87.  
  88.     Private Sub btnCreateTbl_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreateTbl.Click
  89.  
  90.         Dim field As String
  91.         Dim type As String
  92.         Dim nlenght As Integer
  93.         Dim nNull As String
  94.         Dim nIndex As String
  95.         Dim nAI As String
  96.         Dim alltxt As String
  97.         Dim result As Integer
  98.  
  99.         Dim tblAndDB As String = cbdb.Text & "." & txttblName.Text
  100.         Dim TBLCREATE As String = "CREATE TABLE " & tblAndDB & " ( "
  101.  
  102.         For i As Integer = 0 To Me.dtgStructNewTbl.Rows.Count - 2
  103.  
  104.             ' field.Add(Me.dtgStructNewTbl.Rows(i).Cells("n_field").Value)
  105.             field = Me.dtgStructNewTbl.Rows(i).Cells("n_field").Value
  106.             type = Me.dtgStructNewTbl.Rows(i).Cells("n_type").Value
  107.             nlenght = Me.dtgStructNewTbl.Rows(i).Cells("n_lenght").Value
  108.             nNull = Me.dtgStructNewTbl.Rows(i).Cells("n_null").Value
  109.             nIndex = Me.dtgStructNewTbl.Rows(i).Cells("n_index").Value
  110.             nAI = Me.dtgStructNewTbl.Rows(i).Cells("n_ai").Value
  111.  
  112.             alltxt += field & " " & type & "(" & nlenght & ")" & " " & nNull & " " & nIndex & " " & nAI & "," & vbNewLine
  113.  
  114.         Next
  115.         Try
  116.             'it removes all the newline and whitespaces
  117.             alltxt = alltxt.Substring(0, alltxt.Length - Environment.NewLine.Length)
  118.             Dim cleanText As String
  119.             'it removes the the last comma ","
  120.             cleanText = alltxt.Remove(alltxt.Length - 1)
  121.             Dim finalText As String
  122.             'combination of finalText with table name and Mysql ENGINE
  123.             finalText = TBLCREATE & cleanText & ")ENGINE = MYISAM ;"
  124.  
  125.             con.Open()
  126.             With cmd
  127.                 .Connection = con
  128.                 .CommandText = finalText
  129.                 result = cmd.ExecuteNonQuery
  130.                 If result > 0 Then
  131.                     MsgBox("No Table has created!")
  132.                 Else
  133.                     MsgBox(txttblName.Text & " has created Successfully!")
  134.                     With Me
  135.                         .txttblName.ReadOnly = True
  136.                         .btnCreateTbl.Visible = False
  137.                         .btnAddtbl.Visible = True
  138.                         .btnDroptbl.Visible = True
  139.                         .dtgStructNewTbl.SendToBack()
  140.                         '.dtgStructNewTbl.Columns.Clear()
  141.                     End With
  142.  
  143.  
  144.                 End If
  145.             End With
  146.  
  147.  
  148.             Form1_Load(sender, e)
  149.  
  150.         Catch ex As Exception
  151.             MsgBox(ex.Message)
  152.         End Try
  153.         con.Close()
  154.     End Sub
  155.  
  156.     Private Sub btndescribe_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndescribe.Click
  157.  
  158.         txttblName.ReadOnly = True
  159.         btnAddtbl.Visible = True
  160.         btnDroptbl.Visible = True
  161.         btnCreateTbl.Visible = False
  162.         dtgstructure.BringToFront()
  163.  
  164.         Dim sql As String = "DESC " & cbdb.Text & "." & cbtable.Text
  165.         Dim publictable As New DataTable
  166.         Try
  167.  
  168.             'bind the connection and query
  169.             With cmd
  170.                 .Connection = con
  171.                 .CommandText = sql
  172.             End With
  173.  
  174.             da.SelectCommand = cmd
  175.             da.Fill(publictable)
  176.             '  publictable.Rows.Add("Please Select...")
  177.             dtgstructure.DataSource = publictable
  178.             ' dtgrd.Columns(1).Visible = False
  179.             da.Dispose()
  180.  
  181.         Catch ex As Exception
  182.             MsgBox(ex.Message)
  183.  
  184.         End Try
  185.         con.Clone()
  186.  
  187.     End Sub
  188.  
  189.     Private Sub btnDroptbl_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDroptbl.Click
  190.         Dim sql As String = "DROP TABLE " & cbdb.Text & "." & cbtable.Text
  191.         Dim result As Integer
  192.         Try
  193.             con.Open()
  194.             With cmd
  195.                 .Connection = con
  196.                 .CommandText = sql
  197.                 result = cmd.ExecuteNonQuery
  198.                 If result > 0 Then
  199.                     MsgBox("Error in dropping Field!")
  200.                 Else
  201.                     MsgBox(cbtable.Text & " has Successfully dropped!")
  202.                     With Me
  203.                         .txttblName.ReadOnly = True
  204.                         .btnCreateTbl.Visible = False
  205.                         .btnAddtbl.Visible = True
  206.                         .btnDroptbl.Visible = True
  207.                         .dtgStructNewTbl.SendToBack()
  208.                         dtgstructure.Columns.Clear()
  209.                     End With
  210.  
  211.  
  212.                 End If
  213.             End With
  214.  
  215.  
  216.             Form1_Load(sender, e)
  217.  
  218.         Catch ex As Exception
  219.             MsgBox(ex.Message)
  220.         End Try
  221.         con.Close()
  222.         Call Form1_Load(sender, e)
  223.  
  224.  
  225.     End Sub
  226. End Class
After reviewing all the codes above, you can now test your application by pressing “F5”.

Tags

Add new comment

About text formats
  • 174 views