Class SQL pada Module VB NET 2008

Kode Berikut Ketikan di dalam Module VB Net 2008:

Imports Microsoft.VisualBasic
Imports MySql.Data.MySqlClient

Public Class DB_MYSQL
Private varNamaDatabase As String = “”
Private varNamaTabel As String = “”
Private varNamaServer As String = “localhost”
Private varNamaUser As String = “root”
Private varPassword As String = “”
Private varTabelDeskripsi() As String = Nothing
Private varNilaiTabel() = Nothing
Private varPesan As String
Private varPrimaryKey As Integer = 0

Public LIST_OR_COMBO_BOX As New DB_COMBO
Public LIST_VIEW As New DB_VIEW

Private varKoneksi As MySqlConnection
Private varCON_STR As String
Private varPerintah As MySqlCommand
Private varPembaca As MySqlDataReader
Private varPerintahSql As String
Public Enum enumAksi
KONEK = 1
SIMPAN = 2
EDIT = 3
HAPUS = 4
CLOSE = 5
End Enum

Private varAksi As enumAksi

Public Event Setelah_Data_Terkoneksi(ByVal Pesan As String)
Public Event Setelah_Data_Tersimpan(ByVal Pesan As String)
Public Event Setelah_Data_Teredit(ByVal Pesan As String)
Public Event Setelah_Data_Terhapus(ByVal Pesan As String)
Public Event Ketika_Close(ByVal Pesan As String)
Public Event Setelah_Mencari(ByVal isNotNull As Boolean, ByVal Isi() As String)
Public Event Login(ByVal value() As String, ByVal isLogin As Boolean)

Public Sub TryLogin(ByVal sql As String)
KONEK()
Try
varPerintah = New MySqlCommand(sql, varKoneksi)
varPembaca = varPerintah.ExecuteReader
If varPembaca.HasRows Then
While varPembaca.Read
Dim a() As String = {varPembaca(“user”), varPembaca(“password”)}
RaiseEvent Login(a, True)
End While
Else
RaiseEvent Login(Nothing, False)
End If
Catch ex As Exception
MsgBox(ex.Message)
End
End Try
End Sub

Public Property AKSI() As enumAksi
Get
Return varAksi
End Get
Set(ByVal value As enumAksi)
varAksi = value
KONEK()
If varNamaDatabase = “” Then
MsgBox(“Maaf Nama Database Belum di Set, Silahkan Set Dulu”, MsgBoxStyle.Critical, “Kekosongan”)
ElseIf varTabelDeskripsi.Length = 0 Then
MsgBox(“Maaf Deskripsi Tabel Belum di Set, Silahkan Set Dulu”, MsgBoxStyle.Critical, “Kekosongan”)
ElseIf varNilaiTabel.Length = 0 Then
MsgBox(“Maaf Values Of Table Belum di Set, Silahkan Set Dulu”, MsgBoxStyle.Critical, “Kekosongan”)
Else
Select Case value
Case 1
KONEK()
varPesan = “Database Telah Terkoneksi”
RaiseEvent Setelah_Data_Terkoneksi(varPesan)
Case 2
SIMPAN()
RaiseEvent Setelah_Data_Tersimpan(varPesan)
Case 3
EDIT(varPrimaryKey)
RaiseEvent Setelah_Data_Teredit(varPesan)
Case 4
HAPUS(varPrimaryKey)
RaiseEvent Setelah_Data_Terhapus(varPesan)
Case 5
CLOSE()
varPesan = “Database Telah Diputus”
RaiseEvent Ketika_Close(varPesan)
End Select
End If
End Set
End Property

Sub CARI()
Dim str As String = “”
If varTabelDeskripsi.Length > 0 Then
For i As Integer = 0 To varTabelDeskripsi.Length – 1
If i > varTabelDeskripsi.Length – 2 Then
str &= varTabelDeskripsi(i)
Else
str &= varTabelDeskripsi(i) & “,”
End If
Next
varPerintahSql = “select ” & str & ” from ” & varNamaDatabase & “.” & varNamaTabel & ” where ” & _
varNamaTabel & “.” & varTabelDeskripsi(varPrimaryKey) & “='” & getValue(varNilaiTabel(varPrimaryKey)) & “‘ limit 1”
End If
KONEK()
varPerintah = New MySqlCommand(varPerintahSql, varKoneksi)
varPembaca = varPerintah.ExecuteReader
Dim Ada As Boolean = varPembaca.HasRows
Dim isi() As String = Nothing
While varPembaca.Read
For i As Integer = 0 To varPembaca.FieldCount – 1
ReDim Preserve isi(i)
isi(i) = varPembaca(i)
Next i
End While

