SQL Queries For Paging In ASP.NET

Data paging is common task for ASP.NET developers. It could be very confusing if you place hundreds, thousands or even millions of records on single page. Also, your web page will load slowly and increase bandwidth. Common solution when you work with large amount of data is to provide data paging.

Simply, you show only 10, 20 or 50 records per page and provide buttons for navigation to the rest of data if user wants to see more. Some web controls, like GridView includes paging feature simple or customized. To use simple paging you only need to set AllowPaging property to true. Then, you can change some additional properties like PagerSettings.Position, PagerSettings.Mode and PageSize to suite your web application specific requirements. This paging will work fine if you don't have large tables and don't have many visitors.

Scalable Data Paging

But, if you have large table (e.g. more than a hundred pages) or you have a lot of concurrent web site visitors or you have shared web hosting with not so fast connection to Internet, shared memory etc., you should consider more efficient data paging. You need to return only one page instead to bind to complete record set and save web server resources on that way. There are different ways to achieve this, depending of your needs and type of database used.

Common questions could be:

  - Do you need sorting?

  - Do you need filtering (using of WHERE clause) or you can do it on server side by using a view?

  - Do you need to show total number of pages or you just want to list pages forward?

Paging with dynamic SQL

Dynamic SQL. data paging is divided in two queries:

1. Find total number of records

2. Return records from selected page

You can avoid first query to get faster execution. In that case you will not have an information about how much pages you have. Your Next button on pager will simply return next 20 or 30 records , depending of page size. After last page, the record set will be empty and you can show some message to the user or show last page again but now without Next button.

Finding total number of pages

To find total number of records you can use a simple one code line like this:

[ C# ]

string SQLQuery = "SELECT COUNT(*) FROM MyTable";

[ VB.NET ]

Dim SQLQuery As String = "SELECT COUNT(*) FROM MyTable"

After you execute this query and get the number of records, you can get number of pages with this code snippet:

[ C# ]

// Number of records we get from previous query
int NumberOfRecords = 234;
// Page size is number of records per page
int PageSize = 10;
// Number of pages
int NumberOfPages = NumberOfRecords / PageSize + (NumberOfRecords % PageSize > 0 ? 1 : 0);

[ VB.NET ]

' Number of records we get from previous query
Dim NumberOfRecords As Integer = 234
' Page size is number of records per page
Dim PageSize As Integer = 10
' Number of pages
Dim NumberOfPages As Integer = NumberOfRecords \ PageSize + IIf(NumberOfRecords Mod PageSize > 0, 1, 0)

How to return only records from specific page

The problem with paging is that it is hard to use one universal solution, because ANSI SQL has not standard way to limit the number of rows returned from table. There are some complex ways to do this by using JOIN or MAX keywords, often limited on some way. Fortunately, database systems like MS SQL Server, Oracle, MySQL or DB2 provide additional set of keywords that make data paging easier.

Dynamic SQL to return only selected page on SQL Server

You can get selected page with plain T-SQL by using TOP keyword and three nested SQL queries. For example, to return fifth page with 10 rows per page from Customers table ordered by column CompanyName ascending, we need SQL query like this:

SELECT * FROM
   (SELECT TOP 10 * FROM
     (SELECT TOP 50 * FROM Customers AS T1 ORDER BY CompanyName ASC)
   AS T2 ORDER BY CompanyName DESC)
T3 ORDER BY CompanyName ASC

This query works fine for all pages except the last page because last page is usually smaller and contains the rest of dividing records count and page size. I created a .NET function that automates this idea and returns rows that belongs to selected page and meet the WHERE clause conditions. This function works with all versions of SQL Server.

[ C# ]

/// <summary>
/// Build sql query used for data paging
/// </summary>
/// <param name="TableOrViewName">The name of table or view</param>
/// <param name="PageSize">The size of the page</param>
/// <param name="SelectedPage">The page currently selected</param>
/// <param name="PagesCount">Total of pages</param>
/// <param name="RecordsCount">Total of records</param>
/// <param name="Columns">Table columns to return</param>
/// <param name="OrderColumn">Column used in ORDER BY clause</param>
/// <param name="OrderDirection">Order direction</param>
/// <param name="WhereClause">Filter to select only wanted rows</param>
/// <returns>Returns table rows that belongs to selected page</returns>
/// <remarks></remarks>
private string getPageOnSqlServer(string TableOrViewName,
  int PageSize, int SelectedPage,
  int PagesCount, int RecordsCount,
  string Columns, string OrderColumn,
  string OrderDirection, string WhereClause)
{
  string ConOrderDirection = (OrderDirection.ToLower == "asc"? "DESC": "ASC");
 
  // If selected page is last page
  if(SelectedPage >= PagesCount)
    return "SELECT * FROM (SELECT TOP " + (RecordsCount % PageSize).ToString() +
      " * FROM (SELECT TOP " + (PageSize * SelectedPage).ToString() +
      " * FROM " + TableOrViewName + " AS T1 WHERE " + WhereClause +
      " ORDER BY " + OrderColumn + " " + OrderDirection +
      ") AS T2 ORDER BY " + OrderColumn + " " + ConOrderDirection +
      ") T3 ORDER BY " + OrderColumn +
      " " + OrderDirection;
   else
      return "SELECT * FROM (SELECT TOP " + PageSize.ToString() +
        " * FROM (SELECT TOP " + (PageSize * SelectedPage).ToString() +
        " * FROM " + TableOrViewName + " AS T1 WHERE " + WhereClause +
        " ORDER BY " + OrderColumn + " " + OrderDirection +
        ") AS T2 ORDER BY " + OrderColumn + " " + ConOrderDirection +
        ") T3 ORDER BY " + OrderColumn
        + " " + OrderDirection;
}
 
// First overload, without WHERE clause
private string getPageOnSqlServer(string TableOrViewName,
    int PageSize, int SelectedPage,
    int PagesCount, int RecordsCount,
    string Columns, string OrderColumn,
    string OrderDirection)
{
   return getPageOnSqlServer(TableOrViewName, PageSize, SelectedPage,
        PagesCount, RecordsCount, Columns, OrderColumn,
        OrderDirection, "1 = 1");
}
 
// Second overload, order direction is "ASC" and without where clause
private string getPageOnSqlServer(string TableOrViewName,
    int PageSize, int SelectedPage,
    int PagesCount, int RecordsCount,
    string Columns, string OrderColumn)
{
   return getPageOnSqlServer(TableOrViewName, PageSize, SelectedPage,
        PagesCount, RecordsCount, Columns, OrderColumn, "ASC");
}

[ VB.NET ]

''' <summary>
''' Build sql query used for data paging
''' </summary>
''' <param name="TableOrViewName">The name of table or view</param>
''' <param name="PageSize">The size of the page</param>
''' <param name="SelectedPage">The page currently selected</param>
''' <param name="PagesCount">Total of pages</param>
''' <param name="RecordsCount">Total of records</param>
''' <param name="Columns">Table columns to return</param>
''' <param name="OrderColumn">Column used in ORDER BY clause</param>
''' <param name="OrderDirection">Order direction</param>
''' <param name="WhereClause">Filter to select only wanted rows</param>
''' <returns>Returns table rows that belongs to selected page</returns>
''' <remarks></remarks>
Private Function getPageOnSqlServer(ByVal TableOrViewName As String, _
 ByVal PageSize As Integer, ByVal SelectedPage As Integer, _
 ByVal PagesCount As Integer, ByVal RecordsCount As Integer, _
 ByVal Columns As String, ByVal OrderColumn As String, _
 Optional ByVal OrderDirection As String = "ASC", _
 Optional ByVal WhereClause As String = "1 = 1") As String
 
 Dim ConOrderDirection As String = IIf(OrderDirection.ToLower = "asc", "DESC", "ASC")
 
 ' If selected page is last page
 If SelectedPage >= PagesCount Then
    Return "SELECT * FROM (SELECT TOP " & RecordsCount Mod PageSize & _
    " * FROM (SELECT TOP " & (PageSize * SelectedPage).ToString & _
    " * FROM " & TableOrViewName & " AS T1 WHERE " & WhereClause & _
    " ORDER BY " & OrderColumn & " " & OrderDirection & _
    ") AS T2 ORDER BY " & OrderColumn & " " & ConOrderDirection & _
    ") T3 ORDER BY " & OrderColumn _
    & " " & OrderDirection
 Else
    Return "SELECT * FROM (SELECT TOP " & PageSize.ToString() & _
    " * FROM (SELECT TOP " & (PageSize * SelectedPage).ToString & _
    " * FROM " & TableOrViewName & " AS T1 WHERE " & WhereClause & _
    " ORDER BY " & OrderColumn & " " & OrderDirection & _
    ") AS T2 ORDER BY " & OrderColumn & " " & ConOrderDirection & _
    ") T3 ORDER BY " & OrderColumn _
    & " " & OrderDirection
  End If
 End Function

So, for example if we use sample Northwind database and we need to return fifth page with 10 rows per page from Customers table ordered by column CompanyName, we'll need the code:

getPageOnSqlServer("Customers", 10, 5, 10, 91, "*", "CompanyName");

Note that you must select a column for Order By clause. In this case that is "CompanyName".

Paging with row_number() function on SQL Server 2005

Things become simpler with SQL Server 2005. There is a new row_number() function which is very useful for data paging.

For example, to get third page with 10 rows per page, from table Customers, ordered by some column "CompanyName", you can use this T-SQL:

SELECT * FROM (SELECT ROW_NUMBER() OVER
(ORDER BY CompanyName desc) AS 
rownum, * FROM Customers) AS tmpCustomers
WHERE rownum >= 21 AND rownum <= 30

So, things are much simpler with SQL Server 2005. Of course, you can add additional conditions to WHERE clause or even sort page to some other condition in outside query. According to this, it is easy now to create .NET function that automate this task:

[ C# ]

private string getPageSQL2005(string TableOrViewName,
string Columns, string OrderColumn, string OrderDirection,
int PageSize, int SelectedPage, string WhereClause)
{
  return "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY " +
    OrderColumn + " " + OrderDirection + ") AS rownum, " +
    Columns + " FROM " + TableOrViewName +
    ") AS tmp WHERE rownum >= " +
    ((SelectedPage - 1) * PageSize + 1).ToString() +
    " AND rownum <= " + (SelectedPage * PageSize).ToString() +
    (WhereClause.Trim() != "" ? " AND " + WhereClause : "");
}

[ VB.NET ]

Private Function getPageSQL2005(ByVal TableOrViewName As String, _
ByVal Columns As String, ByVal OrderColumn As String, _
ByVal OrderDirection As String, ByVal PageSize As Integer, _
ByVal SelectedPage As Integer, ByVal WhereClause As String) As String
  Return "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY " & _
    OrderColumn & " " & OrderDirection & ") AS rownum, " & _
    Columns & " FROM " & TableOrViewName & _
    ") AS tmp WHERE rownum >= " & _
    ((SelectedPage - 1) * PageSize + 1).ToString() & _
    " AND rownum <= " & (SelectedPage * PageSize).ToString() & _
    IIf(WhereClause.Trim() <> "", " AND " & WhereClause, "")
End Function

Security issues and recommendations in ASP.NET data paging

I must mention that this tutorial does not cover paging with stored procedures. That is very important part and will be the subject of the next tutorial. However, in both cases (dynamic sql or stored procedures) you need to devote attention to user inputs because it can be potentially dangerous for your web application. You can check every user input or use sql parameters. Using of parameters will make your queries secure and in some scenarios faster.

Building of complex WHERE clauses with various search conditions can be hardest part if you need to provide database search to your users. Because of that we developed specialized solution Search Control that makes building of WHERE clauses fun and easy. Search Control supports different search types, like all words, any word, exact phrase or boolean search expressions, works with different databases syntax, take care of security issues and much more.

Tutorial toolbar:  Tell A Friend  |  Add to favorites  |  Feedback  |   Google