Paging pada VB.Net

Fungsi cek jumlah data yang di select :


Public Shared Function CekRowSelect(ByVal Sql As String) As Integer
Dim NewSql As String
NewSql = “Select count (*) from (” & Sql & “) a”

Dim sDa As New SqlDataAdapter(NewSql, DbConString)
Dim Dt As New DataTable
sDa.Fill(Dt)

Return CType(Dt.Rows(0).Item(0), Integer)

Dt.Dispose()
sDa.Dispose()

End Function

Fungsi untuk ambil data

Public Shared Function GetTableDataPaging(ByVal Sql As String, _
ByVal JmlPerPage As Integer, ByVal JmlSelect As Integer, ByVal OrderBy As String) As DataTable

Dim sSql As String
Dim i As String
Dim a() As String
Dim j As Integer

Dim OrderBySql1 As String = “”
Dim OrderBySql2 As String = “”

i = OrderBy
a = i.Split(“#”)
For j = 0 To a.GetUpperBound(0)
If OrderBySql1 = “” Then
OrderBySql1 = OrderBySql1 & a(j) & ” ASC “
Else
OrderBySql1 = OrderBySql1 & “,” & a(j) & ” ASC “
End If
If OrderBySql2 = “” Then
OrderBySql2 = OrderBySql2 & a(j) & ” DESC “
Else
OrderBySql2 = OrderBySql2 & “,” & a(j) & ” DESC “
End If
Next

sSql = “SELECT * FROM (SELECT TOP ” & JmlPerPage & ” * FROM (SELECT TOP ” & JmlSelect & ” * FROM ( ” & Sql & “) c ORDER BY ” & OrderBySql1 & “) a ” _
& “ORDER BY ” & OrderBySql2 & “) b ORDER BY ” & OrderBySql1

Dim sDa As New SqlDataAdapter(sSql, DbConString)
Dim Dt As New DataTable
sDa.Fill(Dt)
Return Dt
Dt.Dispose()
sDa.Dispose()

End Function

Cara pake:

Deklarasi di paling atas biar bs diakses dr seluruh proses dl class


Dim StatusPage, JmlHalaman, TotalRecord, JmlRecordPerHal, S1, S2 As Integer
Dim SqlData, SortBy As String

Private Sub InisialisasiData(ByVal Sql As String)
Try
JmlRecordPerHal = 30
TotalRecord = CekRowSelect(Sql)

If TotalRecord Mod JmlRecordPerHal > 0 Then
JmlHalaman = (TotalRecord / JmlRecordPerHal) + 1
Else
JmlHalaman = (TotalRecord / JmlRecordPerHal)
End If

S1 = JmlRecordPerHal
S2 = StatusPage * CInt(S1)
Catch ex As Exception
Pesan(Err)
End Try
End Sub

Private Sub EksekusiData(ByVal sql As String, ByVal TSort As String)
Try
dt = GetTableDataPaging(sql, S1, S2, TSort)

DGV.Rows.Clear()
For Each Dr As DataRow In dt.Rows
DGV.Rows.Add(SetString(Dr(“ID_Barang”)), SetString(Dr(“Nama_Barang”)), SetString(Dr(“Satuan”)), SetString(Dr(“Kelompok”)), SetString(Dr(“Panjang”)), _
SetString(Dr(“Lebar”)), SetString(Dr(“Tinggi”)), SetString(Dr(“Barcode_Logistik”)), SetString(Dr(“Barcode_ISBN”)), SetString(Dr(“Maksimum_Tumpukan”)), SetString(Dr(“ROQ”)), SetString(Dr(“Prioritas”)))
Next
HeaderColor(DGV)
Blereng2(DGV)

Posisi.Text = “Halaman : ” & StatusPage & ” dari ” & JmlHalaman & “.” & vbNewLine _
& “Halaman ini berisi ” & dt.Rows.Count & ” record.” & vbNewLine & “Seluruh data ” & TotalRecord & ” Record.”
Catch ex As Exception
Pesan(Err)
End Try
End Sub

Load data:

Try
SqlData = “Select * from T_Barang”
SortBy = “Nama_Barang”
StatusPage = 1
InisialisasiData(SqlData)
EksekusiData(SqlData, SortBy)

Catch ex As Exception
Pesan(Err)
End Try

navigasi:

Private Sub BtAfter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtAfter.Click
Try
‘MsgBox(StatusPage)

If StatusPage = JmlHalaman – 1 Then
If TotalRecord Mod JmlRecordPerHal > 0 Then
S1 = TotalRecord Mod JmlRecordPerHal
Else
S1 = JmlRecordPerHal
End If
Else
S1 = JmlRecordPerHal
End If
StatusPage = StatusPage + 1
S2 = StatusPage * S1
EksekusiData(SqlData, SortBy)
Catch ex As Exception
Pesan(Err)
End Try
End Sub

Private Sub BtBefore_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtBefore.Click
Try
StatusPage = StatusPage – 1
S1 = JmlRecordPerHal
S2 = StatusPage * S1
EksekusiData(SqlData, SortBy)
Catch ex As Exception
Pesan(Err)
End Try
End Sub

Private Sub BtFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtFirst.Click
Try
StatusPage = 1
S1 = JmlRecordPerHal
S2 = StatusPage * S1
EksekusiData(SqlData, SortBy)
Catch ex As Exception
Pesan(Err)
End Try
End Sub

Private Sub BtLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtLast.Click
Try
StatusPage = JmlHalaman
If TotalRecord Mod JmlRecordPerHal > 0 Then
S1 = TotalRecord Mod JmlRecordPerHal
Else
S1 = JmlRecordPerHal
End If
S2 = TotalRecord
EksekusiData(SqlData, SortBy)
Catch ex As Exception
Pesan(Err)
End Try
End Sub