Archivo de la etiqueta: T-SQL

PAGING DATA, NOT CLIENT SIDE (2) (Blazor client)


In the previous post,Paging data, NOT client side. | Universidad Net, I described how to use OFFSET-FETCH pair to paginate data in the server side. In this one, I will describe an example of how to use it in a Web Assembly App.

Note: To test this code, you must create a new Blazor app in Visual Studio.

The Model.

The data model will be a class with two properties, one for the list of items and other with the page’s information.

This classes will be added in the Shared project of the Application.

The first class vary depending on the data you want to display, but the second one will be always the same, enhanced by some code.

Since the pager class will be a standardized and enhanced version of the data retrieved by using the stored procedure, and could be used in different projects, let’s define an interface for it.

IPagesInfo interface.

This is the code for the Interface.

public interface IPagesInfo
{
   #region Properties
 
   /// <summary>
   /// Gets or sets the current page.....
   /// </summary>
   System.Int32 CurrentPage { getset; }
 
   /// <summary>
   /// Gets the current first page number..
   /// </summary>
   Int32 FirstPageNumber { get; }
 
   /// <summary>
   /// Gets the HasNextGroup
   /// Gets a value indicating whether this instance has next page..
   /// </summary>
   Boolean HasNextGroup { get; }
 
   /// <summary>
   /// Gets the HasPreviousGroup
   /// Gets a value indicating whether this instance has previous page..
   /// </summary>
   Boolean HasPreviousGroup { get; }
 
   /// <summary>
   /// Gets the last page number..
   /// </summary>
   Int32 LastPageNumber { get; }
 
   /// <summary>
   /// Gets or sets the number of links to show..
   /// </summary>
   Int32 NumberOfLinks { getset; }
 
   /// <summary>
   /// Gets or sets the size of the page.....
   /// </summary>
   System.Int32 PageSize { getset; }
 
   /// <summary>
   /// Gets or sets the Qty of rows skipped from the top of the select.....
   /// </summary>
   System.Int32 Skip { getset; }
 
   /// <summary>
   /// Gets or sets the Qty of rows taken.....
   /// </summary>
   System.Int32 Take { getset; }
 
   /// <summary>
   /// Gets or sets the amount of items to display.
   /// </summary>
   System.Int32 TotalItems { getset; }
 
   /// <summary>
   /// Gets the total pages available to display..
   /// </summary>
   Int32 TotalPages { get; }
   /// <summary>
   /// Gets the page number for the previous group start.
   /// </summary>
   /// <value>
   /// The page number.
   /// </value>
   Int32 PreviousGroupStart { get; }
   /// <summary>
   /// Gets the  page number for the next group start.
   /// </summary>
   /// <value>
   /// The page number.
   /// </value>
   Int32 NextGroupStart { get; }
   #endregion
}

PagerInfo class.

Here, you have the code for the class implementing the IPagesInfo interface.

Notice the class is responsible of the calculations about page numbers displayed, if there are next or previous groups of pages, etc.

public class PagerInfo : IPagesInfo
{
   #region Fields
 
   /// <summary>
   /// Defines the lastPageNumber.
   /// </summary>
   internal Int32 lastPageNumber = 0;
 
   #endregion
 
   #region Properties
   public Int32 TotalPages
   {
      get => (Int32)Math.Ceiling(TotalItems / (Double)PageSize);
   }
   /// <summary>
   /// Gets the page number for the previous group start.
   /// </summary>
   /// <value>
   /// The page number.
   /// </value>
   public Int32 PreviousGroupStart => LastPageNumber - NumberOfLinks;
   /// <summary>
   /// Gets the  page number for the next group start.
   /// </summary>
   /// <value>
   /// The page number.
   /// </value>
   public Int32 NextGroupStart => LastPageNumber + 1;
 
   /// <summary>
   /// Gets or sets the current page.
   /// </summary>
   public System.Int32 CurrentPage { getset; }
 
   /// <summary>
   /// Gets the current first page number..
   /// </summary>
   public Int32 FirstPageNumber
   {
      get => LastPageNumber - (NumberOfLinks - 1);
   }
 
