How To: Page Records in .NET Applications

Статья
07/14/2010

Retired Content
This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Improving .NET Application Performance and Scalability

J.D. Meier, Srinath Vasireddy, Ashish Babbar, and Alex Mackman
Microsoft Corporation

May 2004

Related Links

Home Page for Improving .NET Application Performance and Scalability

Chapter 12, Improving ADO.NET Performance

Checklist: ASP.NET Performance

Send feedback to Scale@microsoft.com

patterns & practices Library

Summary: This How To presents a number of different paging solutions that enable you to efficiently browse through large result sets. Each solution exhibits different performance and scalability characteristics. The solution you choose depends on the nature of your application and other factors, such as database server capacity, load, network bandwidth, and the amount of data to be paged through.

Applies To

Microsoft® .NET Framework version 1.1
Microsoft SQL Server™ 2000

Overview
What You Must Know
SELECT TOP
User-Specific Records
Application-Wide Records
Sample ASP.NET Solution Code
Additional Considerations
Additional Resources

Overview

Many Web and Windows Forms applications need to work with large result sets. For ease of use and efficiency, you may need to process and display the data in distinct chunks or pages and allow the user to page backward and forward through the results. A Web application's search results page is a good example of where this type of functionality is required.

There are a number of ways to tackle this problem. This How To describes the main options, along with the relative pros and cons of each approach.

When choosing a paging solution, you need to try to achieve a balance of the following:

Server-side processing. The number of rows that the database server must process, together with any additional processing that the server needs to perform, such as creating temporary tables or sorting data, affects database server resource utilization. The server resource utilization affects the scalability of the solution.
Bandwidth consumption. The number of rows returned to the client is an important consideration, particularly in scenarios where client bandwidth is limited.
Client-side processing. The way in which the client handles retrieved records is significant. This is particularly important when the client is an ASP.NET application, which needs to scale and support multiple concurrent users.

Make sure that you avoid inefficient paging solutions like the following ones:

Returning all rows for every request. With this approach, all the records are retrieved from the database and are returned across the network. The client then displays the required records. When the user moves to the next page, the server is accessed again and all of the rows are once again retrieved. This approach places unnecessary strain on the database server, consumes vast amounts of client memory, and passes large amounts of data over the network.
Caching all of the records in memory. With this approach, the client ASP.NET application retrieves all of the rows and caches them. While this approach might be appropriate for small amounts of application wide data, be aware of the potential scalability problems that this approach can create especially with larger amounts of cached data. For example, when a user leaves the site after five minutes, the memory resident records are left on the Web server, consuming valuable memory until the session times out.

What You Must Know

Regardless on the type of paging solution that you choose, you need to consider the following guidelines:

If you allow the user to specify the number of rows to be displayed, restrict the range of options by providing choices (for example, 5, 15, 25, and 50) in a drop - down list. Do not let the user specify an arbitrary page size that is out of your control.
Optimize your search query and use a WHERE clause to reduce the number of rows returned.
If you use stored procedures, optimize your query to minimize resource utilization on the server. Use such tools as SQL Query Analyzer, SQL Profiler, and the Index Tuning Wizard to analyze query and index performance.
Consider caching where possible. For example, consider caching the resultant DataSet (which usually contains static, nonvolatile data) on the data access client, for example in the ASP.NET cache, and bind controls to it.
If you use Oracle, use the OracleDataAdapter to load the requested page. The OracleDataReader supports the same functionality as the SqlDataReader with the same method names.

Several of the solutions presented in this How To use the SQL Server SELECT TOP construct. With Oracle databases, you must replace the TOP keyword with ROWNUM.

Also note that you create Oracle temporary tables by using the following syntax.
```
DECLARE GLOBAL TEMPORARY TABLE SESSION."#TEMP2" AS
(SELECT t.seq_nbr, t.id, k.amt, k.qty)
```

SELECT TOP

A simple and effective paging approach is to use the TOP keyword on your SELECT query to restrict the size of the result set.

