Membuat Table Database Mysql Dengan VB.Net

CREATE TABLE table_name (column_name column_type);

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

'Merupakan pernyataan SQL atau disimpan prosedur untuk mengeksekusi terhadap sumber data.
Dim cmd As New MySqlCommand
Dim da As New MySqlDataAdapter
Public total As Integer
Dim publictable As New DataTable
'menyatakan conn sebagai koneksi dan sekarang akan sambungan baru karena
'itu sama dengan Getconnection Function
Dim con As MySqlConnection = Damconn()

Public Function Damconn() 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

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

    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

    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 = Damconn()
     
    Public Function Damconn() 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