HOW TO: Page Through a Query Result for Better Performance

Article translations Article translations
Article ID: 318131 - View products that this article applies to.
This article was previously published under Q318131
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

On This Page

Summary

This step-by-step article provides guidelines about how to implement paging. This article provides a few examples of paging and describes the advantage of each method.

Methods to Implement Paging

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:
    Paging Through a Query Result
    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconpagingthroughqueryresult.asp

Custom Paging Sample

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.
  1. Follow these steps to create a new Visual Basic ASP.NET Web Application:
    1. Start Microsoft Visual Studio .NET.
    2. On the File menu, point to New, and then click Project.
    3. In the New Project dialog box, click Visual Basic Projects under Project Types, and then click ASP.NET Web Application under Templates.
  2. Drag a DataGrid control from the toolbox to WebForm1.aspx.
  3. Right-click DataGrid1, and then click Property Builder.
  4. Follow these steps in the Property Builder dialog box:
    1. Click Paging.
    2. Click to select the AllowPaging and the AllowCustomPaging check boxes.
    3. Set the PageSize property to 3.
    4. In the Mode list, click Page Numbers.
    5. Click Apply, and then click OK.
  5. 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:
    <asp:DataGrid id="Datagrid1" runat="server" AllowCustomPaging="True" PageSize="3" AllowPaging="True" PagerStyle-Mode="NumericPages" OnPageIndexChanged="DataGrid1_PageIndexChanged">
    </asp:DataGrid>
    					
  6. 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.Data
    Imports System.Data.SqlClient
    					
  7. Add the following declaration under Public Class Webform1:
        Dim cn As SqlConnection
        Dim StartIndex As Integer
        Dim EndIndex As Integer
    					
  8. 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
    					
  9. 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
    					
  10. Press F5 to run the application.
  11. To test the project, select any page number that is listed below the grid.

Properties

Article ID: 318131 - Last Review: October 26, 2013 - Revision: 3.0
Applies to
  • Microsoft ADO.NET 1.1
Keywords: 
kbnosurvey kbarchive kbhowtomaster kbsqlclient kbsystemdata KB318131

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com