This approach relies on tables having a unique key column (such as an IDENTITY column, or a unique product ID or customer ID). The following pseudocode shows this technique.

SELECT TOP <pageSize> ProductID, ProductName, ProductPrice
FROM Products
WHERE [standard search criteria]
AND ProductID > <lastProductID>
ORDER BY [Criteria that leaves ProductID monotonically increasing]
GO

The client needs to maintain the lastProductID value and increment or decrement it by the chosen page size between successive calls.

Solution Features

This solution exhibits the following features:

It only works for result sets sorted by a monotonically (consistently) increasing key column, which in practice limits the use of this approach.
It does not cache data but pulls only the required records across the network.
It supports simple navigation that enables the user to move to the next and previous pages. In the above example, the client application just needs to maintain the lastProductID value.
It does not support advanced navigation that enables the user to move to a specific page number.

For tables that do not have a unique key column, you can also use SELECT TOP in conjunction with a nested query. This approach is recommended for handling user - specific queries and is described in the next section.

User-Specific Records

To provide paging through user-specific data, you can use SELECT TOP with nested queries. The main advantages of this approach is that it does not require a unique key column of any sort and it also supports advanced navigation, where the user is able to move to the next, previous, first, and last pages, and is also able to move to a specific page.

The following pseudocode illustrates this technique.

SELECT TOP <pageSize> CustomerID,CompanyName,ContactName,ContactTitle
  FROM
  (SELECT TOP <currentPageNumber * pageSize> 
          CustomerID,CompanyName,ContactName,ContactTitle 
   FROM 
     Customers AS T1 ORDER BY ContactName DESC)
  AS T2 ORDER BY ContactName ASC

The inner SELECT statement selects a set of rows in descending order based (in this example) on the ContactName column. The number of rows selected is equal to the current page number times the page size. The outer SELECT statement then selects the top n rows, where n is the page size, and presents the data in ascending order based on the ContactName column.

The following code shows the above pseudocode implemented as a stored procedure that can be used to page through the Customers table in the Northwind database.

CREATE PROCEDURE UserPaging
(
  @currentPage int = 1, @pageSize int =10
)
AS
  DECLARE @Out int, @rowsToRetrieve int, @SQLSTRING nvarchar(1000)
 
  SET @rowsToRetrieve = (@pageSize * @currentPage)
 
  SET NOCOUNT ON
  SET @SQLSTRING = N'select CustomerID,CompanyName,ContactName,ContactTitle from ( SELECT TOP '+ CAST(@pageSize as varchar(10)) + 'CustomerId,CompanyName,ContactName,ContactTitle from (SELECT TOP ' + CAST(@rowsToRetrieve as varchar(10)) + 
      'CustomerID,CompanyName,ContactName,ContactTitle 
    FROM (SELECT TOP ' + CAST(@rowsToRetrieve as varchar(10)) + 
        'CustomerID,CompanyName,ContactName,ContactTitle FROM Customers as T1 
         ORDER BY contactname) AS T2 ORDER BY contactname DESC ) AS T3) As T4 ORDER BY contactname ASC'
 
  EXEC(@SQLSTRING)
  RETURN
  GO

Solution Features

This solution exhibits the following features:

It does not cache data but pulls only the required records across the network.
It does not require a unique key column.
It supports advanced navigation where the user is able to move to the next, previous, first, and last pages, and is also able to move to a specific page. The client passes in the required current page number.

Performance can be significantly faster when you store records in the sort order in which they will be retrieved, although it is not always practical to do so. A clustered index physically stores the records sorted by the fields in the index. The drawback to clustered indexes is that they can slow down write operations because each insert needs to be sorted in sequence.

Application-Wide Records

To page through application-wide data (records that apply to all users), you can use a global temporary table, which you can share across users. For example, this approach would work well for paging through a product catalog.

The solution uses a global temporary table for the following reasons:

It pulls together the query data.
By including an IDENTITY column in the temporary table, you can assign unique IDs to each row.
You can use the unique IDs to provide advanced record navigation, including the ability to move to the next, previous, first, and last pages, together with the ability to move to a specific page.

