Docstoc

BAB 9 Mengolah Database MySQL

Document Sample
BAB 9 Mengolah Database MySQL Powered By Docstoc
					Created By Uus Rusmawan



                                  BAB 9
                         MENGOLAH DATABASE MYSQL

9.1 Membuat Koneksi ODBC

Sebelum melakukan pengolahan database MySQL diperlukan beberapa hal sebagai
persiapan awal diantaranya adalah membuat ODBC sebagai sarana untuk dapat
mengakses database. Cara membuat ODBC adalah sebagai berikut.

1.   Klik menu Start
2.   Pilih Control Panel
3.   Pilih Administrative Tools




                       Gambar 9.1   Administrative Tools

4.   Pilih Data Sources (ODBC)




                      Gambar 9.2    Data Sources (ODBC)

5.   Pilih User DSN
6.   Klik Add




                         Gambar 9.3 Menambah ODBC

7.   Pilih MySQL ODBC 3.51 Driver




                                                                        123
Created By Uus Rusmawan




                          Gambar 9.4 Memilih driver


8.    Klik Finish
9.    Tulis nama ODBC (misalnya MySQL1)
10.   Tulis nama database (Misalnya DBMySQL)
11.   Klik OK




                        Gambar 9.5 Konfigurasi ODBC

12.   Hasil pembuatan tersebut terlihat pada gambar berikut




124
Created By Uus Rusmawan




                      Gambar 9.6 Hasil pembuatan ODBC

9.2 Koneksi Menggunakan Objek

1.   Buatlah form seperti gambar 9.7.




                  Gambar 9.7 Koneksi menggunakan objek

2.   Tambahkan sebuah OdbcConnection




                      Gambar 9.8 Ikon OdbcConnection

3.   Pilih ConnectionString di properti
4.   Pilih New Connection




                                                         125
Created By Uus Rusmawan




                      Gambar 9.9 Membuat koneksi baru


5.    Pilih tab Machine Data Source
6.    Pilih MySQL1
7.    Klik OK




                          Gambar 9.10 Memilih ODBC

8.    Klik OK
9.    Klik Test Data Source
10.   Klok OK




126
Created By Uus Rusmawan




                     Gambar 9.11 Indikasi koneksi sukses

11.   Klik OK
12.   Klik OK
13.   Klik Include Password




                    Gambar 9.12 Koneksi berikut password

14.   Tambahkan sebuah OdbcDataAdapter




                     Gambar 9.13 Ikon OdbcDataAdapter

15.   Klik Next
16.   Pilih Connection yang telah dibuat




                         Gambar 9.14 Memilih koneksi

17.   Klik Next
18.   Klik Next
19.   Klik Query Builder
20.   Pilih Tabel Barang
21.   Klik Add
22.   Klik Close
23.   Aktifkan ChekBox * (All Columns)
24.   Klik OK
25.   Klik Next


                                                           127
Created By Uus Rusmawan



26.   Klik Finish
27.   Tambahkan sebuah Dataset
28.   Pilih Untyped Dataset
29.   Klik OK
30.   Double Klik Button1
31.   Tulis Coding di bawah ini

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
      Dim Koneksi As String
      Koneksi = "select * from barang"
      OdbcDataAdapter1.SelectCommand.CommandText = Koneksi
      DataSet1.Clear()
      OdbcDataAdapter1.Fill(DataSet1, "Barang")
      DataGrid1.SetDataBinding(DataSet1, "Barang")
End Sub

9.3 Koneksi Tanpa Objek

1.    Buatlah form seperti gambar 9.15 di bawah ini




                      Gambar 9.15 Koneksi tanpa objek

2.    Double Click form di area yang kosong
3.    Tulis coding berikut ini:



Imports System.Data
Imports System.Data.Odbc

  Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
     Dim ObjConn As OdbcConnection
     Dim ObjCommand As OdbcCommand
     Dim ObjDataAdafter As OdbcDataAdapter
     Dim ObjDataSet As New DataSet
     Dim StrConn, StrSQL As String

     StrConn =