RaiseEvent Setelah_Mencari(Ada, isi)
End Sub

Property PRIMARY_KEY() As Integer
Get
Return varPrimaryKey
End Get
Set(ByVal value As Integer)
varPrimaryKey = value
End Set
End Property

Private Sub KONEK()
varCON_STR = “SERVER=” & varNamaServer & “;UID=” & varNamaUser & “;PASSWORD=” & varPassword & “;DATABASE=” & varNamaDatabase
varKoneksi = New MySqlConnection
If varKoneksi.State = ConnectionState.Closed Then
Try
varKoneksi.ConnectionString = varCON_STR
varKoneksi.Open()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End If
End Sub

Private Sub CLOSE()
If varKoneksi.State = 1 Then
varKoneksi.Close()
varKoneksi.Dispose()
End If
End Sub

Property ALAMAT_SERVER() As String
Get
Return varNamaServer
End Get
Set(ByVal value As String)
varNamaServer = value
LIST_OR_COMBO_BOX.NAMA_SERVER = value
LIST_VIEW.NAMA_SERVER = value
End Set
End Property

Property NAMA_DATABASE() As String
Get
Return varNamaDatabase
End Get
Set(ByVal value As String)
varNamaDatabase = value
LIST_OR_COMBO_BOX.NAMA_DATABASE = value
LIST_VIEW.NAMA_DATABASE = value
End Set
End Property

Property NAMA_TABEL() As String
Get
Return varNamaTabel
End Get
Set(ByVal value As String)
varNamaTabel = value
End Set
End Property

Property USER_NAME() As String
Get
Return varNamaUser
End Get
Set(ByVal value As String)
varNamaUser = value
LIST_OR_COMBO_BOX.NAMA_USER = value
LIST_VIEW.NAMA_USER = value
End Set
End Property

Property PASSWORD() As String
Get
Return varPassword
End Get
Set(ByVal value As String)
varPassword = value
LIST_OR_COMBO_BOX.PASSWORD = value
LIST_VIEW.PASSWORD = value
End Set
End Property

Property DESKRIPSI_TABEL() As String()
Get
Return varTabelDeskripsi
End Get
Set(ByVal value() As String)
varTabelDeskripsi = value
End Set
End Property

Property ISI_TABEL()
Get
Return varNilaiTabel
End Get
Set(ByVal value)
varNilaiTabel = value
End Set
End Property

Private Function getValue(ByVal objek) As String
Dim str As String = “”
If TypeOf objek Is TextBox Then
If objek.text = “” Then
str = “null”
Else
str = “‘” & objek.text & “‘”
End If
ElseIf TypeOf objek Is ComboBox Then
If objek.text = “” Then
str = “null”
Else
str = “‘” & objek.text & “‘”
End If
ElseIf TypeOf objek Is DateTimePicker Then
str = “‘” & Format(objek.value, “yyyy-MM-dd”) & “‘”
ElseIf TypeOf objek Is Label Then
If objek.text = “” Then
str = “null”
Else
str = “‘” & objek.text & “‘”
End If
ElseIf TypeOf objek Is String Then
str = “‘” & objek & “‘”
Else
str = “‘” & objek.text & “‘”
End If
Return str
End Function

Private Sub SIMPAN()
Dim str As String = “”
For i As Integer = 0 To varNilaiTabel.Length – 1

If i > varNilaiTabel.Length – 2 Then
str &= getValue(varNilaiTabel(i))
Else
str &= getValue(varNilaiTabel(i)) & “,”
End If
Next
varPerintahSql = “insert into ” & varNamaTabel & ” values(” & str & “)”
Try
varPerintah = New MySqlCommand(varPerintahSql, varKoneksi)
varPerintah.ExecuteNonQuery()
varPesan = “Data Tersimpan”
Catch ex As Exception
varPesan = ex.Message
End Try
End Sub

Private Sub EDIT(ByVal keyIndex As Integer)
Dim str As String = “”
For i As Integer = 0 To varNilaiTabel.Length – 1
If i <> 0 Then
If i > varNilaiTabel.Length – 2 Then
str &= varTabelDeskripsi(i) & “=” & getValue(varNilaiTabel(i))
Else
str &= varTabelDeskripsi(i) & “=” & getValue(varNilaiTabel(i)) & “,”
End If
End If
Next

‘DDL = “update ” & DbName & “.” & TbName & ” set ” & str & ” where convert(” & TbName & “.” & TbDesc(0) & ” USINg utf8)='” & TbValue(0) & “‘ limit 1”
varPerintahSql = “update ” & varNamaDatabase & “.” & varNamaTabel & ” set ” & str & ” where ” & varNamaTabel & “.” & varTabelDeskripsi(keyIndex) & “=” & getValue(varNilaiTabel(keyIndex)) & ” limit 1″

