Pertama-tama pisahkan dahulu koneksi database di Class Connection.vb :
Imports System.Data.OleDb
Public Class Connection
'Public Shared _con_str As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & System.IO.Path.GetDirectoryName(Application.ExecutablePath) & "\DatabasePerpus.mdb"
Public Shared _con_str As String = System.IO.File.ReadAllText(System.IO.Path.GetDirectoryName(Application.ExecutablePath) & "\settings.txt")
Public Sub Connection()
'_con_str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & System.IO.Path.GetDirectoryName(Application.ExecutablePath) & "\DatabasePerpus.mdb"
_con_str = System.IO.File.ReadAllText(System.IO.Path.GetDirectoryName(Application.ExecutablePath) & "\settings.txt")
End Sub
Public Property CON_STR() As String
Get
Return _con_str
End Get
Private Set(ByVal value As String)
_con_str = value
End Set
End Property
Public Shared ReadOnly Property CON() As OleDbConnection
Get
Return New OleDbConnection(_con_str)
End Get
End Property
End Class
Kemudian buat folder Entity untuk menempatkan file Entity/Buku.vb :
Public Class Buku
Dim _idbuku As String
Dim _judul As String
Dim _ringkasan As String
Dim _tglterbit As Date
Dim _harga As Double
Dim _cover As Image
Public Sub Buku()
_idbuku = ""
_judul = ""
_ringkasan = ""
_tglterbit = Now
_harga = 0
_cover = Nothing
End Sub
Public Sub Buku(ByVal idbuku As String, ByVal judul As String, ByVal ringkasan As String, ByVal tglterbit As Date, ByVal harga As Double, ByVal cover As Image)
_idbuku = idbuku
_judul = judul
_ringkasan = ringkasan
_tglterbit = tglterbit
_harga = harga
_cover = cover
End Sub
Public Property IdBuku() As String
Get
Return _idbuku
End Get
Set(ByVal value As String)
_idbuku = value
End Set
End Property
Public Property Judul() As String
Get
Return _judul
End Get
Set(ByVal value As String)
_judul = value
End Set
End Property
Public Property Ringkasan() As String
Get
Return _ringkasan
End Get
Set(ByVal value As String)
_ringkasan = value
End Set
End Property
Public Property TglTerbit() As Date
Get
Return _tglterbit
End Get
Set(ByVal value As Date)
_tglterbit = value
End Set
End Property
Public Property Harga() As Double
Get
Return _harga
End Get
Set(ByVal value As Double)
_harga = value
End Set
End Property
Public Property Cover() As Image
Get
Return _cover
End Get
Set(ByVal value As Image)
_cover = value
End Set
End Property
End Class
Kemudian buat folder Control untuk menempatkan file Control/BukuManager.vb :
Imports System.Data.OleDb
Imports System.IO
Public Class BukuManager
Public Sub BukuManager()
End Sub
Public Function addBuku(ByVal _buku As Buku) As Boolean
Dim con As OleDbConnection = Connection.CON
Try
Dim ms As MemoryStream = New MemoryStream()
_buku.Cover.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg)
Dim Pic_arr(ms.Length) As Byte
ms.Position = 0
ms.Read(Pic_arr, 0, Pic_arr.Length)
con.Open()
Dim sc As OleDbCommand = New OleDbCommand("INSERT INTO BUKU VALUES('" + _buku.IdBuku + "','" + _buku.Judul + "','" + _buku.Ringkasan + "',#" + _buku.TglTerbit.ToShortDateString + "#," + _buku.Harga.ToString + ", @cover)", con)
Console.WriteLine(sc.CommandText)
sc.Parameters.AddWithValue("@cover", Pic_arr)
sc.ExecuteNonQuery()
Return True
Catch e As System.Data.SqlClient.SqlException
Return False
Catch e2 As Exception
Return False
Finally
con.Close()
End Try
End Function
'public bool validateIsbn(string isbn)
'{
' SqlConnection con = Connection.CON;
' Try
' {
' con.Open();
' SqlCommand sc = new SqlCommand("SELECT * FROM BUKU where ISBN= '" + isbn + "'", con);
' SqlDataReader dr = sc.ExecuteReader();
' return (dr.HasRows ? false : true);
' }
' catch (System.Data.SqlClient.SqlException e)
' {
' return false;
' }
' catch (Exception e1)
' {
' return false;
' }
' Finally
' {
' con.Close();
' }
'}
Public Function getAllBuku() As DataTable
Dim dt As DataTable = New DataTable()
Dim con As OleDbConnection = Connection.CON
Try
con.Open()
Dim ad As OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM BUKU", con)
ad.Fill(dt)
Return (dt)
Catch e As System.Data.OleDb.OleDbException
Console.WriteLine(e.Message)
Return Nothing
Catch e2 As Exception
Console.WriteLine(e2.Message)
Return Nothing
Finally
con.Close()
End Try
End Function
Public Function updateBukuByidbuku(ByVal idbukulama As String, ByVal _buku As Buku) As Boolean
Dim con As OleDbConnection = Connection.CON
Try
con.Open()
Dim sc As OleDbCommand = New OleDbCommand("UPDATE BUKU SET IDBUKU ='" + _buku.IdBuku + "', Judul='" + _buku.Judul + "', Ringkasan='" + _buku.Ringkasan + "', TglTerbit=#" + _buku.TglTerbit.ToShortDateString + "#, Harga=" + _buku.Harga.ToString + " WHERE Idbuku='" + idbukulama + "'", con)
Console.WriteLine(sc.CommandText)
sc.ExecuteNonQuery()
Return True
Catch e As System.Data.OleDb.OleDbException
Return False
Catch e2 As Exception
Return False
Finally
con.Close()
End Try
End Function
Public Function deleteBukuByidbuku(ByVal idbukulama As String) As Boolean
Dim con As OleDbConnection = Connection.CON
Try
con.Open()
Dim sc As OleDbCommand = New OleDbCommand("DELETE FROM BUKU WHERE IDBUKU = '" + idbukulama + "'", con)
sc.ExecuteNonQuery()
Return True
Catch e As System.Data.OleDb.OleDbException
Return False
Catch e2 As Exception
Return False
Finally
con.Close()
End Try
End Function
End Class
Setelah itu terakhir buat folder Boundary untuk menempatkan file Boundary/BukuUI.vb :
Imports System.IO
Public Class BukuUI
Dim idbukulama As String = ""
Private Sub BukuUI()
RefreshGrid()
End Sub
Private Sub RefreshGrid()
Dim dt As DataTable = New DataTable()
Dim bukMan As BukuManager = New BukuManager()
Try
dt.Clear()
dt = bukMan.getAllBuku()
dgBuku.DataSource = dt
Catch e1 As System.Data.OleDb.OleDbException
MessageBox.Show("Terjadi kesalahan pada Koneksi database" + e1.Message.ToString().Trim(), "Perhatian", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
Catch e2 As Exception
MessageBox.Show("Terjadi kesalahan pada Koneksi database" + e2.Message.ToString().Trim(), "Perhatian", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
End Try
End Sub
Private Sub BukuUI_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
RefreshGrid()
End Sub
Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
Dim _buku As Buku = New Buku()
Dim bukMan As BukuManager = New BukuManager()
If (txtID.Text.Trim() <> String.Empty And txtJudul.Text.Trim() <> String.Empty And txtRingkasan.Text.Trim() <> String.Empty And txtHarga.Text.Trim() <> String.Empty) Then
'If (bukMan.validateIsbn(textISBN.Text.Trim())) Then
_buku.IdBuku = txtID.Text.Trim()
_buku.Judul = txtJudul.Text.Trim()
_buku.Ringkasan = txtRingkasan.Text.Trim()
_buku.TglTerbit = Date.Parse(txtTgl.Text.Trim())
_buku.Harga = Double.Parse(txtHarga.Text)
If Not PicCover.Image Is Nothing Then
_buku.Cover = PicCover.Image
'Dim ms As MemoryStream = New MemoryStream()
'PicCover.Image.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg)
'Dim Pic_arr(ms.Length) As Byte
'ms.Position = 0
'ms.Read(Pic_arr, 0, Pic_arr.Length)
End If
If (bukMan.addBuku(_buku)) Then
MessageBox.Show("Data berhasil disimpan!", "Informasi", MessageBoxButtons.OK, MessageBoxIcon.Information)
RefreshGrid()
Else
MessageBox.Show("Data gagal disimpan", "Perhatian", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
End If
'Else
' MessageBox.Show("Judul '" + txtJudul.Text.Trim() + "' sudah terdaftar di database, masukkan Judul yang lain", "Informasi", MessageBoxButtons.OK, MessageBoxIcon.Information)
'End If
Else
MessageBox.Show("Masukkan data dengan benar!", "Perhatian", MessageBoxButtons.OK, MessageBoxIcon.Information)
End If
End Sub
Private Sub dgBuku_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgBuku.CellClick
txtID.Text = dgBuku.CurrentRow.Cells("idbuku").Value.ToString
txtJudul.Text = dgBuku.CurrentRow.Cells("judul").Value.ToString
txtRingkasan.Text = dgBuku.CurrentRow.Cells("ringkasan").Value.ToString
txtTgl.Text = dgBuku.CurrentRow.Cells("tglterbit").Value.ToString
txtHarga.Text = dgBuku.CurrentRow.Cells("harga").Value.ToString
idbukulama = txtID.Text
End Sub
Private Sub dgBuku_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgBuku.CellContentClick
End Sub
Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click
Dim _buku As Buku = New Buku()
Dim bukMan As BukuManager = New BukuManager()
If (txtID.Text.Trim() <> String.Empty And txtJudul.Text.Trim() <> String.Empty And txtRingkasan.Text.Trim() <> String.Empty And txtHarga.Text.Trim() <> String.Empty) Then
'If (bukMan.validateIsbn(textISBN.Text.Trim())) Then
_buku.IdBuku = txtID.Text.Trim()
_buku.Judul = txtJudul.Text.Trim()
_buku.Ringkasan = txtRingkasan.Text.Trim()
_buku.TglTerbit = Date.Parse(txtTgl.Text.Trim())
_buku.Harga = Double.Parse(txtHarga.Text)
If (bukMan.updateBukuByidbuku(idbukulama, _buku)) Then
MessageBox.Show("Data berhasil diupdate!", "Informasi", MessageBoxButtons.OK, MessageBoxIcon.Information)
RefreshGrid()
Else
MessageBox.Show("Data gagal diupdate", "Perhatian", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
End If
'Else
' MessageBox.Show("Judul '" + txtJudul.Text.Trim() + "' sudah terdaftar di database, masukkan Judul yang lain", "Informasi", MessageBoxButtons.OK, MessageBoxIcon.Information)
'End If
Else
MessageBox.Show("Masukkan data dengan benar!", "Perhatian", MessageBoxButtons.OK, MessageBoxIcon.Information)
End If
End Sub
Private Sub BtnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnDelete.Click
Dim bukman As BukuManager = New BukuManager
If (bukman.deleteBukuByidbuku(idbukulama)) Then
MessageBox.Show("Data berhasil dihapus!", "Informasi", MessageBoxButtons.OK, MessageBoxIcon.Information)
RefreshGrid()
Else
MessageBox.Show("Data gagal dihapus!", "Perhatian", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
End If
End Sub
Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click
txtID.Text = ""
txtJudul.Text = ""
txtRingkasan.Text = ""
txtTgl.Text = ""
txtHarga.Text = ""
End Sub
Private Sub btnbrowse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnbrowse.Click
If (OD.ShowDialog = Windows.Forms.DialogResult.OK) Then
PicCover.Image = Image.FromFile(OD.FileName)
End If
End Sub
End Class
