CREATE TABLE table_name (column_name column_type);Mari kita mulai membangun aplikasi kita. Pertama buka Visual Basic klik untuk membuat proyek baru (new Project) buat Nama sebagai "tblcreator". Setelah membuat proyek baru, kita akan menambahkan objek untuk mendisain applikasi adalah sebagai berikut : tiga Label, tiga Tombol, dua Combobox, satu Textbox, satu Datagridview dan Groupbox. Kemudian mengatur semua objek seperti yang ditunjukkan di bawah ini.
Selanjutnya, untuk datagridview1 kita perlu menambahkan 6 kolom, dan ini isi kolom tersebut: Field, type, Length/Values, Null, Index and Auto.
Berikut pengaturan untuk kolom datagridview :
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
Setelah kita mengatur kolom datagriview, berikut adalah beberapa item perlu penjelasan :
• NOT NULL digunakan karena kita tidak ingin bidang ini menjadi NULL. Jadi jika pengguna akan mencoba untuk menginput dengan nilai NULL, maka MySQL akan meningkatkan kesalahan.
• AUTO_INCREMENT memberitahu MySQL untuk terus maju dan menambahkan nomor yang tersedia di sebelah kolom id.
• PRIMARY KEY digunakan untuk mendefinisikan sebuah kolom sebagai kunci primer. Anda dapat menggunakan beberapa kolom dipisahkan dengan tanda koma untuk mendefinisikan primary key.
Kemudian kita tambahkan fungsi untuk aplikasi kita. Untuk melakukan ini, klik dua kali pada form dan tambahkan kode berikut di bawah public class.
'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
Kemudian, pada Form1_Load tambahkan kode berikut. Ini akan mengisi combobox dengan nama database.
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
Semua tampilan code akan seperti ini . . .
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
Untuk menguji program ini, tekan "F5"
Post a Comment