You should base the lifetime of the temporary table on the volatility of your data and how frequently you need to refresh the cached data.

The following stored procedure code shows this approach. The procedure starts by calculating the offset using the current page number and page size, which are supplied as parameters. As rows are selected and inserted, the IDENTITY column ensures that each row has an incrementing, numeric key value. This column is used to limit the range of rows returned to the client.

CREATE PROCEDURE dbo.app_temp
(
  @PageNum int = 1, 
  @PageSize int =10, 
  @ShipCity nvarchar(15), 
  @GreaterThanOrderDate datetime, 
  @TotalRows int OUTPUT
)
AS 
  DECLARE @iStart int, @iEnd int, @err int, @SQLSTRING  nvarchar(3000)
  SET NOCOUNT ON
  SELECT  @iStart = (((@PageNum - 1) * @PageSize) + 1)
  SELECT  @iEnd   = @PageNum*@PageSize

  --Check if the temporary table already exists. If so there is no
  --need to create it again.
  IF OBJECT_ID('tempdb..##TempTable1') IS NOT NULL
  BEGIN
    SELECT * from ##TempTable1 where I_ID between @iStart and @iEnd
    SELECT @TotalRows=COUNT(*) FROM ##TempTable1
  RETURN
  END
  -- creating table with as few columns as possible
  CREATE TABLE ##TempTable1 
     (I_ID int identity(1,1) primary key, CustomerID nchar(5), 
     OrderDate datetime,RequiredDate datetime, ShipName nvarchar(40))
  --inserting records
  SET @SQLSTRING = N'insert into ##TempTable1 '+
      ' SELECT '+'CustomerID,OrderDate,RequiredDate, 
      ShipName FROM Orders' + ' Where 
      ShipCity like '+''''+@ShipCity+''''+ ' AND OrderDate> '+''''+ 
      CAST(@GreaterThanOrderDate AS nvarchar(50))+''''+ ' Order by OrderDate'

  EXEC(@SQLSTRING)
  SELECT @TotalRows=COUNT(*) FROM ##TempTable1
  SELECT * from ##TempTable1 where I_ID between @iStart and @iEnd  
  RETURN
GO

Solution Features

This solution exhibits the following features:

It returns only the required records across the network.
It works for tables that do not contain a unique primary key column. An IDENTITY column is used on the temporary table to provide row uniqueness.
It supports advanced navigation where the user is able to move to the next, previous, first, and last pages, and is also able to move to a specific page.

Sample ASP.NET Solution Code

This section presents a sample ASP.NET application that uses the user-specific paging solution and the application wide-paging solution described earlier.

User-Specific Paging from ASP.NET

This solution uses nested SELECT TOP statements to implement the user specific paging solution outlined earlier. The test harness used in this solution consists of an ASP.NET page containing a table that in turn contains a DataGrid and navigation controls. The page navigation controls enable you to move to the first, last, next, and previous pages, and also to a specific page. The Web page also displays the current page number and total page count.

To create the ASP.NET page

Create a blank Web page named Userpaging.aspx and add the following code.

<%@ Page Language="C#"  Debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script >

string   _storedProc = "app_temp"; 
int      _currentPageNumber = 1;   // current selected page 
int      _totalRecords = 0;        // total records in table 
int      _pageSize = 10;           // number of rows per page 
int      _totalPages = 1;          // total pages
string   _connStr = "server=(local);database= northwind;Trusted_Connection=yes";

private void Page_Load(object sender, System.EventArgs e)
{
  if ( !Page.IsPostBack )
  {
    BindData();
  }
  CreateLinks();
}

