DataTable (tabel data) adalah komponen dimana baris data yang
diperoleh dari sumber data ditampung sebelum diproses lebih
lanjut. Baris data ini kemudian direferensi melalui objek DataTable.
Perubahan yang terjadi pada DataTable ditangani library .NET
Framework, jadi SQL Server sudah tidak terlibat lagi.
Kita telah mengetahui bahwa ADO.NET bekerja dalam bentuk
hubungan terputus sehingga perubahan yang terjadi pada tabel
data tidak akan mempengaruhi sumber data sebelum program
menjalankan proses modifikasi, baik melalui objek Command
maupun objek DataAdapter sehingga program dapat
memperlakukan DataTable sebagai „file sementara‰ untuk
berbagai keperluan.
Setelah objek DataTable dideklarasikan, objek tersebut dikaitkan
ke komponen tabel objek DataSet, selanjutnya digunakan nama
variabel objek data tabel untuk melakukan aktifitas yang terkait
dengan objek tersebut, misalnya memperoleh baris data,
mengambil nilai dari kolom tertentu, atau memindahkan posisi
record/pointer ke baris tertentu.
Perintah BindingContext diperlukan ketika akan dilakukan
perubahan yang mereferensi objek data tabel, baik itu untuk
memindahkan posisi record, memperoleh baris data, maupun
menghapus baris. Khusus untuk menambah baris baru atau
memperbarui record, program harus menjalankan perintah
objDataTable.AcceptChanges() sebelum melakukan perubahan
pada DataTabel.
Bila program melakukan penghapusan suatu baris record,
posisi/pointer ke record tersebut harus dipindahkan karena
keberadaan pointer tersebut telah terhapus (ditiadakan). Bila
posisi/pointer tidak dipindahkan, program akan crash. Hal yang
sama (crash) juga akan terjadi bila program mencoba mereferensi
ke pointer (posisi record) yang telah dihapus.
Properti row yang dimiliki oleh DataTable bisa jadi merupakan
properti yang paling sering digunakan. Melalui properti rows.items,
dapat diperoleh data dari masing-masing kolom. Biasanya properti.
tersebut digunakan untuk mengatur nilai ke baris tersebut. Di
samping itu, properti rows.count sangat berguna untuk mengetahui
jumlah baris yang dimiliki objek DataTable. Dengan menghitung
jumlah baris yang ada, program dapat mengetahui apakah suatu
record telah exist/ada pada database atau belum.
tampaknya cukup jelas untuk memberi ilustrasi penggunaan objek
DataTable. Proses update sumber data (database) tidak
dicontohkan di sini karena Anda dapat melihat variasi update
database pada contoh Aplikasi Program Penjualan Barang.
Silahkan memperhatikan listing program dan bermain-main
(berimaginasi) dengan record yang akan ditampilkan pada layar
ketika Anda menekan/mengklik tombol navigasi atau menambah
sembarang baris baru. Jangan bermain-main dulu dengan proses
penghapusan karena ada hal menarik yang terjadi di sana.
Public Class objTabelData
Inherits System.Windows.Forms.Form
#Region " Windows Form Designer generated code "
Public Sub New()
MyBase.New()
'This call is required by the Windows Form Designer.
InitializeComponent()
'Add any initialization after the
InitializeComponent()
call
End Sub
'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing
As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Windows
‘Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents DataGrid1 As
System.Windows.Forms.DataGrid
Friend WithEvents Label1 As System.Windows.Forms.Label
Friend WithEvents TextBox1 As
System.Windows.Forms.TextBox
Friend WithEvents TextBox2 As
System.Windows.Forms.TextBox
Friend WithEvents Label2 As System.Windows.Forms.Label
Friend WithEvents TextBox3 As
System.Windows.Forms.TextBox
Friend WithEvents Label3 As System.Windows.Forms.Label
Friend WithEvents TextBox6 As
System.Windows.Forms.TextBox
Friend WithEvents Label6 As System.Windows.Forms.Label
Friend WithEvents TextBox5 As
System.Windows.Forms.TextBox
Friend WithEvents Label5 As System.Windows.Forms.Label
Friend WithEvents TextBox4 As
System.Windows.Forms.TextBox
Friend WithEvents Label4 As System.Windows.Forms.Label
Friend WithEvents TextBox7 As
System.Windows.Forms.TextBox
Friend WithEvents Label7 As System.Windows.Forms.Label
Friend WithEvents Btn_Buttom As
System.Windows.Forms.Button
Friend WithEvents Btn_Previous As
System.Windows.Forms.Button
Friend WithEvents Btn_Next As System.Windows.Forms.Button
Friend WithEvents Btn_Top As System.Windows.Forms.Button
Friend WithEvents Button1 As System.Windows.Forms.Button
Friend WithEvents Button2 As System.Windows.Forms.Button
Friend WithEvents TextBox8 As
System.Windows.Forms.TextBox
Friend WithEvents Label8 As System.Windows.Forms.Label
<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()
Me.DataGrid1 = New System.Windows.Forms.DataGrid
Me.Label1 = New System.Windows.Forms.Label
Me.TextBox1 = New System.Windows.Forms.TextBox
Me.TextBox2 = New System.Windows.Forms.TextBox
Me.Label2 = New System.Windows.Forms.Label
Me.TextBox3 = New System.Windows.Forms.TextBox
Me.Label3 = New System.Windows.Forms.Label
Me.TextBox6 = New System.Windows.Forms.TextBox
Me.Label6 = New System.Windows.Forms.Label
Me.TextBox5 = New System.Windows.Forms.TextBox
Me.Label5 = New System.Windows.Forms.Label
Me.TextBox4 = New System.Windows.Forms.TextBox
Me.Label4 = New System.Windows.Forms.Label
Me.TextBox7 = New System.Windows.Forms.TextBox
Me.Label7 = New System.Windows.Forms.Label
Me.Btn_Buttom = New System.Windows.Forms.Button
Me.Btn_Previous = New System.Windows.Forms.Button
Me.Btn_Next = New System.Windows.Forms.Button
Me.Btn_Top = New System.Windows.Forms.Button
Me.Button1 = New System.Windows.Forms.Button
Me.Button2 = New System.Windows.Forms.Button
Me.TextBox8 = New System.Windows.Forms.TextBox
Me.Label8 = New System.Windows.Forms.Label
CType(Me.DataGrid1,
System.ComponentModel.ISupportInitialize).BeginInit()
Me.SuspendLayout()
'
'DataGrid1
'
Me.DataGrid1.DataMember = ""
Me.DataGrid1.HeaderForeColor =
System.Drawing.SystemColors.ControlText
Me.DataGrid1.Location = New System.Drawing.Point(0, 0)
Me.DataGrid1.Name = "DataGrid1"
Me.DataGrid1.Size = New System.Drawing.Size(240, 128)
Me.DataGrid1.TabIndex = 0
'
'Label1
'
Me.Label1.Location = New System.Drawing.Point(248, 16)
Me.Label1.Name = "Label1"
Me.Label1.Size = New System.Drawing.Size(104, 16)
Me.Label1.TabIndex = 1
'
'TextBox1
'
Me.TextBox1.Location = New System.Drawing.Point
(376, 8)
Me.TextBox1.Name = "TextBox1"
Me.TextBox1.TabIndex = 2
Me.TextBox1.Text = ""
'TextBox2
'
Me.TextBox2.Location = New System.Drawing.Point
(376, 40)
Me.TextBox2.Name = "TextBox2"
Me.TextBox2.TabIndex = 4
Me.TextBox2.Text = ""
'
'Label2
'
Me.Label2.Location = New System.Drawing.Point(248, 48)
Me.Label2.Name = "Label2"
Me.Label2.Size = New System.Drawing.Size(104, 16)
Me.Label2.TabIndex = 3
'
'TextBox3
'
Me.TextBox3.Location = New System.Drawing.Point
(376, 72)
Me.TextBox3.Name = "TextBox3"
Me.TextBox3.TabIndex = 6
Me.TextBox3.Text = ""
'
'Label3
'
Me.Label3.Location = New System.Drawing.Point(248, 80)
Me.Label3.Name = "Label3"
Me.Label3.Size = New System.Drawing.Size(104, 16)
Me.Label3.TabIndex = 5
'
'TextBox6
'
Me.TextBox6.Location = New System.Drawing.Point
(200, 264)
Me.TextBox6.Name = "TextBox6"
Me.TextBox6.TabIndex = 13
Me.TextBox6.Text = ""
'
'Label6
'
Me.Label6.Location = New System.Drawing.Point(16, 264)
Me.Label6.Name = "Label6"
Me.Label6.Size = New System.Drawing.Size(168, 16)
Me.Label6.TabIndex = 12
'
'TextBox5
'
Me.TextBox5.Location = New System.Drawing.Point
(200, 232)
Me.TextBox5.Name = "TextBox5"
Me.TextBox5.TabIndex = 11
Me.TextBox5.Text = ""
'
'Label5
'
Me.Label5.Location = New System.Drawing.Point(16, 232)
Me.Label5.Name = "Label5"
Me.Label5.Size = New System.Drawing.Size(168, 16)
Me.Label5.TabIndex = 10
'
'TextBox4
Me.TextBox4.Location = New System.Drawing.Point
(200, 200)
Me.TextBox4.Name = "TextBox4"
Me.TextBox4.TabIndex = 9
Me.TextBox4.Text = ""
'
'Label4
'
Me.Label4.Location = New System.Drawing.Point(16, 200)
Me.Label4.Name = "Label4"
Me.Label4.Size = New System.Drawing.Size(168, 16)
Me.Label4.TabIndex = 8
'
'TextBox7
'
Me.TextBox7.Location = New System.Drawing.Point
(392, 232)
Me.TextBox7.Name = "TextBox7"
Me.TextBox7.TabIndex = 16
Me.TextBox7.Text = ""
'
'Label7
'
Me.Label7.Location = New System.Drawing.Point
(360, 208)
Me.Label7.Name = "Label7"
Me.Label7.Size = New System.Drawing.Size(168, 16)
Me.Label7.TabIndex = 15
'
'Btn_Buttom
'
Me.Btn_Buttom.Font = New
System.Drawing.Font("Microsoft
Sans Serif", 8.25!, System.Drawing.FontStyle.Bold,
System.Drawing.GraphicsUnit.Point, CType(0, Byte))
Me.Btn_Buttom.Location = New System.Drawing.Point
(176, 136)
Me.Btn_Buttom.Name = "Btn_Buttom"
Me.Btn_Buttom.Size = New System.Drawing.Size(48, 24)
Me.Btn_Buttom.TabIndex = 24
Me.Btn_Buttom.Text = ">|"
'
'Btn_Previous
'
Me.Btn_Previous.Font = New
System.Drawing.Font("Microsoft Sans Serif", 8.25!,
System.Drawing.FontStyle.Bold,
System.Drawing.GraphicsUnit.Point, CType(0, Byte))
Me.Btn_Previous.Location = New System.Drawing.Point
(64, 136)
Me.Btn_Previous.Name = "Btn_Previous"
Me.Btn_Previous.Size = New System.Drawing.Size(48, 24)
Me.Btn_Previous.TabIndex = 22
Me.Btn_Previous.Text = "<"
'
'Btn_Next
'
Me.Btn_Next.Font = New System.Drawing.Font("Microsoft
Sans Serif", 8.25!, System.Drawing.FontStyle.Bold,
System.Drawing.GraphicsUnit.Point, CType(0, Byte))
Me.Btn_Next.Location = New System.Drawing.Point
(120, 136)
Me.Btn_Next.Name = "Btn_Next"
Me.Btn_Next.Size = New System.Drawing.Size(48, 24)
Me.Btn_Next.TabIndex = 23
Me.Btn_Next.Text = ">"
'
'Btn_Top
'
Me.Btn_Top.Font = New System.Drawing.Font("Microsoft
Sans Serif", 8.25!, System.Drawing.FontStyle.Bold,
System.Drawing.GraphicsUnit.Point, CType(0, Byte))
Me.Btn_Top.Location = New System.Drawing.Point(8, 136)
Me.Btn_Top.Name = "Btn_Top"
Me.Btn_Top.Size = New System.Drawing.Size(48, 24)
Me.Btn_Top.TabIndex = 21
Me.Btn_Top.Text = "|<"
'
'Button1
'
Me.Button1.Location = New System.Drawing.Point
(16, 296)
Me.Button1.Name = "Button1"
Me.Button1.TabIndex = 25
Me.Button1.Text = "ADD"
'
'Button2
'
Me.Button2.Location = New System.Drawing.Point
(392, 264)
Me.Button2.Name = "Button2"
Me.Button2.TabIndex = 26
Me.Button2.Text = "DELETE"
'
'TextBox8
'
Me.TextBox8.Location = New System.Drawing.Point
(200, 328)
Me.TextBox8.Name = "TextBox8"
Me.TextBox8.TabIndex = 28
Me.TextBox8.Text = ""
'
'Label8
'
Me.Label8.Location = New System.Drawing.Point(16, 328)
Me.Label8.Name = "Label8"
Me.Label8.Size = New System.Drawing.Size(168, 16)
Me.Label8.TabIndex = 27
'
'objTabelData
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(536, 365)
Me.Controls.Add(Me.TextBox8)
Me.Controls.Add(Me.Label8)
Me.Controls.Add(Me.Button2)
Me.Controls.Add(Me.Button1)
Me.Controls.Add(Me.Btn_Buttom)
Me.Controls.Add(Me.Btn_Previous)
Me.Controls.Add(Me.Btn_Next)
Me.Controls.Add(Me.Btn_Top)
Me.Controls.Add(Me.TextBox7)
Me.Controls.Add(Me.Label7)
Me.Controls.Add(Me.TextBox6)
Me.Controls.Add(Me.Label6)
Me.Controls.Add(Me.TextBox5)
Me.Controls.Add(Me.Label5)
Me.Controls.Add(Me.TextBox4)
Me.Controls.Add(Me.Label4)
Me.Controls.Add(Me.TextBox3)
Me.Controls.Add(Me.Label3)
Me.Controls.Add(Me.TextBox2)
Me.Controls.Add(Me.Label2)
Me.Controls.Add(Me.TextBox1)
Me.Controls.Add(Me.Label1)
Me.Controls.Add(Me.DataGrid1)
Me.Name = "objTabelData"
Me.Text = "objTabelData"
CType(Me.DataGrid1,
System.ComponentModel.ISupportInitialize).EndInit()
Me.ResumeLayout(False)
End Sub
#End Region
Dim objConnection As SqlConnection
Dim objCommand As SqlCommand
Dim objDataAdapter As SqlDataAdapter
Dim objDataSet As New DataSet
Dim strConn, strSQL As String
Dim objDataTable As New DataTable
Private Sub objTabelData_Load(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
MyBase.Load
Dim form_MenuInduk As New MenuInduk
strConn = form_MenuInduk.mRoot_StrConn
strSQL = "select album_id, album_singer, album_title
from AlbumList order by album_id"
objConnection = New SqlConnection(strConn)
objCommand = New SqlCommand(strSQL, objConnection)
objConnection.Open()
objDataAdapter = New SqlDataAdapter(objCommand)
objDataAdapter.Fill(objDataSet, "mDT_albumlist")
objDataTable = objDataSet.Tables("mDT_AlbumList")
DataGrid1.DataSource = objDataTable
DataGrid1.ReadOnly = True
objConnection.Close()
Label1.Text = "posisi record"
Label2.Text = "Album ID"
Label3.Text = "Judul Album"
Label4.Text = "Album ID"
Label5.Text = "Penyanyi"
Label6.Text = "Judul Album"
Label7.Text = "posisi record untuk dihapus (>0)"
Label8.Text = "posisi record yang di tambah"
End Sub
Private Sub Display_data()
Dim mPosition As Integer =
BindingContext(objDataTable).Position
TextBox1.Text = mPosition
With objDataTable.Rows(mPosition)
TextBox2.Text = .Item("Album_ID")
TextBox3.Text = .Item("Album_title")
End With
End Sub
Private Sub Btn_Top_Click(ByVal sender As
System.Object,
ByVal e As System.EventArgs) Handles Btn_Top.Click
BindingContext(objDataTable).Position = 0
Display_data()
End Sub
Private Sub Btn_Previous_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
Btn_Previous.Click
BindingContext(objDataTable).Position -= 1
Display_data()
End Sub
Private Sub Btn_Next_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles Btn_Next.Click
BindingContext(objDataTable).Position += 1
Display_data()
End Sub
Private Sub Btn_Buttom_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
Btn_Buttom.Click
BindingContext(objDataTable).Position =
objDataTable.Rows.Count - 1
Display_data()
End Sub
Private Sub Button1_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles Button1.Click
objDataSet.AcceptChanges()
BindingContext(objDataTable).AddNew()
Dim mPos As Integer =
BindingContext(objDataTable).Position
BindingContext(objDataTable).Position = mPos
With objDataTable.Rows(mPos)
.Item("Album_ID") = TextBox4.Text
.Item("Album_Singer") = TextBox5.Text
.Item("Album_Title") = TextBox6.Text
End With
TextBox8.Text = mPos
End Sub
Private Sub Button2_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles Button2.Click
Dim mPosHapus As Integer = TextBox7.Text
Dim Row As DataRow = objDataTable.Rows(mPosHapus)
Row.Delete()
BindingContext(objDataTable).Position -= 1
End Sub
End Class
Apa gerangan hal menarik dari proses penghapusan record pada
tabel data? Sekilas memang tidak ada hal yang istimewa, tetapi
coba jalankan program dan hapus posisi record ke-3 (isi dengan
angka 2 pada kolom entri) dan tekan tombol Delete. Anda akan
melihat record tersebut dihapus dari tampilan DataGrid.
Coba lakukan navigasi ke Beginning of Record (|<) dan End of
Record (>|). Posisi record yang ditampilkan baik-baik saja. Akan
tetapi cobalah mulai melakukan navigasi baris satu per satu.
Booomm!! Program crash saat mendekati posisi record ke-3.
Coba jalankan kembali program tersebut dan hapus kembali posisi
record ke-3, dilanjutkan dengan menambahkan record baru.
Silahkan menggunakan tombol navigasi dan program akan
berjalan dengan mulus. Lakukan kembali penghapusan posisi
record ke-3. Anda akan melihat baris yang bersangkutan dihapus.
Selanjutnya bila Anda mengerakan tombol navigasi maka program
akan crash, tetapi bila dilakukan penambahan record baru,
program akan bekerja dengan normal pada pergerakan posisi
record. Singkatnya, dapat dikatakan tabel data baru akan di-
update secara fisik bila terjadi penambahan record baru.
Masalah yang muncul dalam penulisan aplikasi program adalah
bagaimana kita mengetahui seorang pengguna akan menambah
record baru setelah dia melakukan penghapusan? Bagaimana bila
dia mengerakan/navigasi posisi record? Tentunya kita tidak ingin
program crash.
Ingin tahu solusinya? Sabar saja, lupakan saja masalah ini untuk
sementara waktu, lebih baik belajar masalah lain karena
permasalahan tersebut akan diilustrasikan pada Aplikasi Program
Penjualan Barang dan Anda dapat melihat solusinya serta melihat
cara lain untuk menambah/menghapus baris tanpa melibatkan
sistem tombol navigasi.
Selain memperoleh (struktur) tabel data secara otomatis dari hasil
proses Selection Query, Anda dapat juga membuat (struktur) tabel
data melalui program dengan cara membuat objek data tabel baru,
membuat objek column dan menambahkan objek column ke
dalam objek data tabel, serta mengisi tabel data dengan baris
baru. Proses selanjutnya sama dengan proses struktur tabel yang
diperoleh dari proses Selection Query.
objTabelData2.vb:
Public Class objTabelData2
Inherits System.Windows.Forms.Form
#Region " Windows Form Designer generated code "
Public Sub New()
MyBase.New()
'This call is required by the Windows Form Designer.
InitializeComponent()
'Add any initialization after the
InitializeComponent()
call
End Sub
'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing
As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Windows
‘Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents DataGrid1 As
System.Windows.Forms.DataGrid
<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()
Me.DataGrid1 = New System.Windows.Forms.DataGrid
CType(Me.DataGrid1,
System.ComponentModel.ISupportInitialize).BeginInit()
Me.SuspendLayout()
'
'DataGrid1
'
Me.DataGrid1.DataMember = ""
Me.DataGrid1.HeaderForeColor =
System.Drawing.SystemColors.ControlText
Me.DataGrid1.Location = New System.Drawing.Point
(16, 16)
Me.DataGrid1.Name = "DataGrid1"
Me.DataGrid1.Size = New System.Drawing.Size(312, 152)
Me.DataGrid1.TabIndex = 0
'
'objTabelData2
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(344, 181)
Me.Controls.Add(Me.DataGrid1)
Me.Name = "objTabelData2"
Me.Text = "objTabelData2"
CType(Me.DataGrid1,
System.ComponentModel.ISupportInitialize).EndInit()
Me.ResumeLayout(False)
End Sub
#End Region
Private Sub objTabelData2_Load(ByVal sender As Object,
ByVal e As System.EventArgs) Handles MyBase.Load
'Buat objDataTable
Dim objDataTable As New DataTable
'Buat objek Column - objColumn dan set propertinya
Dim objColumn As DataColumn = New DataColumn
objColumn.DataType =
System.Type.GetType("System.String")
objColumn.AllowDBNull = False
objColumn.Caption = "EMP_ID"
objColumn.ColumnName = "Nomor Karyawan"
objColumn.DefaultValue = "KO000"
'Tambahkan objColumn ke tabel
objDataTable.Columns.Add(objColumn)
'Buat objek Column - objColumn1 dan set propertinya
objColumn = New DataColumn
objColumn.DataType =
System.Type.GetType("System.String")
objColumn.AllowDBNull = False
objColumn.Caption = "EMP_Name"
objColumn.ColumnName = "Nama Karyawan"
objDataTable.Columns.Add(objColumn)
' Tambahkan baris ke tabel
Dim objRow As DataRow
Dim i As Integer
For i = 0 To 5
objRow = objDataTable.NewRow()
objRow("Nomor Karyawan") = "K000" & Trim(Str(i))
objRow("Nama Karyawan") = "Nama Karyawan " & Str(i)
' Btambahkan baris baru ke dalam objDataTable
objDataTable.Rows.Add(objRow)
Next i
'set ukuran kolom dan row sebelum tabel dikaitkan ke
‘DataGrid
DataGrid1.PreferredColumnWidth() = 125
DataGrid1.PreferredRowHeight() = 20
DataGrid1.DataSource = objDataTable
End Sub
End Class
diperoleh dari sumber data ditampung sebelum diproses lebih
lanjut. Baris data ini kemudian direferensi melalui objek DataTable.
Perubahan yang terjadi pada DataTable ditangani library .NET
Framework, jadi SQL Server sudah tidak terlibat lagi.
Kita telah mengetahui bahwa ADO.NET bekerja dalam bentuk
hubungan terputus sehingga perubahan yang terjadi pada tabel
data tidak akan mempengaruhi sumber data sebelum program
menjalankan proses modifikasi, baik melalui objek Command
maupun objek DataAdapter sehingga program dapat
memperlakukan DataTable sebagai „file sementara‰ untuk
berbagai keperluan.
Setelah objek DataTable dideklarasikan, objek tersebut dikaitkan
ke komponen tabel objek DataSet, selanjutnya digunakan nama
variabel objek data tabel untuk melakukan aktifitas yang terkait
dengan objek tersebut, misalnya memperoleh baris data,
mengambil nilai dari kolom tertentu, atau memindahkan posisi
record/pointer ke baris tertentu.
Perintah BindingContext diperlukan ketika akan dilakukan
perubahan yang mereferensi objek data tabel, baik itu untuk
memindahkan posisi record, memperoleh baris data, maupun
menghapus baris. Khusus untuk menambah baris baru atau
memperbarui record, program harus menjalankan perintah
objDataTable.AcceptChanges() sebelum melakukan perubahan
pada DataTabel.
Bila program melakukan penghapusan suatu baris record,
posisi/pointer ke record tersebut harus dipindahkan karena
keberadaan pointer tersebut telah terhapus (ditiadakan). Bila
posisi/pointer tidak dipindahkan, program akan crash. Hal yang
sama (crash) juga akan terjadi bila program mencoba mereferensi
ke pointer (posisi record) yang telah dihapus.
Properti row yang dimiliki oleh DataTable bisa jadi merupakan
properti yang paling sering digunakan. Melalui properti rows.items,
dapat diperoleh data dari masing-masing kolom. Biasanya properti.
tersebut digunakan untuk mengatur nilai ke baris tersebut. Di
samping itu, properti rows.count sangat berguna untuk mengetahui
jumlah baris yang dimiliki objek DataTable. Dengan menghitung
jumlah baris yang ada, program dapat mengetahui apakah suatu
record telah exist/ada pada database atau belum.
tampaknya cukup jelas untuk memberi ilustrasi penggunaan objek
DataTable. Proses update sumber data (database) tidak
dicontohkan di sini karena Anda dapat melihat variasi update
database pada contoh Aplikasi Program Penjualan Barang.
Silahkan memperhatikan listing program dan bermain-main
(berimaginasi) dengan record yang akan ditampilkan pada layar
ketika Anda menekan/mengklik tombol navigasi atau menambah
sembarang baris baru. Jangan bermain-main dulu dengan proses
penghapusan karena ada hal menarik yang terjadi di sana.
Public Class objTabelData
Inherits System.Windows.Forms.Form
#Region " Windows Form Designer generated code "
Public Sub New()
MyBase.New()
'This call is required by the Windows Form Designer.
InitializeComponent()
'Add any initialization after the
InitializeComponent()
call
End Sub
'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing
As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Windows
‘Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents DataGrid1 As
System.Windows.Forms.DataGrid
Friend WithEvents Label1 As System.Windows.Forms.Label
Friend WithEvents TextBox1 As
System.Windows.Forms.TextBox
Friend WithEvents TextBox2 As
System.Windows.Forms.TextBox
Friend WithEvents Label2 As System.Windows.Forms.Label
Friend WithEvents TextBox3 As
System.Windows.Forms.TextBox
Friend WithEvents Label3 As System.Windows.Forms.Label
Friend WithEvents TextBox6 As
System.Windows.Forms.TextBox
Friend WithEvents Label6 As System.Windows.Forms.Label
Friend WithEvents TextBox5 As
System.Windows.Forms.TextBox
Friend WithEvents Label5 As System.Windows.Forms.Label
Friend WithEvents TextBox4 As
System.Windows.Forms.TextBox
Friend WithEvents Label4 As System.Windows.Forms.Label
Friend WithEvents TextBox7 As
System.Windows.Forms.TextBox
Friend WithEvents Label7 As System.Windows.Forms.Label
Friend WithEvents Btn_Buttom As
System.Windows.Forms.Button
Friend WithEvents Btn_Previous As
System.Windows.Forms.Button
Friend WithEvents Btn_Next As System.Windows.Forms.Button
Friend WithEvents Btn_Top As System.Windows.Forms.Button
Friend WithEvents Button1 As System.Windows.Forms.Button
Friend WithEvents Button2 As System.Windows.Forms.Button
Friend WithEvents TextBox8 As
System.Windows.Forms.TextBox
Friend WithEvents Label8 As System.Windows.Forms.Label
<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()
Me.DataGrid1 = New System.Windows.Forms.DataGrid
Me.Label1 = New System.Windows.Forms.Label
Me.TextBox1 = New System.Windows.Forms.TextBox
Me.TextBox2 = New System.Windows.Forms.TextBox
Me.Label2 = New System.Windows.Forms.Label
Me.TextBox3 = New System.Windows.Forms.TextBox
Me.Label3 = New System.Windows.Forms.Label
Me.TextBox6 = New System.Windows.Forms.TextBox
Me.Label6 = New System.Windows.Forms.Label
Me.TextBox5 = New System.Windows.Forms.TextBox
Me.Label5 = New System.Windows.Forms.Label
Me.TextBox4 = New System.Windows.Forms.TextBox
Me.Label4 = New System.Windows.Forms.Label
Me.TextBox7 = New System.Windows.Forms.TextBox
Me.Label7 = New System.Windows.Forms.Label
Me.Btn_Buttom = New System.Windows.Forms.Button
Me.Btn_Previous = New System.Windows.Forms.Button
Me.Btn_Next = New System.Windows.Forms.Button
Me.Btn_Top = New System.Windows.Forms.Button
Me.Button1 = New System.Windows.Forms.Button
Me.Button2 = New System.Windows.Forms.Button
Me.TextBox8 = New System.Windows.Forms.TextBox
Me.Label8 = New System.Windows.Forms.Label
CType(Me.DataGrid1,
System.ComponentModel.ISupportInitialize).BeginInit()
Me.SuspendLayout()
'
'DataGrid1
'
Me.DataGrid1.DataMember = ""
Me.DataGrid1.HeaderForeColor =
System.Drawing.SystemColors.ControlText
Me.DataGrid1.Location = New System.Drawing.Point(0, 0)
Me.DataGrid1.Name = "DataGrid1"
Me.DataGrid1.Size = New System.Drawing.Size(240, 128)
Me.DataGrid1.TabIndex = 0
'
'Label1
'
Me.Label1.Location = New System.Drawing.Point(248, 16)
Me.Label1.Name = "Label1"
Me.Label1.Size = New System.Drawing.Size(104, 16)
Me.Label1.TabIndex = 1
'
'TextBox1
'
Me.TextBox1.Location = New System.Drawing.Point
(376, 8)
Me.TextBox1.Name = "TextBox1"
Me.TextBox1.TabIndex = 2
Me.TextBox1.Text = ""
'TextBox2
'
Me.TextBox2.Location = New System.Drawing.Point
(376, 40)
Me.TextBox2.Name = "TextBox2"
Me.TextBox2.TabIndex = 4
Me.TextBox2.Text = ""
'
'Label2
'
Me.Label2.Location = New System.Drawing.Point(248, 48)
Me.Label2.Name = "Label2"
Me.Label2.Size = New System.Drawing.Size(104, 16)
Me.Label2.TabIndex = 3
'
'TextBox3
'
Me.TextBox3.Location = New System.Drawing.Point
(376, 72)
Me.TextBox3.Name = "TextBox3"
Me.TextBox3.TabIndex = 6
Me.TextBox3.Text = ""
'
'Label3
'
Me.Label3.Location = New System.Drawing.Point(248, 80)
Me.Label3.Name = "Label3"
Me.Label3.Size = New System.Drawing.Size(104, 16)
Me.Label3.TabIndex = 5
'
'TextBox6
'
Me.TextBox6.Location = New System.Drawing.Point
(200, 264)
Me.TextBox6.Name = "TextBox6"
Me.TextBox6.TabIndex = 13
Me.TextBox6.Text = ""
'
'Label6
'
Me.Label6.Location = New System.Drawing.Point(16, 264)
Me.Label6.Name = "Label6"
Me.Label6.Size = New System.Drawing.Size(168, 16)
Me.Label6.TabIndex = 12
'
'TextBox5
'
Me.TextBox5.Location = New System.Drawing.Point
(200, 232)
Me.TextBox5.Name = "TextBox5"
Me.TextBox5.TabIndex = 11
Me.TextBox5.Text = ""
'
'Label5
'
Me.Label5.Location = New System.Drawing.Point(16, 232)
Me.Label5.Name = "Label5"
Me.Label5.Size = New System.Drawing.Size(168, 16)
Me.Label5.TabIndex = 10
'
'TextBox4
Me.TextBox4.Location = New System.Drawing.Point
(200, 200)
Me.TextBox4.Name = "TextBox4"
Me.TextBox4.TabIndex = 9
Me.TextBox4.Text = ""
'
'Label4
'
Me.Label4.Location = New System.Drawing.Point(16, 200)
Me.Label4.Name = "Label4"
Me.Label4.Size = New System.Drawing.Size(168, 16)
Me.Label4.TabIndex = 8
'
'TextBox7
'
Me.TextBox7.Location = New System.Drawing.Point
(392, 232)
Me.TextBox7.Name = "TextBox7"
Me.TextBox7.TabIndex = 16
Me.TextBox7.Text = ""
'
'Label7
'
Me.Label7.Location = New System.Drawing.Point
(360, 208)
Me.Label7.Name = "Label7"
Me.Label7.Size = New System.Drawing.Size(168, 16)
Me.Label7.TabIndex = 15
'
'Btn_Buttom
'
Me.Btn_Buttom.Font = New
System.Drawing.Font("Microsoft
Sans Serif", 8.25!, System.Drawing.FontStyle.Bold,
System.Drawing.GraphicsUnit.Point, CType(0, Byte))
Me.Btn_Buttom.Location = New System.Drawing.Point
(176, 136)
Me.Btn_Buttom.Name = "Btn_Buttom"
Me.Btn_Buttom.Size = New System.Drawing.Size(48, 24)
Me.Btn_Buttom.TabIndex = 24
Me.Btn_Buttom.Text = ">|"
'
'Btn_Previous
'
Me.Btn_Previous.Font = New
System.Drawing.Font("Microsoft Sans Serif", 8.25!,
System.Drawing.FontStyle.Bold,
System.Drawing.GraphicsUnit.Point, CType(0, Byte))
Me.Btn_Previous.Location = New System.Drawing.Point
(64, 136)
Me.Btn_Previous.Name = "Btn_Previous"
Me.Btn_Previous.Size = New System.Drawing.Size(48, 24)
Me.Btn_Previous.TabIndex = 22
Me.Btn_Previous.Text = "<"
'
'Btn_Next
'
Me.Btn_Next.Font = New System.Drawing.Font("Microsoft
Sans Serif", 8.25!, System.Drawing.FontStyle.Bold,
System.Drawing.GraphicsUnit.Point, CType(0, Byte))
Me.Btn_Next.Location = New System.Drawing.Point
(120, 136)
Me.Btn_Next.Name = "Btn_Next"
Me.Btn_Next.Size = New System.Drawing.Size(48, 24)
Me.Btn_Next.TabIndex = 23
Me.Btn_Next.Text = ">"
'
'Btn_Top
'
Me.Btn_Top.Font = New System.Drawing.Font("Microsoft
Sans Serif", 8.25!, System.Drawing.FontStyle.Bold,
System.Drawing.GraphicsUnit.Point, CType(0, Byte))
Me.Btn_Top.Location = New System.Drawing.Point(8, 136)
Me.Btn_Top.Name = "Btn_Top"
Me.Btn_Top.Size = New System.Drawing.Size(48, 24)
Me.Btn_Top.TabIndex = 21
Me.Btn_Top.Text = "|<"
'
'Button1
'
Me.Button1.Location = New System.Drawing.Point
(16, 296)
Me.Button1.Name = "Button1"
Me.Button1.TabIndex = 25
Me.Button1.Text = "ADD"
'
'Button2
'
Me.Button2.Location = New System.Drawing.Point
(392, 264)
Me.Button2.Name = "Button2"
Me.Button2.TabIndex = 26
Me.Button2.Text = "DELETE"
'
'TextBox8
'
Me.TextBox8.Location = New System.Drawing.Point
(200, 328)
Me.TextBox8.Name = "TextBox8"
Me.TextBox8.TabIndex = 28
Me.TextBox8.Text = ""
'
'Label8
'
Me.Label8.Location = New System.Drawing.Point(16, 328)
Me.Label8.Name = "Label8"
Me.Label8.Size = New System.Drawing.Size(168, 16)
Me.Label8.TabIndex = 27
'
'objTabelData
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(536, 365)
Me.Controls.Add(Me.TextBox8)
Me.Controls.Add(Me.Label8)
Me.Controls.Add(Me.Button2)
Me.Controls.Add(Me.Button1)
Me.Controls.Add(Me.Btn_Buttom)
Me.Controls.Add(Me.Btn_Previous)
Me.Controls.Add(Me.Btn_Next)
Me.Controls.Add(Me.Btn_Top)
Me.Controls.Add(Me.TextBox7)
Me.Controls.Add(Me.Label7)
Me.Controls.Add(Me.TextBox6)
Me.Controls.Add(Me.Label6)
Me.Controls.Add(Me.TextBox5)
Me.Controls.Add(Me.Label5)
Me.Controls.Add(Me.TextBox4)
Me.Controls.Add(Me.Label4)
Me.Controls.Add(Me.TextBox3)
Me.Controls.Add(Me.Label3)
Me.Controls.Add(Me.TextBox2)
Me.Controls.Add(Me.Label2)
Me.Controls.Add(Me.TextBox1)
Me.Controls.Add(Me.Label1)
Me.Controls.Add(Me.DataGrid1)
Me.Name = "objTabelData"
Me.Text = "objTabelData"
CType(Me.DataGrid1,
System.ComponentModel.ISupportInitialize).EndInit()
Me.ResumeLayout(False)
End Sub
#End Region
Dim objConnection As SqlConnection
Dim objCommand As SqlCommand
Dim objDataAdapter As SqlDataAdapter
Dim objDataSet As New DataSet
Dim strConn, strSQL As String
Dim objDataTable As New DataTable
Private Sub objTabelData_Load(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
MyBase.Load
Dim form_MenuInduk As New MenuInduk
strConn = form_MenuInduk.mRoot_StrConn
strSQL = "select album_id, album_singer, album_title
from AlbumList order by album_id"
objConnection = New SqlConnection(strConn)
objCommand = New SqlCommand(strSQL, objConnection)
objConnection.Open()
objDataAdapter = New SqlDataAdapter(objCommand)
objDataAdapter.Fill(objDataSet, "mDT_albumlist")
objDataTable = objDataSet.Tables("mDT_AlbumList")
DataGrid1.DataSource = objDataTable
DataGrid1.ReadOnly = True
objConnection.Close()
Label1.Text = "posisi record"
Label2.Text = "Album ID"
Label3.Text = "Judul Album"
Label4.Text = "Album ID"
Label5.Text = "Penyanyi"
Label6.Text = "Judul Album"
Label7.Text = "posisi record untuk dihapus (>0)"
Label8.Text = "posisi record yang di tambah"
End Sub
Private Sub Display_data()
Dim mPosition As Integer =
BindingContext(objDataTable).Position
TextBox1.Text = mPosition
With objDataTable.Rows(mPosition)
TextBox2.Text = .Item("Album_ID")
TextBox3.Text = .Item("Album_title")
End With
End Sub
Private Sub Btn_Top_Click(ByVal sender As
System.Object,
ByVal e As System.EventArgs) Handles Btn_Top.Click
BindingContext(objDataTable).Position = 0
Display_data()
End Sub
Private Sub Btn_Previous_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
Btn_Previous.Click
BindingContext(objDataTable).Position -= 1
Display_data()
End Sub
Private Sub Btn_Next_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles Btn_Next.Click
BindingContext(objDataTable).Position += 1
Display_data()
End Sub
Private Sub Btn_Buttom_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
Btn_Buttom.Click
BindingContext(objDataTable).Position =
objDataTable.Rows.Count - 1
Display_data()
End Sub
Private Sub Button1_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles Button1.Click
objDataSet.AcceptChanges()
BindingContext(objDataTable).AddNew()
Dim mPos As Integer =
BindingContext(objDataTable).Position
BindingContext(objDataTable).Position = mPos
With objDataTable.Rows(mPos)
.Item("Album_ID") = TextBox4.Text
.Item("Album_Singer") = TextBox5.Text
.Item("Album_Title") = TextBox6.Text
End With
TextBox8.Text = mPos
End Sub
Private Sub Button2_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles Button2.Click
Dim mPosHapus As Integer = TextBox7.Text
Dim Row As DataRow = objDataTable.Rows(mPosHapus)
Row.Delete()
BindingContext(objDataTable).Position -= 1
End Sub
End Class
Apa gerangan hal menarik dari proses penghapusan record pada
tabel data? Sekilas memang tidak ada hal yang istimewa, tetapi
coba jalankan program dan hapus posisi record ke-3 (isi dengan
angka 2 pada kolom entri) dan tekan tombol Delete. Anda akan
melihat record tersebut dihapus dari tampilan DataGrid.
Coba lakukan navigasi ke Beginning of Record (|<) dan End of
Record (>|). Posisi record yang ditampilkan baik-baik saja. Akan
tetapi cobalah mulai melakukan navigasi baris satu per satu.
Booomm!! Program crash saat mendekati posisi record ke-3.
Coba jalankan kembali program tersebut dan hapus kembali posisi
record ke-3, dilanjutkan dengan menambahkan record baru.
Silahkan menggunakan tombol navigasi dan program akan
berjalan dengan mulus. Lakukan kembali penghapusan posisi
record ke-3. Anda akan melihat baris yang bersangkutan dihapus.
Selanjutnya bila Anda mengerakan tombol navigasi maka program
akan crash, tetapi bila dilakukan penambahan record baru,
program akan bekerja dengan normal pada pergerakan posisi
record. Singkatnya, dapat dikatakan tabel data baru akan di-
update secara fisik bila terjadi penambahan record baru.
Masalah yang muncul dalam penulisan aplikasi program adalah
bagaimana kita mengetahui seorang pengguna akan menambah
record baru setelah dia melakukan penghapusan? Bagaimana bila
dia mengerakan/navigasi posisi record? Tentunya kita tidak ingin
program crash.
Ingin tahu solusinya? Sabar saja, lupakan saja masalah ini untuk
sementara waktu, lebih baik belajar masalah lain karena
permasalahan tersebut akan diilustrasikan pada Aplikasi Program
Penjualan Barang dan Anda dapat melihat solusinya serta melihat
cara lain untuk menambah/menghapus baris tanpa melibatkan
sistem tombol navigasi.
Selain memperoleh (struktur) tabel data secara otomatis dari hasil
proses Selection Query, Anda dapat juga membuat (struktur) tabel
data melalui program dengan cara membuat objek data tabel baru,
membuat objek column dan menambahkan objek column ke
dalam objek data tabel, serta mengisi tabel data dengan baris
baru. Proses selanjutnya sama dengan proses struktur tabel yang
diperoleh dari proses Selection Query.
objTabelData2.vb:
Public Class objTabelData2
Inherits System.Windows.Forms.Form
#Region " Windows Form Designer generated code "
Public Sub New()
MyBase.New()
'This call is required by the Windows Form Designer.
InitializeComponent()
'Add any initialization after the
InitializeComponent()
call
End Sub
'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing
As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Windows
‘Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents DataGrid1 As
System.Windows.Forms.DataGrid
<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()
Me.DataGrid1 = New System.Windows.Forms.DataGrid
CType(Me.DataGrid1,
System.ComponentModel.ISupportInitialize).BeginInit()
Me.SuspendLayout()
'
'DataGrid1
'
Me.DataGrid1.DataMember = ""
Me.DataGrid1.HeaderForeColor =
System.Drawing.SystemColors.ControlText
Me.DataGrid1.Location = New System.Drawing.Point
(16, 16)
Me.DataGrid1.Name = "DataGrid1"
Me.DataGrid1.Size = New System.Drawing.Size(312, 152)
Me.DataGrid1.TabIndex = 0
'
'objTabelData2
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(344, 181)
Me.Controls.Add(Me.DataGrid1)
Me.Name = "objTabelData2"
Me.Text = "objTabelData2"
CType(Me.DataGrid1,
System.ComponentModel.ISupportInitialize).EndInit()
Me.ResumeLayout(False)
End Sub
#End Region
Private Sub objTabelData2_Load(ByVal sender As Object,
ByVal e As System.EventArgs) Handles MyBase.Load
'Buat objDataTable
Dim objDataTable As New DataTable
'Buat objek Column - objColumn dan set propertinya
Dim objColumn As DataColumn = New DataColumn
objColumn.DataType =
System.Type.GetType("System.String")
objColumn.AllowDBNull = False
objColumn.Caption = "EMP_ID"
objColumn.ColumnName = "Nomor Karyawan"
objColumn.DefaultValue = "KO000"
'Tambahkan objColumn ke tabel
objDataTable.Columns.Add(objColumn)
'Buat objek Column - objColumn1 dan set propertinya
objColumn = New DataColumn
objColumn.DataType =
System.Type.GetType("System.String")
objColumn.AllowDBNull = False
objColumn.Caption = "EMP_Name"
objColumn.ColumnName = "Nama Karyawan"
objDataTable.Columns.Add(objColumn)
' Tambahkan baris ke tabel
Dim objRow As DataRow
Dim i As Integer
For i = 0 To 5
objRow = objDataTable.NewRow()
objRow("Nomor Karyawan") = "K000" & Trim(Str(i))
objRow("Nama Karyawan") = "Nama Karyawan " & Str(i)
' Btambahkan baris baru ke dalam objDataTable
objDataTable.Rows.Add(objRow)
Next i
'set ukuran kolom dan row sebelum tabel dikaitkan ke
‘DataGrid
DataGrid1.PreferredColumnWidth() = 125
DataGrid1.PreferredRowHeight() = 20
DataGrid1.DataSource = objDataTable
End Sub
End Class