≡ Menu

Datagridview Paging Technique Using Data Adapter & Stored Procedure

You all ever make a form to display or record a very large load on the grid for example in a grid have to load about 4 -10 million records? Sure enough khan also troublesome, especially for application performance. First saia already done it with TrueDBGrid (time still use VB6) which should load the millions of records into a single grid, it is quite risky for the performance … fortunately still helped with data retrieval model is asyncfect ado … But still a big risk for application performance, not to mention if the application is multiuser … can be more severe.

It turns out the solution of that is using paging in the DataAdapter or the Stored Procedure (TableAdapter).

With this technique application performance is getting good, safe and stable.

Here paging technique using Table Adapter (Stored Procedure) in VB.Net:

Ok … first we make the stored procedurenya first. This procedure Storde who do paging. It also used a new feature in SQL Server 2005 is the Common Table Expression (CTE) that we can use to create a virtual table (create table on the fly).

Used the AdventureWorks database, table table Production.Product

  1. Make stored procedure
    Create Proc GetProductPaging
    @from int, @to int
    As
    With cteProduct
    As
    (
    Select row_number() Over(Order By ProductID ASC) As recnum,
    [Name],ProductNumber
    From Production.Product
    )
    Select * From cteProduct Where recnum Between @from And @to
    
  2. After that create a design form as below (label, numericupdown, Button, DataGridView)
  3. Add TableAdapter with a click on the Data menu> Add New Data Source, select Database as its DataSource object … After that, select Stored Procedure that have been made over … then the result will be creating a dataset with a TableAdapter
  4. Now we began to make his code. Add this code within the Form class is used:
    Imports System.Data.SqlClient
    
    Public Class Form1
    
        Private intStartRec, intRecPerPage, intTotalRec As Integer
        Private taProduct As AdventureWorksDataSetTableAdapters.GetProductPagingTableAdapter
        Private tblProduct As DataTable
    
        Private Sub GetData(ByVal startRecord As Integer, ByVal maxRecord As Integer)
            taProduct = New AdventureWorksDataSetTableAdapters.GetProductPagingTableAdapter
            tblProduct = taProduct.GetData(startRecord, maxRecord)
            DataGridView1.DataSource = tblProduct
        End Sub
    
        Private Sub SetRecordLabel()
            Label2.Text = "Start Record Number : " + (intStartRec + 1).ToString + _
            " From total record : " + intTotalRec.ToString
        End Sub
    
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            intRecPerPage = NumericUpDown1.Value '//number of records per page
            intStartRec = 0 '//initial record index
            GetData(intStartRec, intRecPerPage)
            intTotalRec = taProduct.GetTotalRecord
            SetRecordLabel()
        End Sub
    
        Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click
            intRecPerPage = NumericUpDown1.Value
            intStartRec = 0
            GetData(intStartRec, intRecPerPage)
            SetRecordLabel()
        End Sub
    
        Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.Click
            intRecPerPage = NumericUpDown1.Value
            intStartRec -= intRecPerPage
    
            'check if the start index record = 0 Then
                GetData(intStartRec + 1, intStartRec + intRecPerPage)
                SetRecordLabel()
            Else
                'reset back to start the record index
                intStartRec += intRecPerPage
            End If
        End Sub
    
        Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
            intRecPerPage = NumericUpDown1.Value
            intStartRec += intRecPerPage
    
            'check if the start index recordnya equal to the total number of records
            'or greater than the total record
            'it means that no longer exists recordnya (stop Next!!)
            If intStartRec < taProduct.GetTotalRecord() Then
                GetData(intStartRec + 1, intStartRec + intRecPerPage)
                SetRecordLabel()
            Else
                'reset back to start the record index
                intStartRec -= intRecPerPage
            End If
        End Sub
    
        Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
            intRecPerPage = NumericUpDown1.Value
            intTotalRec = taProduct.GetTotalRecord()
            intStartRec = intTotalRec - intRecPerPage
            GetData(intStartRec, intTotalRec)
            SetRecordLabel()
        End Sub
    
    End Class
    
  5. After that we will add a function to calculate the total number of records that exist in that table by using Partial Class. We will extend the TableAdapter class that has been generated at the top.
    Namespace AdventureWorksDataSetTableAdapters
        Partial Public Class GetProductPagingTableAdapter
            Private intTotalRec As Integer
    
            Public Function GetTotalRecord() As Integer
                Using sqlCmd As New SqlCommand
                    Using sqlConn As New SqlConnection("Database=AdventureWorks;Server=.SqlDev2k5;Integrated Security=SSPI")
                        sqlCmd.CommandType = CommandType.Text
                        sqlCmd.Connection = sqlConn
                        sqlCmd.CommandText = "Select Count (*) From Production.Product"
                        If sqlConn.State  ConnectionState.Open Then sqlConn.Open()
                        intTotalRec = Convert.ToInt32(sqlCmd.ExecuteScalar)
                        Return intTotalRec
                        sqlConn.Close()
                    End Using
                End Using
            End Function
        End Class
    End Namespace
    

