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.<a href="http://astroalloys.com.au/my-account/">Product</a> ) Select * From cteProduct Where recnum Between @from And @to

   1 2 3 4 5 6 7 8 9 10 11

   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.<a href="http://astroalloys.com.au/my-account/">Product</a> ) 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 <a href="http://www.info-vijesti.com/">index</a> 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 &lt; taProduct.GetTotalRecord() Then GetData(intStartRec + 1, intStartRec + intRecPerPage) SetRecordLabel() Else &#039;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

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72

   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 <a href="http://www.info-vijesti.com/">index</a>         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 &lt; taProduct.GetTotalRecord() Then             GetData(intStartRec + 1, intStartRec + intRecPerPage)             SetRecordLabel()         Else             &#039;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

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

   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 :

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 &lt; GetTotalRecord() Then GetData(intStartRec, intRecPerPage) SetRecordLabel() Else &#039;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

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88

   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

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

source: newbiebanget