Creating MySQL Database tables using Visual Basic. Net

Submitted by joken on Wednesday, October 9, 2013 - 16:54.

In this tutorial, I’m going to show you how to create a MySQL database tables dynamically in a simple way. To start in this lesson, we need to understand first the concept of creating a MySQL database table. The creation of table command requires the following: Name of the table, Names of fields and Definitions for each field. The generic syntax of creating a MySQL table is like as shown below:

CREATE TABLE table_name (column_name column_type);

This time, let’s start building our application. First open Visual basic and create a new project and Name it as “tblcreator”. After creating a new project, we will then add objects to our form and these objects are the following: three labels, three buttons, two combobox, one textbox, one datagridview and a groupbox. Then arrange all objects looks like as shown below.

Next, for datagridview1 we need to add 6 columns. And these are: Field, type, Length/Values, Null, Index and Auto. Here’s the settings for datagridview columns:

Selected Columns	Properties		Settings
Field			Header Text		Field
			DatapropertyName	n_field
			Name			n_field
			ColumnType		DataGridViewTextBoxColumn
type			Header Text		type
			DatapropertyName	n_type
			Name			n_type
			ColumnType		DataGridViewComboBoxColumn
			Items			INT
VARCHAR
TEXT
DATE
Lenght/Values		Header Text		Lenght/Values
			DatapropertyName	n_Lenght
			Name			n_Lenght
			ColumnType		DataGridViewTextBoxColumn
NULL			Header Text		Null
			DatapropertyName	n_null
			Name			n_null
			ColumnType		DataGridViewCheckBoxColumn
			Truevalue 		NOT NULL
			Falsevalue		NULL
Index			Header Text		Index
			DatapropertyName	n_index
			Name			n_index
			ColumnType		DataGridViewComboBoxColumn
Items			PRIMARY KEY
UNIQUE	
INDEX
FULLTEXT
Auto Increment		Header Text		Auto Increment
			DatapropertyName	n_ai
Truevalue		AUTO_INCREMENT
			Name			n_ai
			ColumnType		DataGridViewCheckBoxColumn

After we set the datagriview columns, here are few items need explanation: • Field Attribute NOT NULL is being used because we do not want this field to be NULL. So if user will try to create a record with NULL value, then MySQL will raise an error. • Field Attribute NOT NULL is being used because we do not want this field to be NULL. So if user will try to create a record with NULL value, then MySQL will raise an error. • Field Attribute AUTO_INCREMENT tells MySQL to go ahead and add the next available number to the id field. • Keyword PRIMARY KEY is used to define a column as primary key. You can use multiple columns separated by comma to define a primary key. This time, lets add functionality to our application. To do this, double click the form and add the following code below public class.

'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

Then, on the form1_load add the following code. This will populate the combobox with database name.

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
            MsgBox(ex.Message)
 
        End Try
        con.Clone()
 
    End Sub

Next, we need to display all the MySQL Database tables on the combobox provided for table name based on the Database selected. To do this, double click the “use” button and add the following code:

 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
            MsgBox(ex.Message)
 
        End Try
        con.Clone()
 
    End Sub

Then in order to enable the “create” button, we need to pass first to “Add” button. To do this, add the following code:

        txttblName.ReadOnly = False
        btnCreateTbl.Visible = True
        btnAddtbl.Visible = False

next, double click our “create” button and add the following code:

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
                    MsgBox("No Table has created!")
                Else
                    MsgBox(txttblName.Text & " has created Successfully!")
                    With Me
                        .txttblName.ReadOnly = True
                        .btnCreateTbl.Visible = False
                        .btnAddtbl.Visible = True
                        .dtgStructNewTbl.SendToBack()
                        '.dtgStructNewTbl.Columns.Clear()
                    End With
 
 
                End If
            End With
 
 
            Form1_Load(sender, e)
 
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        con.Close()
    End Sub

And here’s all the code use for this application.

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
            MsgBox(ex.Message)
 
        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
            MsgBox(ex.Message)
 
        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
    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
                    MsgBox("No Table has created!")
                Else
                    MsgBox(txttblName.Text & " has created Successfully!")
                    With Me
                        .txttblName.ReadOnly = True
                        .btnCreateTbl.Visible = False
                        .btnAddtbl.Visible = True
                        .dtgStructNewTbl.SendToBack()
                        '.dtgStructNewTbl.Columns.Clear()
                    End With
 
 
                End If
            End With
 
 
            Form1_Load(sender, e)
 
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        con.Close()
    End Sub
End Class

Creating MySQL Database tables using Visual Basic. Net

Tags

Comments

can i ask a question..?

Add new comment