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
- 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
- After that create a design form as below (label, numericupdown, Button, DataGridView)
- 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
- 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
- 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:
- You create a Windows application project templates
- Add textbox control, numericupdown, buttons, the GridView, the label
- 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