"STMT=;OPTION=3;DSN=MySQL1;UID=;PASSWORD=;DESC=MySQL ODBC 3.51
Driver DSN;DATABASE=DBMySQL;SERVER=localhost;PORT=3306"


128
Created By Uus Rusmawan



       StrSQL = "select * from barang"
       ObjConn = New OdbcConnection(StrConn)
       ObjCommand = New OdbcCommand(StrSQL, ObjConn)
       ObjConn.Open()
       ObjDataAdafter = New OdbcDataAdapter(ObjCommand)
       ObjDataAdafter.Fill(ObjDataSet, "Barang")
       DataGrid1.DataSource = ObjDataSet.Tables("barang")
       ObjConn.Close()
     End Sub

9.4 Koneksi Menggunakan Server Explorer

1.     Buatlah form baru dengan sebuah DataGrid
2.     Klik Menu View
3.     Pilih Server Explorer
4.     Klik Add Connection (dengan asumsi belum ada koneksi yang pernah dibuat.
       Jika koneksi telah dibuat sebelumnya kita tinggal memilihnya lalu drag
       koneksi tersebut ke dalam Form)
5.     Klik Tabulasi Provider
6.     Pilih Microsoft OLE DB Provider For ODBC Drivers




                          Gambar 9.16 Memilih provider

7.     Klik Tabulasi Connection
8.     Pilih Use Connection String
9.     Klik Build




                                                                             129
Created By Uus Rusmawan




                       Gambar 9.17 Membangun koneksi

10.   Klik Tabulasi Machine Data Source
11.   Pilih MySql1




                          Gambar 9.18 Memilih ODBC

12.   Klik OK
13.   Klik Test Data Source
14.   Klik OK (beberapa kali)
15.   Hasil pembuatan koneksi terlihat seperti gambar berikut ini.




                 Gambar 9.19     Memasang koneksi dalam form


130
Created By Uus Rusmawan



16.   Drag koneksi ke dalam form
17.   Pilih Include Password
18.   Tambahkan objek OdbcDataAdapter
19.   Atur propertinya
20.   Tambahkan Dataset
21.   Pilih Untyped Dataset
22.   Klik OK
23.   Tulis Coding berikut
24.   RUN Project


9.5 Mengolah Data Tanpa Objek

Untuk mempercepat penulisan coding saat mengakses database, buatlah sebuah
Module lalu ketik coding berikut ini. Setelah itu buatlah form seperti gambar di
bawah ini.

Module Module1

  Public   DA As Odbc.OdbcDataAdapter
  Public   DT As New DataTable
  Public   DS As New DataSet
  Public   Cmd As Odbc.OdbcCommand
  Public   StrConn, StrSQL As String
  Public   Conn = Module1.Koneksi

  Public Function Koneksi() As Odbc.OdbcConnection
     Dim Conn As Odbc.OdbcConnection
     Conn = New