// The BindData method constructs a SQL query that uses nested SELECT TOP 
// statements (as described earlier) to retrieve a specified page of data.
//
public void BindData()
{
  SqlConnection myConnection = new SqlConnection(_connStr);
  String strCmd = "";
  StringBuilder sb = new StringBuilder();

  sb.Append("select top {0} CustomerID,CompanyName,ContactName,ContactTitle from (select top {1} CustomerID,CompanyName,ContactName,ContactTitle from Customers ");
  sb.Append("as t1 order by contactname desc) ");
  sb.Append("as t2 order by contactname asc");
  strCmd = sb.ToString(); 
  sb = null;

  // Set pseudoparameters: TableName, KeyField and RowIndex 
  strCmd = String.Format(strCmd, _pageSize, 
                                 _currentPageNumber * _pageSize);

  // Prepare the command
  SqlCommand myCommand = new SqlCommand(strCmd,myConnection);

  SqlDataAdapter sa = new SqlDataAdapter(myCommand);
  DataSet searchData = new DataSet("SearchData");
  try
  {
    myConnection.Open();
    sa.Fill(searchData);

    MyDataGrid.DataSource = searchData;
    MyDataGrid.DataBind(); 
  }
  finally
  {
    myConnection.Close();
  }

  CurrentPage.Text = _currentPageNumber.ToString();
  if ( !Page.IsPostBack )
  {
    using (SqlConnection conn = new SqlConnection(_connStr))
    {
      SqlCommand cmd = conn.CreateCommand();
      cmd.CommandText = "SELECT Count(*) FROM Customers";
      conn.Open();
      _totalRecords = (int)cmd.ExecuteScalar();
      _totalPages = _totalRecords / MyDataGrid.PageSize;
      TotalPages.Text = _totalPages.ToString();
    }
  }
  else
  {
    _totalPages = int.Parse ( TotalPages.Text ); 
  }

  if (_currentPageNumber == 1)
  {
    PreviousPage.Enabled = false;
    if (_totalPages > 1)
      NextPage.Enabled = true;
    else
    NextPage.Enabled = false;
  }
  else
  {
    PreviousPage.Enabled = true;
    if (_currentPageNumber == _totalPages)
      NextPage.Enabled = false;
    else
      NextPage.Enabled = true;
  }
}

// The CreateLinks method creates a link button for each page in the target table.
// Users of the Web form can use these to directly move to a specific page number.
//
private void CreateLinks()
{
  Table tbl = new Table();   
  TableRow tr = new TableRow();
  int j=0;
  for(int i=1;i<=int.Parse(TotalPages.Text);i++,j++)
  {
    if(j==20)
    {
      tbl.Rows.Add(tr);
      j=0;
      tr = new TableRow();
    }
    LinkButton link = new LinkButton();
    link.Text = i.ToString();
    link.CommandName = i.ToString();
    link.Command += new CommandEventHandler(NavigationLink_Click);

    link.CssClass="pageLinks";
    link.EnableViewState=true;
    TableCell cell = new TableCell();
    cell.Controls.Add(link);
    tr.Cells.Add(cell);
  }
  tbl.Rows.Add(tr);
  SpecificPage.Controls.Add(tbl);
}

// This method handles the click event for the Next, Previous, First and Last
// link buttons.
//
protected void NavigationLink_Click ( Object sender, CommandEventArgs e )
{
  switch ( e.CommandName )
  { 
    case "First":
     _currentPageNumber =1;
      break;
    case "Next":
      _currentPageNumber = int.Parse( CurrentPage.Text ) + 1; 
      break;
    case "Prev":
      _currentPageNumber = int.Parse( CurrentPage.Text ) - 1;
      break;
    case "Last":
      _currentPageNumber =int.Parse(TotalPages.Text);
      break;
    default:
      _currentPageNumber=int.Parse(e.CommandName.ToString());
      break;
  } 
  BindData();
}
</script>