Download source code :

http://newbiebanget.fileave.com/PagingDenganTA.rar

Here paging technique using Data Adapter in VB.Net:

  1. You create a Windows application project templates
  2. Add textbox control, numericupdown, buttons, the GridView, the label
  3. Add code like the following:
    Imports System.Data.SqlClient
    
    Public Class Form1
    
        Private intStartRec, intRecPerPage, intTotalRec As Integer
    
        Private Function GetTotalRecord() As Integer
            Using sqlCmd As New SqlCommand
                Using sqlConn As New SqlConnection("Database=Northwind;Server=localhost;Integrated Security=SSPI")
                    sqlCmd.CommandType = CommandType.Text
                    sqlCmd.Connection = sqlConn
                    sqlCmd.CommandText = "Select Count(*) From Customers"
                    If sqlConn.State  ConnectionState.Open Then sqlConn.Open()
                    intTotalRec = Convert.ToInt32(sqlCmd.ExecuteScalar)
                    Return intTotalRec
                    sqlConn.Close()
                End Using
            End Using
        End Function
    
        Private Sub GetData(ByVal startRecord As Integer, ByVal maxRecord As Integer)
            Using sqlConn As New SqlConnection("Database=Northwind;Server=localhost;Integrated Security=SSPI")
                Using sqlDa As New SqlDataAdapter("Select CustomerID,CompanyName From Customers", sqlConn)
                    Dim dsCustomers As New DataSet
                    sqlDa.Fill(dsCustomers, startRecord, maxRecord, "dtCustomers")
                    DataGridView1.DataSource = dsCustomers.Tables("dtCustomers")
                End Using
            End Using
        End Sub
    
        Private Sub SetRecordLabel()
            Label2.Text = "Start Index Record : " + intStartRec.ToString + _
            " From total record : " + intTotalRec.ToString
        End Sub
    
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            intRecPerPage = NumericUpDown1.Value '//jumlah record per page
            intStartRec = 0  '//index record awal
            GetData(intStartRec, intRecPerPage)
            intTotalRec = GetTotalRecord()
            SetRecordLabel()
        End Sub
    
        Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click
            intRecPerPage = NumericUpDown1.Value
            intStartRec = 0
            GetData(intStartRec, intRecPerPage)
            SetRecordLabel()
        End Sub
    
        Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.Click
            intRecPerPage = NumericUpDown1.Value
            intStartRec -= intRecPerPage
    
            'cek jika start index recordnya = 0 Then
                GetData(intStartRec, intRecPerPage)
                SetRecordLabel()
            Else
                'reset kembali start record indexnya
                intStartRec += intRecPerPage
            End If
        End Sub
    
        Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
            intRecPerPage = NumericUpDown1.Value
            intStartRec += intRecPerPage
    
            'cek jika start index recordnya sama dengan jumlah record total
            'atau lebih besar dari total record
            'maka berarti recordnya sudah tidak ada lagi (stop Next!!)
            If intStartRec < GetTotalRecord() Then
                GetData(intStartRec, intRecPerPage)
                SetRecordLabel()
            Else
                'reset kembali start record indexnya
                intStartRec -= intRecPerPage
            End If
        End Sub
    
        Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
            intRecPerPage = NumericUpDown1.Value
            intTotalRec = GetTotalRecord()
            intStartRec = intTotalRec - intRecPerPage
            GetData(intStartRec, intRecPerPage)
            SetRecordLabel()
        End Sub
    
    End Class
    

    Source code:

    http://newbiebanget.fileave.com/PagingDenganDA.rar

source: newbiebanget

{ 0 comments… add one }

Leave a Comment

*