Membuat Table Database Mysql Dengan VB.Net
Minggu, 03 November 2013
0
komentar
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"
Baca Selengkapnya ....
