HOW TO: Page Through a Query Result for Better Performance (318131)

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.

back to the top

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

back to the top

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.

back to the top