There are many approaches to paging. Paging is typically an architectural issue. Paging largely depends on your database design and how many records you have to page. You can use either of the following methods to implement paging:
Use the intrinsic paging that is available in the DataGrid control. This method of paging is easy to use and works well when you have a small number of records to page. However, performance decreases when the number of records increase. In Microsoft Visual Basic .NET, there is no intrinsic paging.For additional information about how to implement paging, click the article number below to view the article in the Microsoft Knowledge Base:
305271 HOW TO: Custom Page a DataGrid Windows Control by Using Visual Basic .NET
To speed up performance, design queries or stored procedures to retrieve the number of records that you want. The Custom Paging Sample section demonstrates how to implement custom paging.
You can also use the TOP and the WHERE clauses in your SQL statement to design queries or stored procedures to retrieve the number of records that you want. For more information about how to use TOP and WHERE to implement custom paging, visit the following Microsoft Developer Network (MSDN) Web site:
This sample demonstrates how to use a DataGrid control to implement custom paging through ASP.NET. When you use this method, you can retrieve the records that you want in any page order. As a result, this method is highly efficient and versatile.
This sample requires that the database include tables that have an index. Although this sample uses Microsoft SQL Server and the SQL Server Northwind database, you can adapt this sample to use with any database.
Follow these steps to create a new Visual Basic ASP.NET Web Application:
Start Microsoft Visual Studio .NET.
On the File menu, point to New, and then click Project.
In the New Project dialog box, click Visual Basic Projects under Project Types, and then click ASP.NET Web Application under Templates.
Drag a DataGrid control from the toolbox to WebForm1.aspx.
Right-click DataGrid1, and then click Property Builder.
Follow these steps in the Property Builder dialog box:
Click to select the AllowPaging and the AllowCustomPaging check boxes.
Set the PageSize property to 3.
In the Mode list, click Page Numbers.
Click Apply, and then click OK.
Click HTML at the bottom of WebForm1.aspx to open the HTML source view. Replace the <asp:DataGrid> tag that is added by default with the following code:
Double-click in an empty area of the WebForm1.aspx page to open the Code window, and then add the following namespaces to the top of the page:
Imports System.DataImports System.Data.SqlClient
Add the following declaration under Public Class Webform1:
Dim cn As SqlConnection Dim StartIndex As Integer Dim EndIndex As Integer
Add the following code in the Page_Load event of the form:
Dim myCommand As SqlCommand cn = New SqlConnection("server = localhost; uid=userid; pwd=password; database=Northwind ") If Not IsPostBack Then myCommand = New SqlCommand() myCommand.CommandText = "Select Count(*) from Products" myCommand.Connection = cn cn.Open() DataGrid1.VirtualItemCount = myCommand.ExecuteScalar() cn.Close() BindDatagrid() End If
Add the following code after the Page_Load subroutine:
Sub BindDatagrid() Dim myAdapter As SqlDataAdapter Dim DS As DataSet EndIndex = StartIndex + DataGrid1.PageSize myAdapter = New SqlDataAdapter("Select * From Products Where ProductID > @startIndex And ProductID <= @endIndex Order by ProductID", cn) myAdapter.SelectCommand.Parameters.Add("@startIndex", StartIndex) myAdapter.SelectCommand.Parameters.Add("@endIndex", EndIndex) DS = New DataSet() myAdapter.Fill(DS) DataGrid1.DataSource = DS DataGrid1.DataBind() End Sub Public Sub datagrid1_PageIndexChanged(ByVal source As System.Object, ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs) StartIndex = (e.NewPageIndex * DataGrid1.PageSize) DataGrid1.CurrentPageIndex = e.NewPageIndex BindDatagrid() End Sub
Press F5 to run the application.
To test the project, select any page number that is listed below the grid.