Subscribe:

Saturday, November 6, 2010

Menyimpan Data ke Database (Module, Function dan Procedure)

Program berikut menggunakan Module untuk menampung VARIABLE, FUNCTION dan PROCEDURE yang sifatnya digunakan secara umum.

Adapun contoh aplikasi yang dibuat sebagai berikut

Contoh Code pada module:
(Code pada module ini menggunakan Connection String ke database yang berasal dari file notepad “ClientObject.ini”, dan menggunakan form koneksi pada postingnya yang lalu)

Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Module mdlRef4General
    Public sqlConn As New SqlConnection
    Public sqlCmd As New SqlCommand
    Public sqlDA As New SqlDataAdapter
    Private m_ConnString As String
    Private m_SR As StreamReader

    'mengambil PATH atau lokasi,
    'dimana file EXE berada (akar program)
    Private m_BasePath As String = _
            AppDomain.CurrentDomain.BaseDirectory

    Private Sub openDbConnApp()
        If File.Exists(m_BasePath & "Database Connect.exe") Then
            'membuka program untuk menyimpan Connection String
            Shell(m_BasePath & "Database Connect.exe", _
                    AppWinStyle.NormalFocus)
        Else
            MessageBox.Show("Aplikasi untuk menyimpan Connection String " & _
                        vbCrLf & _
                  "TIDAK DITEMUKAN, silahkan letakan pada tempatnya", "ERROR", _
                 MessageBoxButtons.OK, MessageBoxIcon.Error)
        End If
    End Sub
    'mengambil connection string
    'untuk melakukan koneksi ke SQL Server
    Private Function getConnectionString() As String
        'Dim strConnString As String = ""
        'mengecek apakah file ClientObject.ini ada
        If File.Exists(m_BasePath & "ClientObject.ini") Then
            'membuka file ClientObject.ini jika filenya ada
            m_SR = File.OpenText(m_BasePath & "ClientObject.ini")
            'membaca baris connection string dan menampung ke variabel
            m_ConnString = m_SR.ReadLine
            'menutup Stream Reader
            m_SR.Close()
        Else
            openDbConnApp()
            'jika file ClientObject.ini tidak ada,
            'maka variabel akan dikosongkan
            m_ConnString = ""
        End If
        Return m_ConnString
    End Function
    Public Function tryConnection() As Boolean
        m_ConnString = getConnectionString()
        Try
            'memasukan isi dari m_ConnString ke property Connection
            sqlConn.ConnectionString = m_ConnString
            'membuka koneksi
            sqlConn.Open()
            'jika berhasil maka akan mengembalikan nilai TRUE
            Return True
        Catch ex As Exception
            MessageBox.Show(ex.Message, "ERROR", _
            MessageBoxButtons.OK, MessageBoxIcon.Error)

            openDbConnApp()
            'koneksi gagal, maka akan mengembalikan nilai FALSE
            Return False
        Finally
            'jika sqlConn(Connection) dalam keadaan BUKA,
            'maka koneksi harus ditutup
            If sqlConn.State = ConnectionState.Open Then
                sqlConn.Close()
            End If
        End Try
    End Function

    'function ini dapat digunakan untuk
    'INSERT, UPDATE dan DELETE data
    Public Function InUpDeData(ByVal strQuery As String) As Boolean
        m_ConnString = getConnectionString()
        If m_ConnString = "" Then
            Return False
            Exit Function
        End If

        Try
            sqlConn.ConnectionString = m_ConnString
            sqlCmd.CommandText = strQuery
            sqlCmd.Connection = sqlConn
            sqlConn.Open()
            sqlCmd.ExecuteNonQuery()
            Return True
        Catch ex As Exception
            MessageBox.Show(ex.Message, "ERROR", _
                            MessageBoxButtons.OK, MessageBoxIcon.Error)
            Return False
        Finally
            If sqlConn.State = ConnectionState.Open Then
                sqlConn.Close()
            End If
        End Try
    End Function

    Public Function GetData(ByVal strQuery As String) As DataSet
        Dim dtsResult As New DataSet
        m_ConnString = My.Settings.ConnString
   
        Try
            sqlConn = New SqlConnection(m_ConnString)
            sqlDA.SelectCommand = New SqlCommand(strQuery, sqlConn)
            sqlDA.SelectCommand.CommandText = strQuery
            sqlDA.Fill(dtsResult)
   
        Catch ex As Exception
            MessageBox.Show(ex.Message, "ERROR", _
                            MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If sqlConn.State = ConnectionState.Open Then
                sqlConn.Close()
            End If
        End Try
        Return dtsResult
    End Function
End Module

Contoh Code pada Form:
Public Class frmRegister
    Dim AddEdit As String
    Private Sub AllowInput(ByVal tf As Boolean)
        txtNPM.ReadOnly = Not tf
        txtNama.ReadOnly = Not tf
        txtAlamat.ReadOnly = Not tf
        txtNoHP.ReadOnly = Not tf
        DataGridView1.Enabled = Not tf
    End Sub
    Private Sub ClearInput()
        txtNPM.Text = ""
        txtNama.Text = ""
        txtAlamat.Text = ""
        txtNoHP.Text = ""
        txtNPM.Focus()
    End Sub
    Private Sub frmRegister_Load(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles MyBase.Load

        If tryConnection() = False Then
            Me.Close()
        End If
        AllowInput(False)
        refreshDataGrid()
    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles btnSave.Click
        Try
            If AddEdit = "add" Then
                If InUpDeData("INSERT INTO tblStudent " & _
                          "(NPM, Nama, Alamat, NoHP, CreateDate) " & _
                          "VALUES ('" & txtNPM.Text & "','" & txtNama.Text & _
                            "','" & txtAlamat.Text & "','" & _
                          txtNoHP.Text & "','" & Now & "')") = True Then
                    MessageBox.Show("Data Berhasil disimpan", "SUCCEED", _
                                           MessageBoxButtons.OK, MessageBoxIcon.Information)
                End If


            ElseIf AddEdit = "edit" Then
                If InUpDeData("UPDATE tblStudent " & _
                           "SET Nama='" & txtNama.Text & "', Alamat='" & txtAlamat.Text & _
                           "', NoHP='" & txtNoHP.Text & "', CreateDate='" & Now & _
                           "' WHERE NPM='" & txtNPM.Text & "'") = True Then
                    MessageBox.Show("Data Berhasil diubah", "SUCCEED", _
                                           MessageBoxButtons.OK, MessageBoxIcon.Information)
                End If

            End If
            refreshDataGrid()
            AllowInput(False)
        Catch ex As Exception
            MessageBox.Show(ex.Message, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try



    End Sub
    Private Sub btnAdd_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles btnAdd.Click
        AddEdit = "add"
        AllowInput(True)
        ClearInput()
    End Sub
    Private Sub btnEdit_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles btnEdit.Click
        AddEdit = "edit"
        AllowInput(True)
        txtNPM.ReadOnly = True
    End Sub
    Private Sub refreshDataGrid()
        Dim dtsResult As New DataSet
        Try
            dtsResult.Tables.Clear()
            dtsResult = GetData("SELECT * FROM tblStudent ORDER BY ID ASC")
            DataGridView1.DataSource = dtsResult.Tables(0)
        Catch ex As Exception
            MessageBox.Show(ex.Message, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

    End Sub
    Private Sub DataGridView1_CurrentCellChanged(ByVal sender As Object, _
    ByVal e As System.EventArgs) Handles DataGridView1.CurrentCellChanged
        On Error Resume Next
        Dim intCRow As Integer
        intCRow = DataGridView1.CurrentRow.Index
        If intCRow < 0 Then Exit Sub
        txtNPM.Text = DataGridView1(1, intCRow).Value
        txtNama.Text = DataGridView1(2, intCRow).Value
        txtAlamat.Text = DataGridView1(3, intCRow).Value
        txtNoHP.Text = DataGridView1(4, intCRow).Value
    End Sub


    Private Sub btnCancel_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles btnCancel.Click
        AllowInput(False)
        refreshDataGrid()
    End Sub

    Private Sub btnDelete_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles btnDelete.Click
        If MessageBox.Show("Apakah Anda yakin ingin menghapus data ini?", "Cofirmation", _
        MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
            If InUpDeData("DELETE FROM tblStudent WHERE NPM='" & txtNPM.Text & "'") = True Then
                MessageBox.Show("Data berhasil dihapus")
                refreshDataGrid()
            End If
        End If
    End Sub
End Class

Download Source Lengkap : DOWNLOAD

NB: Bagi yang kurang mengerti silahkan comment pada post ini atau email

Silahkan modifikasi sesuai dengan kebutuhan.

No comments: