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
NB: Bagi yang kurang mengerti silahkan comment pada post ini atau email
Silahkan modifikasi sesuai dengan kebutuhan.
