Kamis, 03 Mei 2012

Coding Database ODBC di VB.NET

Teknologi ODBC dapat digunakan untuk membuat program database menggunakan hampir semua database asal mempunyai driver ODBC yang telah terinsal di Windows seperti yang telah diterangkan sebelumnya. Untuk membuat program database dengan Microsoft Access menggunakan ODBC dengan kode program ikuti langkah-langkah berikut :

Imports System.Data.Odbc
Imports System.IO

Public Class FAccessODBC
    Public cn As OdbcConnection
    Public ds As DataSet
    Public tb As DataTable
    Public WithEvents bs As BindingSource

    Private Sub FAccessODBC_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim i As Integer

        cn = New OdbcConnection("Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Path.GetDirectoryName(Application.ExecutablePath) & "\world.mdb")

        cn.Open()

        Dim cm As New OdbcCommand("SELECT name, address, phone FROM contacts", cn)
        Dim rd As OdbcDataReader

        rd = cm.ExecuteReader()

        ds = New DataSet
        tb = New DataTable
        tb.TableName = "contacts"
        ds.Tables.Add(tb)

        ds.Load(rd, LoadOption.Upsert, tb)

        For i = 0 To (tb.Columns.Count - 1)
            tb.Columns(i).MaxLength = -1
        Next

        rd.Close()
        cn.Close()

        bs = New BindingSource()

        bs.DataSource = ds

        bs.DataMember = "contacts"

        gContacts.DataSource = bs
    End Sub

    Private Sub bs_CurrentChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles bs.CurrentChanged
        If ((bs.Count > 0) And (bs.DataMember = "contacts")) Then
            tName.Text = bs.Item(bs.Position)("name").ToString
            tAddress.Text = bs.Item(bs.Position)("address").ToString
            tPhone.Text = bs.Item(bs.Position)("phone").ToString
        End If
    End Sub

    Private Sub bFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bFirst.Click
        bs.Position = 0
    End Sub

    Private Sub bPrev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bPrev.Click
        bs.Position = bs.Position - 1
    End Sub

    Private Sub bNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bNext.Click
        bs.Position = bs.Position + 1
    End Sub

    Private Sub bLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bLast.Click
        bs.Position = bs.Count - 1
    End Sub

    Private Sub bAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bAdd.Click
        bs.AddNew()
    End Sub

    Private Sub bEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bEdit.Click
        '
    End Sub

    Private Sub bSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bSave.Click
        Dim cm As New OdbcCommand

        cn.Open()

        If bs.Item(bs.Position)("name").ToString() = "" Then
            cm.Dispose()

            cm = New OdbcCommand("INSERT INTO contacts (name, address, phone) VALUES (?, ?, ?)", cn)
        Else
            cm.Dispose()

            cm = New OdbcCommand("UPDATE contacts SET name = ?, address = ?, phone = ? WHERE name = ?", cn)
        End If

        cm.Parameters.Add("@name", OdbcType.VarChar, 0, "").Value = tName.Text
        cm.Parameters.Add("@address", OdbcType.VarChar, 0, "").Value = tAddress.Text
        cm.Parameters.Add("@phone", OdbcType.VarChar, 0, "").Value = tPhone.Text

        If bs.Item(bs.Position)("name").ToString() <> "" Then
            cm.Parameters.Add("@Original_name", OdbcType.VarChar, 0, "").Value = bs.Item(bs.Position)("name").ToString
        End If

        cm.ExecuteNonQuery()
        cn.Close()

        bs.Item(bs.Position)("name") = tName.Text
        bs.Item(bs.Position)("address") = tAddress.Text
        bs.Item(bs.Position)("phone") = tPhone.Text

        bs.EndEdit()
    End Sub

    Private Sub bCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bCancel.Click
        bs.CancelEdit()
    End Sub

    Private Sub bDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bDelete.Click
        cn.Open()
        Dim cm As New OdbcCommand("DELETE FROM contacts WHERE name = ?", cn)
        cm.Parameters.Add("@Original_name", OdbcType.VarChar, 0, "").Value = bs.Item(bs.Position)("name").ToString
        cm.ExecuteNonQuery()
        cn.Close()

        bs.RemoveAt(bs.Position)
    End Sub

    Private Sub bRefresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bRefresh.Click
        Dim i As Integer

        gContacts.DataSource = Nothing
        bs.DataSource = Nothing
        ds.Dispose()
        tb.Dispose()

        cn.Open()

        Dim cm As New OdbcCommand("SELECT name, address, phone FROM contacts WHERE name LIKE '%" & tFind.Text.Trim & "%'", cn)
        Dim rd As OdbcDataReader

        rd = cm.ExecuteReader()

        ds = New DataSet
        tb = New DataTable
        tb.TableName = "contacts"
        ds.Tables.Add(tb)

        ds.Load(rd, LoadOption.Upsert, tb)

        For i = 0 To (tb.Columns.Count - 1)
            tb.Columns(i).MaxLength = -1
        Next

        rd.Close()
        cn.Close()

        bs = New BindingSource()

        bs.DataSource = ds

        bs.DataMember = "contacts"

        gContacts.DataSource = bs
    End Sub

End Class

Setelah program dijalankan hasilnya tampak seperti tampilan berikut :

 

Membuat aplikasi database menggunakan MySQL dapat dilakukan memakai Driver ODBC (System.Data.Odbc), dengan syarat terlebih dahulu telah diinstall Driver MyODBC untuk MySQL yang dapat didownload gratis di internet. Membuat aplikasi database MySQL menggunakan ODBC (System.Data.Odbc) cara pemrogramannya mirip dengan Odbc saat membuat aplikasi database menggunakan Access, hanya saja ConnectionString-nya diganti sebagai berikut :

cn = New OdbcConnection("DRIVER={MySQL ODBC 3.51 Driver};UID=root;STMT=;OPTION=;PORT=;PASSWORD=;SERVER=localhost;DATABASE=world;DESC=")

ConnectionString tersebut dapat dilihat susunannya dari Registry.

Membuat aplikasi database menggunakan Interbase atau Firebird dapat dilakukan memakai Driver ODBC (System.Data.Odbc), dengan syarat terlebih dahulu telah diinstall Driver Firebird/Interbase ODBC untuk Interbase/Firebird yang dapat didownload gratis di internet. Membuat aplikasi database Firebird/Interbase menggunakan ODBC (System.Data.Odbc) cara pemrogramannya mirip dengan Odbc saat membuat aplikasi database menggunakan Access maupun MySQL, hanya saja ConnectionString-nya diganti sebagai berikut :

cn = New OdbcConnection("DRIVER={Firebird/InterBase(r) driver};Dbname=" & Path.GetDirectoryName(Application.ExecutablePath) & "\WORLD.FDB;User=SYSDBA;PASSWORD=DKEBFJENHFCOBHGHLAIMNAAFICELEAEGDNMFNOGALAMHBBGCHFADNKCBPPGMANOGIEKENIOPHDIPBIECPLLLCBIKEJKMJLPLIB")

ConnectionString tersebut dapat dilihat susunannya dari File DSN atau Registry.