Odbc.OdbcConnection("STMT=;OPTION=3;DSN=MySQL1;UID=;PASSWORD=;DES
C=MySQL ODBC 3.51 Driver
DSN;DATABASE=DBMySQL;SERVER=localhost;PORT=3306")
     Conn = Nothing
  End Function
End Module




                                                                             131
Created By Uus Rusmawan




                 Gambar 9.20 Pengolahan data tanpa objek

Coding

Imports System.Data
Imports System.Data.Odbc

Public Class Form2
  Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

  Public Sub New()
    MyBase.New()
    InitializeComponent()
  End Sub
#End Region

  Dim    conn As OdbcConnection
  Dim    strconn As String
  Dim    strsql As String
  Dim    DA1 As New OdbcDataAdapter
  Dim    DS1 As DataSet
  Dim    DT1 As DataTable
  Dim    DA2 As New OdbcDataAdapter
  Dim    DS2 As DataSet
  Dim    DT2 As DataTable

  Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
     TextBox1.MaxLength = 6
     TextBox2.MaxLength = 30
     TextBox3.MaxLength = 8
     TextBox4.MaxLength = 8


132
Created By Uus Rusmawan



    TextBox5.MaxLength = 4
    KondisiAwal()
    TampilGrid()
  End Sub

   Sub TampilGrid()
     Dim strconn =
"STMT=;OPTION=3;DSN=MySQL1;UID=;PASSWORD=;DESC=MySQL ODBC 3.51
Driver DSN;DATABASE=DBMySQL;SERVER=localhost;PORT=3306"
     conn = New OdbcConnection(strconn)
     strsql = "Select * from barang"
     DA1 = New OdbcDataAdapter(strsql, conn)
     DS1 = New DataSet
     DA1.Fill(DS1, "Barang")
     DataGrid1.SetDataBinding(DS1, "Barang")
     DataGrid1.ReadOnly = True
   End Sub

   Sub CariData()
     Dim strconn =
"STMT=;OPTION=3;DSN=MySQL1;UID=;PASSWORD=;DESC=MySQL ODBC 3.51
Driver DSN;DATABASE=DBMySQL;SERVER=localhost;PORT=3306"
     conn = New OdbcConnection(strconn)
     strsql = "Select * from barang where kodebrg='" & TextBox1.Text & "'"
     DA2 = New OdbcDataAdapter(strsql, conn)
     DS2 = New DataSet
     DA2.Fill(DS2, "Barang")
     TampilGrid()
   End Sub

  Sub TampilData()
    Dim Tbl As DataTable = DS2.Tables(0)
    TextBox2.Text = Tbl.Rows(0)(1)
    TextBox3.Text = Tbl.Rows(0)(2)
    TextBox4.Text = Tbl.Rows(0)(3)
    TextBox5.Text = Tbl.Rows(0)(4)
  End Sub

  Sub Gelap()
    TextBox1.Enabled = False : TextBox2.Enabled = False
    TextBox3.Enabled = False : TextBox4.Enabled = False
    TextBox5.Enabled = False
  End Sub

  Sub Terang()
    TextBox1.Enabled = True : TextBox2.Enabled = True
    TextBox3.Enabled = True : TextBox4.Enabled = True
    TextBox5.Enabled = True
  End Sub

  Sub Kosongkan()
    TextBox1.Text = "" : TextBox2.Text = ""
    TextBox3.Text = "" : TextBox4.Text = ""
    TextBox5.Text = ""
  End Sub

                                                                         133
Created By Uus Rusmawan




  Sub KondisiAwal()
    Gelap()
    Kosongkan()
    Button1.Enabled = True
    Button2.Enabled = True
    Button3.Enabled = True
    Button4.Enabled = True
    Button1.Text = "Input"
    Button2.Text = "Edit"
    Button3.Text = "Hapus"
    Button4.Text = "Tutup"
  End Sub

  Sub KondisiInput()
    Terang()
    Kosongkan()
    TextBox1.Focus()
    Button1.Text = "Simpan"
    Button2.Enabled = False
    Button3.Enabled = False
    Button4.Text = "Batal"
  End Sub

  Sub KondisiEdit()
    Terang()
    Kosongkan()
    TextBox1.Focus()
    Button1.Enabled = False
    Button2.Text = "Simpan"
    Button3.Enabled = False
    Button4.Text = "Batal"
  End Sub

  Sub KondisiHapus()
    Terang()
    Kosongkan()
    TextBox1.Enabled = True
    TextBox1.Focus()
    Button1.Enabled = False
    Button2.Enabled = False
    Button3.Text = "Hapus"
    Button4.Text = "Batal"
  End Sub

  Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
     If Button1.Text = "Input" Then
        KondisiInput()
     Else
        If TextBox1.Text = "" Or TextBox2.Text = "" Or TextBox3.Text = "" Or
TextBox4.Text = "" Or TextBox5.Text = "" Then
           MessageBox.Show("data belum lengkap")
           If TextBox1.Text = "" Then
              TextBox1.Focus()

134
Created By Uus Rusmawan



           ElseIf TextBox2.Text = "" Then
              TextBox2.Focus()
           ElseIf TextBox3.Text = "" Then
              TextBox3.Focus()
           ElseIf TextBox4.Text = "" Then
              TextBox4.Focus()
           ElseIf TextBox5.Text = "" Then
              TextBox5.Focus()
           End If
           Exit Sub
        Else
           Try
              Call Module1.Koneksi()
              conn.Open()
              Dim SqlInput As String = "insert into barang
(kodebrg,namabrg,hargabeli,hargajual,jumlahbrg) values ('" & TextBox1.Text & "','"
& TextBox2.Text & "','" & TextBox3.Text & "','" & TextBox4.Text & "','" &
TextBox5.Text & "')"
              Dim SqlKu As OdbcCommand = New OdbcCommand(SqlInput, conn)
              SqlKu.ExecuteNonQuery()
              conn.Close()
           Catch ex As Exception
              MsgBox(ex.Message, MsgBoxStyle.Information, "Perhatian")
           End Try
           KondisiAwal()
           TampilGrid()
        End If
     End If
   End Sub

  Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click
     If Button2.Text = "Edit" Then
        KondisiEdit()
     Else
        If TextBox2.Text = "" Or TextBox3.Text = "" Or TextBox4.Text = "" Or
TextBox5.Text = "" Then
           MessageBox.Show("data belum lengkap")
           If TextBox2.Text = "" Then
              TextBox2.Focus()
           ElseIf TextBox3.Text = "" Then
              TextBox3.Focus()
           ElseIf TextBox4.Text = "" Then
              TextBox4.Focus()
           ElseIf TextBox5.Text = "" Then
              TextBox5.Focus()
           End If
           Exit Sub
        Else
           Try
              Call Module1.Koneksi()
              conn.Open()
              Dim SqlEdit As String = "Update Barang Set NamaBrg='" &
TextBox2.Text & "',HargaBeli='" & TextBox3.Text & "',HargaJual='" &



                                                                               135
Created By Uus Rusmawan



TextBox4.Text & "',JumlahBrg='" & TextBox5.Text & "' where KodeBrg='" &
TextBox1.Text & "'"
            Dim SqlKu As OdbcCommand = New OdbcCommand(SqlEdit, conn)
            SqlKu.ExecuteNonQuery()
            conn.Close()
          Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Information, "Perhatian")
          End Try
          KondisiAwal()
          TampilGrid()
       End If
     End If
  End Sub

  Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button3.Click
     KondisiHapus()
  End Sub

  Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button4.Click
     Select Case Button4.Text
        Case "Tutup"
           KondisiAwal()
           Me.Close()
        Case "Batal"
           KondisiAwal()
     End Select
  End Sub

  Private Sub TextBox1_KeyPress(ByVal sender As Object, ByVal e As
System.Windows.Forms.KeyPressEventArgs) Handles TextBox1.KeyPress
     If e.KeyChar = Chr(13) Then
        If Len(TextBox1.Text) < 6 Then
           MsgBox("Kode barang harus 6 digit")
           TextBox1.Focus()
           Exit Sub
        End If
        TextBox1.Text = UCase(TextBox1.Text)
        If Button1.Text = "Simpan" Then
            Call CariData()
           Dim Tbl As DataTable = DS2.Tables(0)
           If Tbl.Rows.Count <> 0 Then
              Call TampilData()
              MessageBox.Show("Kode Barang Sudah Ada, Coba Kode Lain")
              TextBox1.Focus()
              Kosongkan()
           Else
              TextBox2.Focus()
              Exit Sub
           End If

       ElseIf Button2.Text = "Simpan" Then
          Call CariData()
          Dim Tbl As DataTable = DS2.Tables(0)

136
Created By Uus Rusmawan



          If Tbl.Rows.Count <> 0 Then
             Call TampilData()
             TextBox2.Focus()
          Else
             MessageBox.Show("Kode Barang Tidak Ada, Coba Kode Lain")
             TextBox1.Focus()
             Kosongkan()
             Exit Sub
          End If

        ElseIf Button3.Text = "Hapus" Then
           Call CariData()
           Dim Tbl As DataTable = DS2.Tables(0)
           If Tbl.Rows.Count <> 0 Then
              Call TampilData()
              Dim Message As String = "Yakin akan dihapus ?"
              Dim Caption As String = "Hapus Data"
              Dim Buttons As Integer = MessageBoxButtons.YesNo
              Dim Result As DialogResult
              Result = MessageBox.Show(Me, Message, Caption,
MessageBoxButtons.YesNo, MessageBoxIcon.Question,
MessageBoxDefaultButton.Button1, MessageBoxOptions.RightAlign)
              If Result = DialogResult.Yes Then
                 Dim Conn As String
                  Conn =
"STMT=;OPTION=3;DSN=MySQL1;UID=;PASSWORD=;DESC=MySQL ODBC 3.51
Driver DSN;DATABASE=DBMySQL;SERVER=localhost;PORT=3306"
                 Dim myconn As New OdbcConnection(Conn)
                 Dim StrHapus As String = "delete from barang where kodebrg='"
& TextBox1.Text & "'"
                 Dim StrCmd As New OdbcCommand(StrHapus)
                  StrCmd.Connection = myconn
                 myconn.Open()
                  StrCmd.ExecuteNonQuery()
                  StrCmd.Connection.Close()
                  KondisiAwal()
                  TampilGrid()
              Else
                  KondisiAwal()
                  TampilGrid()
              End If
           Else
              MessageBox.Show("Data tidak ditemukan")
              TextBox1.Focus()
              Exit Sub
           End If
        End If
     End If
   End Sub

  Private Sub TextBox2_KeyPress(ByVal sender As Object, ByVal e As
System.Windows.Forms.KeyPressEventArgs) Handles TextBox2.KeyPress
     If e.KeyChar = Chr(13) Then
        TextBox3.Focus()
        TextBox2.Text = UCase(TextBox2.Text)

                                                                            137
Created By Uus Rusmawan



       End If
     End Sub

  Private Sub TextBox3_KeyPress(ByVal sender As Object, ByVal e As
System.Windows.Forms.KeyPressEventArgs) Handles TextBox3.KeyPress
     If e.KeyChar = Chr(13) Then TextBox4.Focus()
     If Not ((e.KeyChar >= "0" And e.KeyChar <= "9") Or e.KeyChar = vbBack)
Then e.Handled() = True
  End Sub

  Private Sub TextBox4_KeyPress(ByVal sender As Object, ByVal e As
System.Windows.Forms.KeyPressEventArgs) Handles TextBox4.KeyPress
     If e.KeyChar = Chr(13) Then
        If TextBox4.Text <= TextBox3.Text Then
           MessageBox.Show("Cek harga jual.., jangan <= harga beli")
           TextBox4.Focus()
           Exit Sub
        Else
           TextBox5.Focus()
        End If
     End If
     If Not ((e.KeyChar >= "0" And e.KeyChar <= "9") Or e.KeyChar = vbBack)
Then e.Handled() = True
  End Sub

  Private Sub TextBox5_KeyPress(ByVal sender As Object, ByVal e As
System.Windows.Forms.KeyPressEventArgs) Handles TextBox5.KeyPress
     If e.KeyChar = Chr(13) Then
        If Button1.Text = "Simpan" Then
           Button1.Focus()
        ElseIf Button2.Text = "Simpan" Then
           Button2.Focus()
        End If
     End If
     If Not ((e.KeyChar >= "0" And e.KeyChar <= "9") Or e.KeyChar = vbBack)
Then e.Handled() = True
  End Sub
End Class


Setelah membahas beberapa cara koneksi database dalam VB.Net dapat diambil
kesimpulan antara lain :

1.     Cara koneksi dan mengolah database Ms. Access dapat dilakukan dengan tiga
       cara yaitu menggunakan Objek, Tanpa objek dan menggunakan Server
       Explorer. Hal yang sama dapat dilakukan pada database SQL Server dan
       MySQL.
2.     Perbedaan dari masing-masing cara pengolahan data tersebut bergantung
       pada jenis database yang akan diolah.
3.     Untuk memudahkan akses ke database pada model pengolahan data tanpa
       objek sebaiknya membuat module khusus agar penulisan coding tidak
       dilakukan berulang-ulang

Sekarang bagaimana menurut Anda, Mengolah database dengan VB.Net mudah
bukan…?

138

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:26
posted:11/20/2012
language:Unknown
pages:16