Try
varPerintah = New MySqlCommand(varPerintahSql, varKoneksi)
varPerintah.ExecuteNonQuery()
varPesan = “Data Tersimpan”
Catch ex As Exception
varPesan = ex.Message
End Try
End Sub

Private Sub HAPUS(ByVal keyIndex As Integer)
varPerintahSql = “delete from ” & varNamaDatabase & “.” & varNamaTabel & ” where ” & varNamaTabel & “.” & varTabelDeskripsi(0) & “=” & getValue(varNilaiTabel(keyIndex))
Try
‘MsgBox(DDL)
varPerintah = New MySqlCommand(varPerintahSql, varKoneksi)
varPerintah.ExecuteNonQuery()
varPesan = “Data Terhapus”
Catch ex As Exception
varPesan = ex.Message
End Try
End Sub

End Class
‘—————————————————————————————
‘class untuk menampilkan ke listview
‘—————————————————————————————
‘—————————————————————————————
Public Class DB_VIEW
Private varItem() As ListView

Private varNamaDatabase As String = “”
Private varNamaKolom() As String

Private varNamaServer As String = “localhost”
Private varNamaUser As String = “root”
Private varPassword As String = “”

Private varKoneksi As MySqlConnection
Private varCON_STR As String
Private varPerintah As MySqlCommand
Private varPembaca As MySqlDataReader
Private varPerintahSql() As String
Private varKolom()() As String
Private varUkuranKolom()() As Integer

Private lenghtView As Integer = 0

Public Sub SEGARKAN_LISTVIEW(ByVal index As Integer, Optional ByVal sqlQuery As String = “”)
KONEK()
If sqlQuery = “” Then
sqlQuery = varPerintahSql(index)
End If

Dim no As Integer = 1
varPerintah = New MySqlCommand(sqlQuery, varKoneksi)
varPembaca = varPerintah.ExecuteReader
varItem(index).Items.Clear()
While varPembaca.Read
Dim listItem As New ListViewItem
listItem.Text = no
For j As Integer = 0 To varPembaca.FieldCount – 1
listItem.SubItems.Add(varPembaca(j).ToString)
Next
varItem(index).Items.Add(listItem)
no += 1
End While
varPembaca.Close()
End Sub

Property PASSWORD()
Get
Return varPassword
End Get
Set(ByVal value)
varPassword = value
End Set
End Property

Property NAMA_USER()
Get
Return varNamaUser
End Get
Set(ByVal value)
varNamaUser = value
End Set
End Property

Property NAMA_SERVER()
Get
Return varNamaServer
End Get
Set(ByVal value)
varNamaServer = value
End Set
End Property

Property NAMA_DATABASE()
Get
Return varNamaDatabase
End Get
Set(ByVal value)
varNamaDatabase = value
End Set
End Property

Private Sub KONEK()
varCON_STR = “SERVER=” & varNamaServer & “;UID=” & varNamaUser & “;PASSWORD=” & varPassword & “;DATABASE=” & varNamaDatabase
varKoneksi = New MySqlConnection
If varKoneksi.State = ConnectionState.Closed Then
Try
varKoneksi.ConnectionString = varCON_STR
varKoneksi.Open()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End If
End Sub

Public Sub TAMBAH(ByVal Item As ListView, ByVal ColumnsArray() As String, ByVal ColumnSize() As Integer, ByVal sql As String)
ReDim Preserve varItem(lenghtView)
varItem(lenghtView) = Item
ReDim Preserve varPerintahSql(lenghtView)
varPerintahSql(lenghtView) = sql
ReDim Preserve varKolom(lenghtView)
varKolom(lenghtView) = ColumnsArray
ReDim Preserve varUkuranKolom(lenghtView)
varUkuranKolom(lenghtView) = ColumnSize
BUAT_KOLOM()
SEGARKAN_SERMUA_LISTVIEW()
lenghtView += 1
End Sub

Private Sub BUAT_KOLOM()
For i As Integer = 0 To varKolom.Length – 1
varItem(i).View = View.Details
varItem(i).GridLines = True
varItem(i).FullRowSelect = True
varItem(i).Columns.Clear()
varItem(i).Columns.Add(“NO”, 50, HorizontalAlignment.Center)
For j As Integer = 0 To varKolom(i).Length – 1
varItem(i).Columns.Add(varKolom(i)(j), varUkuranKolom(i)(j), HorizontalAlignment.Left)
Next
Next
End Sub

