It is frequent to make web sites, mobile, UWP or Windows Applications, which need to present information of several rows which must be presented in pages of data.

That could be accomplished by 3 basic methods:

1. Send the content to the client and manage the pagination in it, for example, using some of the JScript frameworks available.
2. Using some sort of LinQ sentence, against EF, which will get the group of data after retrieving the entire set from the database.
3. Get the exact set of rows needed each time, by using T-SQL from SQL Server

The third method is the most responsive, since it processes the selection in the server side, and reduce the amount of traffic between server, application level and client UI.

This is how I get the information and paging support by using T-SQL.

The OFFSET – FETCH statements.

The basic tool for getting pages of data is the combination of OFFSET <n> FETCH <c>.

The n value is the number of rows excluded from the beginning of the result set, and the c value, the quantity to return.

Any query you create, could be modified by this pair of statements.

Note: This example, uses the AdventureWorks2017 sample database, which you can get here: https://docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure

For example, let us get the employees form the database:

           SELECT
                  [BusinessEntityID],
                  [Title],
                  [FirstName],
                  [MiddleName],
                  [LastName]
              FROM
                   [Person].[Person]
              WHERE [PersonType] = N'EM'
              ORDER BY
                       [LastName],
                       [FirstName]
              OFFSET 10 ROWS FETCH NEXT 20 ROWS ONLY 

It is just a standard select, but with the addition of the OFFSET and FETCH NEXT statements.

Of course, the numeric values could be replaced by parameters, like in:

           SELECT
                  [BusinessEntityID],
                  [Title],
                  [FirstName],
                  [MiddleName],
                  [LastName]
              FROM
                   [Person].[Person]
              WHERE [PersonType] = N'EM'
              ORDER BY
                       [LastName],
                       [FirstName]
              OFFSET @Skip ROWS FETCH NEXT @Take ROWS ONLY 

In case you want to get the first set of rows, the offset value could be 0, and it will work perfectly for your need.

Getting information about the entire set of data.

If you want to display to the user, precise information about the total rows in the set of data etc. you just need to perform another query after this one, by using the same filter in WHERE statement, to count the rows.

At the same time, you can get the current page based in the offset and fetch values, like in this query:

SELECT
   COUNT(*) AS [TotalItems],
   @Skip / @Take + 1 AS [CurrentPage],
   @Take AS [PageSize]
     FROM
      [Person].[Person]
     WHERE [PersonType] = N'EM'

Combining both sentences in the same query, you get two result sets to obtain, for example, using a data wrapper like Dapper:

SELECT
       [BusinessEntityID],
       [Title],
       [FirstName],
       [MiddleName],
       [LastName]
   FROM
        [Person].[Person]
   WHERE [PersonType] = N'EM'
   ORDER BY
            [LastName],
            [FirstName]
   OFFSET @Skip ROWS FETCH NEXT @Take ROWS ONLY;

SELECT
       COUNT(*) AS [TotalItems],
       @Skip / @Take + 1 AS [CurrentPage],
       @Take AS [PageSize]
   FROM
        [Person].[Person]
   WHERE [PersonType] = N'EM';

A JSON for an API.

This sentence could be modified to retrieve a JSON structure containing the entire set, to transfer it by an API to any client, following these rules:

• The entire set of information must be returned as a well-formed JSON.
• Each part of the information must be property identified.
• All the information must be returned as a single string value, which enables to get the information as a scalar value.
• The first part must be identified as a list/array of data. The second one, just as a unique entry.

For the list of rows, adding FOR JSON PATH, will transform the result in a single JSON content.

For the information about the paging process, it will be modified to avoid create an array of JSON elements, by issuing FOR JSON PATH,WITHOUT_ARRAY_WRAPPER modifier.

Finally, concatenating both sets as different attributes of a single JSON structure, will do the trick.

To have it easy to use, we will make a stored procedure with the entire query, with the corresponding parameters, as follows:

CREATE PROCEDURE [Person].[Person_GetforPager]
 ( @skip INT = 0,
   @take INT = 20
 )
AS
      BEGIN
        SELECT
               '{"List":' +
            (
               SELECT
                      [BusinessEntityID],
                      [Title],
                      [FirstName],
                      [MiddleName],
                      [LastName]
                  FROM
                       [Person].[Person]
                  WHERE [PersonType] = N'EM'
                  ORDER BY
                           [LastName],
                           [FirstName]
                  OFFSET @Skip ROWS FETCH NEXT @Take ROWS ONLY FOR JSON PATH
            )
            + ',"Pager":' +
            (
               SELECT
                      COUNT(*) AS [TotalItems],
                      @Skip / @Take + 1 AS [CurrentPage],
                      @Take AS [PageSize]
                  FROM
                       [Person].[Person]
                  WHERE [PersonType] = N'EM' FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
            )
            + '}';  
    END; 

Executing the procedure, this is the result:

{
   {
      "List": [
         {
            "BusinessEntityID": 38,
            "FirstName": "Kim",
            "MiddleName": "B",
            "LastName": "Abercrombie"
         },
         {
            "BusinessEntityID": 211,
            "FirstName": "Hazem",
            "MiddleName": "E",
            "LastName": "Abolrous"
         },
         {
            "BusinessEntityID": 121,
            "FirstName": "Pilar",
            "MiddleName": "G",
            "LastName": "Ackerman"
         },
      ],
      "Pager": {
         "TotalItems": 273,
         "CurrentPage": 1,
         "PageSize": 10
      }
   }

In the next post, we will see how to use it form a Web Assembly application.