   /// <summary>
   /// Gets a value indicating whether this instance has next page.
   /// </summary>
   public Boolean HasNextGroup
   {
      get => CurrentPage + NumberOfLinks < TotalPages;
   }
 
   /// <summary>
   /// Gets a value indicating whether this instance has previous page.
   /// </summary>
   public Boolean HasPreviousGroup
   {
      get => CurrentPage > NumberOfLinks;
   }
 
   /// <summary>
   /// Gets the last page number..
   /// </summary>
   public Int32 LastPageNumber
   {
      get
      {
         lastPageNumber = (Int32)Math.Ceiling((Double)CurrentPage / NumberOfLinks) * NumberOfLinks;
         if (lastPageNumber > TotalPages)
         {
            lastPageNumber = TotalPages;
         }
         return lastPageNumber;
      }
   }
 
   /// <summary>
   /// Gets or sets the number of links to show..
   /// </summary>
   public Int32 NumberOfLinks { getset; } = 10;
 
   /// <summary>
   /// Gets or sets the size of the page.....
   /// </summary>
   public System.Int32 PageSize { getset; }
 
   /// <summary>
   /// Gets or sets the Qty of rows skipped from the top of the select.....
   /// </summary>
   public System.Int32 Skip { getset; }
 
   /// <summary>
   /// Gets or sets the Qty of rows taken.....
   /// </summary>
   public System.Int32 Take { getset; }
 
   /// <summary>
   /// Gets or sets the amount of items to display.
   /// </summary>
   public System.Int32 TotalItems { getset; }
 
   /// <summary>
   /// Gets the total pages available to display..
   /// </summary>
 
   #endregion
 
}

The Data Class.

This class will contain your data and the IPagesInfo properties. In this sample, it will be called PersonsPager.

Note: You can easily create it by executing the stored procedure, copying the result and in a new code window (for example, an empty one created for PersonsPager), paste it by the Edit – Paste Special-Past JSON as Classes.

Then replace the names autogenerated by your own and change the type for the second one for the PagesInfo class.

The pasted code names the main class as Rootobject, which has been renamed to PersonsPager.

The Pager class will be changed by PagesInfo, and the Pager class defined could be removed.

This is the final code for the PersonsPager class.

public class PersonsPager
{
 
   public List[] List { getset; }
   public PagerInfo Pager { getset; }
}
public class List
{
   public int BusinessEntityID { getset; }
   public string FirstName { getset; }
   public string MiddleName { getset; }
   public string LastName { getset; }
}

The Pager Component.

You must add a Razor Component. In this sample, it is called Pager.razor.

In the UI code, I use an UL tag, adding a button for previous group of pages (when the user moves beyond the first set of page numbers), buttons for the different page numbers, and a button for the next group as well.

For the buttons, the pagination, page-item, and page-link classes are used.

<nav class="text-center">
   <ul class="pagination">
      @{//Previous page group button.
 
         string className = "page-item " + (!PagesInfo.HasPreviousGroup ? " disabled" : ""); //If there is in previous group, the button will be disabled
         <li class="@className">
            <button class="page-link "
                    @onclick="@(()=>
                                   {
                                      if (PagesInfo.HasPreviousGroup)
                                        ChangePage(PagesInfo.PreviousGroupStart);
                                   }
               )">
               ⏪
            </button>
         </li>
      }
      @*Buttons for page numbers*@
      @for (Int32 i = PagesInfo.FirstPageNumber; i <= PagesInfo.LastPageNumber; i++)
      {
         int pageSelector = i;
 
         className = "page-item " + (i == PagesInfo.CurrentPage ? " active" : "");
         <li class="@className">
            <button class="page-link" @onclick="@(()=>ChangePage(pageSelector))">@string.Format("{0:00}"pageSelector)</button>
         </li>
      }
      @{//Next page group button.
         className = "page-item  " + (!PagesInfo.HasNextGroup ? " disabled" : "");
         <li class="@className">
            <button class="page-link " @onclick="@(()=>
                                                      { if (PagesInfo.HasNextGroup)
                                                            ChangePage(PagesInfo.NextGroupStart);
                                                      }
               )">
               ⏩
            </button>
 
         </li>
      }
   </ul>