<html>
  <body>
    <form runat=server>
      <table>
      <tr><td><!-- DataGrid goes here --></td></tr>
        <asp:DataGrid  AllowPaging="True" AllowCustomPaging="True" 
        ID="MyDataGrid">
        <PagerStyle Visible="False"></PagerStyle>
        </asp:DataGrid>
      <tr><td><!-- navigation goes here --></td></tr>

      <asp:LinkButton id="FirstPage"  
        CommandName="First" 
        OnCommand="NavigationLink_Click" 
        Text="[First Page]">
      </asp:LinkButton>
      <asp:LinkButton id="PreviousPage"  
        CommandName="Prev" 
        OnCommand="NavigationLink_Click" 
        Text="[Previous Page]">
      </asp:LinkButton>
       <asp:LinkButton id="NextPage"  
        CommandName="Next" 
        OnCommand="NavigationLink_Click" 
        Text="[Next Page]">
      </asp:LinkButton>
       <asp:LinkButton id="LastPage"  
        CommandName="Last" 
        OnCommand="NavigationLink_Click" 
        Text="[Last Page]">
      </asp:LinkButton>

      <asp:PlaceHolder  ID="SpecificPage"></asp:PlaceHolder>

      Page <asp:Label id="CurrentPage" ></asp:Label> 
      of <asp:Label id="TotalPages" ></asp:Label>

      </table>
    </form>
  </body>
</html>

Save the Userpaging.aspx file.

To configure IIS and the ASPNET user

Create a virtual directory called DataPaging in Internet Information Services (IIS).
Copy Userpaging.aspx to the IIS virtual directory.
Ensure that a Windows login exists for the local ASPNET account in your SQL Server database.

To grant login and database access for the ASPNET account, use SQL Query Analyzer to execute the following commands against the Northwind database. Replace LocalMachine with your local computer name.
```
exec sp_grantlogin [LocalMachine\ASPNET]
exec sp_grantdbaccess [LocalMachine\ASPNET]
```

To test the Web page and paging functionality

Use Internet Explorer and browse to https://localhost/DataPaging/Userpaging.aspx.
Test the paging functionality and the various types of navigation.

Application-Specific Paging Solution

This solution uses a global temporary table to implement the application-wide paging solution outlined earlier from an ASP.NET application.

To create the required stored procedure

In SQL Query Analyzer, execute the following SQL script against the Northwind database to create the stored procedure.

app_temp.sql

CREATE PROCEDURE dbo.app_temp
(
  @PageNum int = 1, 
  @PageSize int =10, 
  @ShipCity nvarchar(15), 
  @GreaterThanOrderDate datetime, 
  @TotalRows int OUTPUT
)
AS 
  DECLARE @iStart int, @iEnd int, @err int, @SQLSTRING  nvarchar(3000)
  SET NOCOUNT ON
  SELECT  @iStart = (((@PageNum - 1) * @PageSize) + 1)
  SELECT  @iEnd   = @PageNum*@PageSize

  --Check if the temporary table already exists. If so there is no
  --need to create it again.
  IF OBJECT_ID('tempdb..##TempTable1') IS NOT NULL
  BEGIN
    SELECT * from ##TempTable1 where I_ID between @iStart and @iEnd
    SELECT @TotalRows=COUNT(*) FROM ##TempTable1
  RETURN
  END
  -- creating table with as few columns as possible
  CREATE TABLE ##TempTable1 
     (I_ID int identity(1,1) primary key, CustomerID nchar(5), 
     OrderDate datetime,RequiredDate datetime, ShipName nvarchar(40))
  --inserting records
  SET @SQLSTRING = N'insert into ##TempTable1 '+
      ' SELECT '+'CustomerID,OrderDate,RequiredDate, 
      ShipName FROM Orders' + ' Where 
      ShipCity like '+''''+@ShipCity+''''+ ' AND OrderDate> '+''''+ 
      CAST(@GreaterThanOrderDate AS nvarchar(50))+''''+ ' Order by OrderDate'

  EXEC(@SQLSTRING)
  SELECT @TotalRows=COUNT(*) FROM ##TempTable1
  SELECT * from ##TempTable1 where I_ID between @iStart and @iEnd  
  RETURN
GO

Grant execute permissions to the ASPNET account. In SQL Query Analyzer, execute the following commands against the Northwind database. Replace LocalMachine with your local computer name.
```
grant execute on app_temp to [LocalMachine\ASPNET]
```