Sub SEGARKAN_SERMUA_LISTVIEW()
KONEK()
For i As Integer = 0 To varItem.Length – 1
Dim no As Integer = 1
varPerintah = New MySqlCommand(varPerintahSql(i), varKoneksi)
varPembaca = varPerintah.ExecuteReader
varItem(i).Items.Clear()
While varPembaca.Read
Dim listItem As New ListViewItem
listItem.Text = no
For j As Integer = 0 To varPembaca.FieldCount – 1
Try
listItem.SubItems.Add(varPembaca(j).ToString)
Catch ex As Exception
listItem.SubItems.Add(varPembaca(j))
End Try
Next
varItem(i).Items.Add(listItem)
no += 1
End While
varPembaca.Close()
Next
End Sub
End Class
‘——————————————————————————————————————–
‘——————————————————————————————————————–
‘class untuk menampilkan field kedalam combo box, atau listbox
‘——————————————————————————————————————–
‘——————————————————————————————————————–
Public Class DB_COMBO

Private varItem() = Nothing

Private varNamaDatabase As String = “”
Private varNamaTabel() As String
Private varNamaKolom() As String

Private varNamaServer As String = “localhost”
Private varNamaUser As String = “root”
Private varPassword As String = “”

Private varKoneksi As MySqlConnection
Private varCON_STR As String
Private varPerintah As MySqlCommand
Private varPembaca As MySqlDataReader
Private varPerintahSql As String

Dim lenghtCombo As Integer = 0

Property PASSWORD()
Get
Return varPassword
End Get
Set(ByVal value)
varPassword = value
End Set
End Property

Property NAMA_USER()
Get
Return varNamaUser
End Get
Set(ByVal value)
varNamaUser = value
End Set
End Property

Property NAMA_SERVER()
Get
Return varNamaServer
End Get
Set(ByVal value)
varNamaServer = value
End Set
End Property

Property NAMA_DATABASE()
Get
Return varNamaDatabase
End Get
Set(ByVal value)
varNamaDatabase = value
End Set
End Property

Private Sub KONEK()
varCON_STR = “SERVER=” & varNamaServer & “;UID=” & varNamaUser & “;PASSWORD=” & varPassword & “;DATABASE=” & varNamaDatabase
varKoneksi = New MySqlConnection
If varKoneksi.State = ConnectionState.Closed Then
Try
varKoneksi.ConnectionString = varCON_STR
varKoneksi.Open()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End If
End Sub

Public Sub TAMBAH(ByVal Item, ByVal FieldName, ByVal TabelName)
ReDim Preserve varItem(lenghtCombo)
varItem(lenghtCombo) = Item
ReDim Preserve varNamaKolom(lenghtCombo)
varNamaKolom(lenghtCombo) = FieldName
ReDim Preserve varNamaTabel(lenghtCombo)
varNamaTabel(lenghtCombo) = TabelName
SEGARKAN_SERMUA()
lenghtCombo += 1
End Sub

Public Sub SEGARKAN(ByVal item As Integer)
KONEK()
varPerintahSql = “select ” & varNamaTabel(item) & “.” & varNamaKolom(item) & ” from ” & varNamaDatabase & “.” & _
varNamaTabel(item)
varPerintah = New MySqlCommand(varPerintahSql, varKoneksi)
varPembaca = varPerintah.ExecuteReader
varItem(item).items.clear()
While varPembaca.Read
varItem(item).items.add(varPembaca(0).ToString)
End While
varPembaca.Close()
End Sub

Private Sub SEGARKAN_SERMUA()
KONEK()
For i As Integer = 0 To varItem.Length – 1
varPerintahSql = “select ” & varNamaTabel(i) & “.” & varNamaKolom(i) & ” from ” & varNamaDatabase & “.” & _
varNamaTabel(i)
varPerintah = New MySqlCommand(varPerintahSql, varKoneksi)
varPembaca = varPerintah.ExecuteReader
varItem(i).items.clear()
While varPembaca.Read
varItem(i).items.add(varPembaca(0).ToString)
End While
varPembaca.Close()
Next
End Sub
End Class
‘—————————————————————————————————————
‘—————————————————————————————————————
‘modul untuk fungsi tambahan database
‘—————————————————————————————————————
‘—————————————————————————————————————
Module DB_FUNGSI
Function HANYA_INPUT_NOMOR(ByVal INPUT As Char) As Char
Select Case Asc(INPUT)
Case 8 : Return INPUT
Case 13 : Return INPUT
Case Is >= 48
If Asc(INPUT) <= 57 Then
Return INPUT
End If
Case Else : Return Chr(0)
End Select
End Function
End Module

Silahkan di download

Download Kode Class SQL

Download Full Code Class VB Net 2008

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s