</nav>

In the code section, parameters are defined for:

  • an instance of a class implementing the IPagerInfo
  • a value to persist the selected page
  • an EventCallback to notify the client page about the changes in the selection by the user.

Finally, the ChangePage function called every time the user clicks ant of the buttons is defined to change the selected page and notify the client page.

@code {
   [Parameter]
   public IPagesInfo PagesInfo { getset; }
   [Parameter]
   public int SelectedPage { get => PagesInfo.CurrentPage; set => PagesInfo.CurrentPage = value; }
   [Parameter]
   public EventCallback OnPageChange { getset; }
   void ChangePage(int newPage)
   {
      PagesInfo.CurrentPage = newPage;
      SelectedPage = newPage;
      OnPageChange.InvokeAsync(SelectedPage);
   }
}

The controller.

To get the information from the database, you will need an API REST controller which returns the JSON string from the stored procedure.

The method must be decorated with the HttpGet attribute to react when the client page code calls it.

[Route("/[controller]")]
[ApiController]
public class PersonDataController : ControllerBase
{
   public PersonDataController(IConfiguration configuration)
   {
      Configuration = configuration;
   }
 
   public IConfiguration Configuration { get; }
 
   [HttpGet]
   public async Task<stringGetPersons(int selectedPageint pageSize = 10)
   {
      using SqlConnection con = new SqlConnection(Configuration.GetConnectionString("aw"));
      try
      {
         SqlCommand com = new SqlCommand("[Person].[Person_GetforPager]"con);
         com.CommandType = System.Data.CommandType.StoredProcedure;
         com.Parameters.AddWithValue("@skip", (selectedPage == 0 ? 0 : selectedPage - 1* pageSize);
         com.Parameters.AddWithValue("@take"pageSize);
         con.Open();
         string values = (await com.ExecuteScalarAsync()).ToString();
         return values;
      }
      catch (System.Exception ex)
      {
 
         throw;
      }
   }
}

The client page.

A new component will be defined to display the data.

It will contain any type of display for your data (in the sample, it is just a UL list), and an instance of the Pager component.

As usual in a Web assembly app, you must check if you have data to display before performing the UI generation.

Moreover, you can decide if you need display the pager component in case of no more than one page is needed to display the information.

@inject HttpClient httpClient
<h3>Persons</h3>
@if (result != null)
{
   <div>@result.StatusCode</div>
   <div>@result.Content.ReadAsStringAsync().Result</div>
   <div>@httpClient.BaseAddress</div>
}
@if (personsPager != null && personsPager.List.Count() > 0)
{
   <ul>
      @foreach (var item in personsPager.List)
      {
         <li>@item.LastName</li>
 
      }
   </ul>
   @if (personsPager.Pager != null && personsPager.Pager.TotalPages > 1)
   {
      <Pager PagesInfo="PagesInfo" OnPageChange="ChangePage" />
      @**@
   }
}
else SelectedPage = 1;

The code of this page will call the controller get method to retrieve the information.

@code {
   PersonsPager personsPager;
   int SelectedPage;
   public IPagesInfo PagesInfo { get => personsPager.Pager; }
   HttpResponseMessage result;
   protected async override Task OnInitializedAsync()
   {
      await GetDataAsync();
   }
   async void ChangePage()
   {
      SelectedPage = personsPager.Pager.CurrentPage;
      //SelectedPage = PagesInfo.CurrentPage;
      await GetDataAsync();
      this.StateHasChanged();
 
   }
   async Task GetDataAsync()
   {
      //result = await httpClient.GetAsync($"PersonData?Selectedpage={SelectedPage}");
      personsPager = await httpClient.GetFromJsonAsync<PersonsPager>($"PersonData?Selectedpage={SelectedPage}");
 
   }
 
}

And this is the result:

Persons page with Pager sample
Persons page with Pager sample

Note: The “aw” connection string points to the AdventureWorks2017 database where the stored procedure from the previous post has been created.

BTW here is the sample

Paging data, NOT client side.


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.