To create Appwidepaging.aspx

Create a blank Web page named Appwidepaging.aspx in the virtual directory called DataPaging and add the following code.

<%@ Page Language="C#" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script >

    string   _storedProc = "app_temp";
         int      _currentPageNumber = 1;   // current selected page
         int      _totalRecords = 0;        // total records in table
         int      _pageSize = 10;           // number of rows per page
         int      _totalPages = 1;          // total pages

    private void Page_Load(object sender, System.EventArgs e)
    {
       if ( !Page.IsPostBack )
       {
         BindData();
       }
       CreateLinks();
    }


    public void BindData()
    {
       SqlConnection myConnection = new SqlConnection("server=(local);database= northwind;Trusted_Connection=yes");
       SqlCommand myCommand = new SqlCommand(_storedProc, myConnection);
       myCommand.CommandType = CommandType.StoredProcedure;

       myCommand.Parameters.Add("@PageNum", SqlDbType.Int).Value = _currentPageNumber ;
       myCommand.Parameters.Add("@PageSize",SqlDbType.Int).Value = _pageSize;
       myCommand.Parameters.Add("@ShipCity",SqlDbType.NVarChar,15).Value = "%";
    ;
       myCommand.Parameters.Add("@GreaterThanOrderDate",SqlDbType.DateTime).Value = DateTime.Parse("7/4/1996");
       myCommand.Parameters.Add("@TotalRows", SqlDbType.Int).Direction = ParameterDirection.Output;

       SqlDataAdapter sa = new SqlDataAdapter(myCommand);
       DataSet searchData = new DataSet("SearchData");
       try
       {
         myConnection.Open();
         sa.Fill(searchData);

         MyDataGrid.DataSource = searchData;
         MyDataGrid.DataBind();
       }
       finally
       {
         myConnection.Close();
       }

       CurrentPage.Text = _currentPageNumber.ToString();

       if ( !Page.IsPostBack )
       {
         _totalRecords = (int)(myCommand.Parameters["@TotalRows"].Value);
         _totalPages = _totalRecords / MyDataGrid.PageSize;
         TotalPages.Text = _totalPages.ToString();
       }
       else
       {
         _totalPages = int.Parse ( TotalPages.Text );
       }

       if (_currentPageNumber == 1)
       {
         PreviousPage.Enabled = false;
         if (_totalPages > 1)
           NextPage.Enabled = true;
         else
           NextPage.Enabled = false;
       }
       else
       {
         PreviousPage.Enabled = true;
         if (_currentPageNumber == _totalPages)
           NextPage.Enabled = false;
         else
           NextPage.Enabled = true;
       }
    }


    private void CreateLinks()
    {
       Table tbl = new Table();
       TableRow tr = new TableRow();
       int j=0;
       for(int i=1;i<=int.Parse(TotalPages.Text);i++,j++)
       {
         if(j==20)
         {
           tbl.Rows.Add(tr);
           j=0;
           tr = new TableRow();
         }
         LinkButton link = new LinkButton();
         link.Text = i.ToString();
         link.CommandName = i.ToString();
         link.Command += new CommandEventHandler(NavigationLink_Click);

         link.CssClass="pageLinks";
         link.EnableViewState=true;
         TableCell cell = new TableCell();
         cell.Controls.Add(link);
         tr.Cells.Add(cell);
       }
       tbl.Rows.Add(tr);
       SpecificPage.Controls.Add(tbl);
    }

    protected void NavigationLink_Click ( Object sender, CommandEventArgs e )
    {
       switch ( e.CommandName )
       {
         case "First":
           _currentPageNumber =1;
           break;
         case "Next":
           _currentPageNumber = int.Parse( CurrentPage.Text ) + 1;
           break;
         case "Prev":
           _currentPageNumber = int.Parse( CurrentPage.Text ) - 1;
           break;
         case "Last":
           _currentPageNumber =int.Parse(TotalPages.Text);
           break;
         default:
           _currentPageNumber=int.Parse(e.CommandName.ToString());
           break;
       }
       BindData();
    }

</script>
<html>
<head>
</head>
<body>
    <form >
        <table>
            <tbody>
                <tr>
                    <td>
                        <asp:DataGrid id="MyDataGrid"  AllowPaging="True" AllowCustomPaging="True">
                            <PagerStyle visible="False"></PagerStyle>
                        </asp:DataGrid>
                    </td>
                </tr>
                <tr>
                    <td>
                        <asp:LinkButton id="FirstPage"  CommandName="First" OnCommand="NavigationLink_Click" Text="[First Page]"></asp:LinkButton>
                        <asp:LinkButton id="PreviousPage"  CommandName="Prev" OnCommand="NavigationLink_Click" Text="[Previous Page]"></asp:LinkButton>
                        <asp:LinkButton id="NextPage"  CommandName="Next" OnCommand="NavigationLink_Click" Text="[Next Page]"></asp:LinkButton>
                        <asp:LinkButton id="LastPage"  CommandName="Last" OnCommand="NavigationLink_Click" Text="[Last Page]"></asp:LinkButton>
                        <asp:PlaceHolder id="SpecificPage" ></asp:PlaceHolder>
                        Page <asp:Label id="CurrentPage" ></asp:Label>of <asp:Label id="TotalPages" ></asp:Label></td>
                </tr>
            </tbody>
        </table>
    </form>
</body>
</html>

Save Appwidepaging.aspx.

To test the Web page and paging functionality

Use Internet Explorer and browse to https://localhost/DataPaging/Appwidepaging.aspx.
Test the paging functionality and the various types of navigation.

Additional Considerations

In addition to the approaches described earlier in this How To, there are a number of other paging approaches, although the alternatives tend to offer limited performance and scalability characteristics. The additional approaches explained in this section are:

DataAdapter's overloaded Fill method.
DataGrid's default paging feature.

DataAdapter's Overloaded Fill Method

You can use the following overloaded Fill method of the DataAdapter to page through rows.

public int Fill(
      DataSet dataSet,
      int startRecord,
      int maxRecords,
      string srcTable
);

The startRecord parameter indicates the zero-based index of the first record to retrieve. The maxRecords parameter indicates the number of records, starting from startRecord, to copy into the new DataSet.

The DataAdapter copies all of the results into a newly generated DataSet and discards any unnecessary results. This means that a lot of unnecessary data could be pulled across the network to the data access client, which is the primary drawback to this approach. For example, if you have 1,000 records and want to retrieve records 900 through 950, the first 899 records are still pulled across the network and discarded on the client side. This overhead is likely to be minimal for small result sets, but could be significant when you page through larger sets of data. Therefore, this approach is not a good choice for paging through large query result sets.

DataGrid's Default Paging Feature

To display a single page, the DataGrid object's default paging behavior retrieves all of the records each time the user navigates to a new page. This approach is not a good choice for paging through large query result sets.

Additional Resources

For more information, see the following resources:

Chapter 4, "Architecture and Design Review of a .NET Application for Performance and Scalability"
Chapter 12, "Improving ADO.NET Performance"
Chapter 13, "Code Review: .NET Application Performance"
"Checklist: ADO.NET Performance" in the "Checklists" section of this guide
Microsoft Knowledge Base article 318131, "HOW TO: Page Through a Query Result for Better Performance," at https://support.microsoft.com/default.aspx?scid=kb;en-us;318131.

Retired Content
This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Поделиться через

How To: Page Records in .NET Applications

Improving .NET Application Performance and Scalability

Applies To

Contents

Overview

What You Must Know

SELECT TOP

Solution Features

User-Specific Records

Solution Features

Application-Wide Records

Solution Features

Sample ASP.NET Solution Code

User-Specific Paging from ASP.NET

Application-Specific Paging Solution

Additional Considerations

DataAdapter's Overloaded Fill Method

DataGrid's Default Paging Feature

Additional Resources

Дополнительные ресурсы