How To Do Paging With ASP.NET Web API

Link for downloading Application Source Code.

In this article, we are going to learn how to perform paging with Web API. In this modern era where there is tons of data to share and consume, we cannot send all the data in one go or to one consumer; we need to slice the data into pieces to share known as paging.

In this era, every next device is consuming API in some way or another. If we consider the iPhone, it consumes API, the Windows Surface tablets consume API, the IoT devices also need API. In other words, API is required everywhere now. With these APIs, we not only send a single row of data, we send large chunks of data. And to display this data, we are going to use paging.

ASP.NET

For doing this demo, we are going to use some tools such as:

  1. Visual Studio 2015
  2. SQL Server 2008
  3. Entity Framework 6

Let’s start.

Database first

I have created a simple database named CustomerDB for the demo purpose. Inside I have added a table with name CustomerTB.

ASP.NET

Design of CustomerTB table

ASP.NET

After creating customer database and table, now let’s create a simple Web API application in Visual Studio 2015.

Creating Web API Project

Open Visual Studio IDE and on the Start page, select New Project…

ASP.NET
Fig 1. Start page

After selecting New Project link, a "New Project" dialog will appear. Inside that, select Templates >> Visual C#. Inside this, select Web and you will see “ ASP.NET Web Application ”. Now, just name your project as “DemoPaging” and finally, click on OK button to create a project.

Selecting Templates

ASP.NET
Fig 2. Selecting Template

After clicking on the OK button, another Project Template Selection wizard will pop up named “New ASP.NET Project”. In this template, select Web API Template. We are not going to create unit testing for this project, hence do not check this option and finally, click on OK button.

ASP.NET
Fig 3. Selecting MVC Project

After selecting all options as described above, click the OK button. Your project will be created.

Project structure after creating DemoPaging project 

ASP.NET
Fig 4. Project Structure

After creating the project, now we are going to add a folder named EFModel in the project. In this folder, we are going to add Entity Framework (Database First).edmx file.

Adding ADO.NET Entity Framework

ASP.NET
Fig 5. Adding ADO.NET Entity Framework

After clicking on ADO.NET Entity Data Model, it will ask for the name of the ADO.NET Entity Data Model. We are going to name it “EfCustomer”.

ASP.NET
Fig 6. Specify Item Name

As soon as you click on OK button, a new wizard of Entity Data Model will appear. In that, choose the “EF Designer from Database” option from the above options and click on the Next button.

ASP.NET
Fig 7. Choosing Model

Then, it will prompt new wizard for your Database Connection.

ASP.NET
Fig 8. Choosing Data Connection

Click on "New Connection"; a Connection Properties Wizard will pop-up. Here, fill in all your details.

In the following snapshot, you will see where to fill the details.

ASP.NET
Fig 9. Setting Database Connection

After entering the details of Username and Password, a list of database names will pop up. Here we would normaly select our database, but for this demo, I will select CustomerDB. Click on the "Test Connection" button to validate the database connection setting and finally, click the OK button if the Database connection setting is valid. Then, you can see all the changes in the Entity Connection String that have you chosen.

You can also configure the Web.config Connection String name by changing “Save connection settings in Web.Config” field.

ASP.NET
Fig 10.Choose option to Display Sensitive data in Connection String

The last option to choose is to show the sensitive data in the Connection string. Click "Yes".

Select the object to use

ASP.NET
Fig 11.Choosing objects

Select Tables. Inside that, select CustomerTB table and click on "Finish" button.

Now, it will generate EFCustomer Entity Data Model.

EFModel View after adding Entity Data Model

ASP.NET
Fig 12. EfCustomer Entity Data Model structure

Next, after adding EFCustomer Entity Data Model, we are going to add API Controller.

Adding API Controller

In this part, we are going to add an Empty API Controller with the name “CustomerInformation”.

ASP.NET
Fig 13.Snapshot after Adding CustomerInformation Controller

After adding API Controller, next, we are going add a Model.

Adding Model (PagingParameterModel)

We are going to add a Model named “PagingParameterModel” in the Models folder and it will have the paging property in it.

ASP.NET
Fig 14.Snapshot after adding PagingParameterModel

Code snippet of PagingParameterModel

  1. namespace DemoPaging.Models  
  2. {  
  3.     public class PagingParameterModel  
  4.     {  
  5.         const int maxPageSize = 20;  
  6.   
  7.         public int pageNumber { get; set; } = 1;  
  8.   
  9.         public int _pageSize { get; set; } = 10;  
  10.   
  11.         public int pageSize  
  12.         {  
  13.   
  14.             get { return _pageSize; }  
  15.             set  
  16.             {  
  17.                 _pageSize = (value > maxPageSize) ? maxPageSize : value;  
  18.             }  
  19.         }  
  20.     }  
  21. }  

NoteWe are going to use PagingParameterModel model as our input model.

Next, we add the Action Method to “CustomerInformation” API Controller.

Adding GetCustomer Action Method in CustomerInformation API Controller

Adding GetCustomer Action Method will handle HTTP Get requests and will take “PagingParameterModel” as an input parameter.

ASP.NET
Fig 15.Snapshot after adding Constructor and GetCustomer Action Method

Note AsQueryable just creates a query; the instructions are needed to get a list. You can make further changes to the query later, such as adding new Where clauses that get sent all the way down to the database level.

Code snippet of CustomerInformationController

In this part, first, we are going to get a request to API which will populate the model with data. After that, we are going to get the List of Customers and then, for further querying, we are going to make it as “AsQueryable”. After that, first, we are going to get count (“Total no of Customer”) to count variable.

Next, we are going to assign a value to the “CurrentPage” variable which will be from the PagingParameterModel Model. It has the property pageNumber and in a similar way, we are going to assign value to the PageSize property. After that, we will calculate TotalPages and further, we are going to apply a skip and take operators to the source which will return a Paged List of Customer. Lastly, we are going to create a “paginationMetadata” object which we are going to send in the response header of return list of Customer. 
  1. using DemoPaging.EFModel;  
  2. using DemoPaging.Models;  
  3. using Newtonsoft.Json;  
  4. using System;  
  5. using System.Collections.Generic;  
  6. using System.Linq;  
  7. using System.Web;  
  8. using System.Web.Http;  
  9.   
  10. namespace DemoPaging.Controllers  
  11. {  
  12. public class CustomerInformationController : ApiController  
  13. {  
  14. /// <summary>  
  15. /// Constructor for Creating instance of CustomerDBEntities   
  16. /// </summary>  
  17. CustomerDBEntities _context;  
  18. public CustomerInformationController()  
  19. {  
  20.     _context = new EFModel.CustomerDBEntities();  
  21. }  
  22.   
  23.   
  24. [HttpGet]  
  25. public IEnumerable<CustomerTB> GetCustomer([FromUri]PagingParameterModel pagingparametermodel)  
  26. {  
  27.      
  28.     // Return List of Customer  
  29.     var source = (from customer in _context.CustomerTBs.  
  30.                     OrderBy(a => a.Country)  
  31.                   select customer).AsQueryable();  
  32.   
  33.     // Get's No of Rows Count   
  34.     int count = source.Count();  
  35.   
  36.     // Parameter is passed from Query string if it is null then it default Value will be pageNumber:1  
  37.     int CurrentPage = pagingparametermodel.pageNumber;  
  38.   
  39.     // Parameter is passed from Query string if it is null then it default Value will be pageSize:20  
  40.     int PageSize = pagingparametermodel.pageSize;  
  41.   
  42.     // Display TotalCount to Records to User  
  43.     int TotalCount = count;  
  44.   
  45.     // Calculating Totalpage by Dividing (No of Records / Pagesize)  
  46.     int TotalPages = (int)Math.Ceiling(count / (double)PageSize);  
  47.   
  48.     // Returns List of Customer after applying Paging   
  49.     var items = source.Skip((CurrentPage - 1) * PageSize).Take(PageSize).ToList();  
  50.   
  51.     // if CurrentPage is greater than 1 means it has previousPage  
  52.     var previousPage = CurrentPage > 1 ? "Yes" : "No";  
  53.   
  54.     // if TotalPages is greater than CurrentPage means it has nextPage  
  55.     var nextPage = CurrentPage < TotalPages ? "Yes" : "No";  
  56.   
  57.     // Object which we are going to send in header   
  58.     var paginationMetadata = new  
  59.     {  
  60.         totalCount = TotalCount,  
  61.         pageSize = PageSize,  
  62.         currentPage = CurrentPage,  
  63.         totalPages = TotalPages,  
  64.         previousPage,  
  65.         nextPage  
  66.     };  
  67.   
  68.     // Setting Header  
  69.     HttpContext.Current.Response.Headers.Add("Paging-Headers", JsonConvert.SerializeObject (paginationMetadata));  
  70.     // Returing List of Customers Collections  
  71.     return items;   
  72.       
  73. }  
  74.   
  75. }  
  76. }  

Now, we have completed the coding part. Let’s try a real time example.

First, to call the Web API “API/GetCustomer” method, we are going to use Postman Web Debugger.

Installing the Postman Chrome App

For instructions on downloading the Postman Chrome app, go to https://www.getpostman.com/docs/introduction

After installing the Postman Chrome app, now you can open the Postman Chrome app. Below is a snapshot of the Postman Chrome App.

ASP.NET
Fig 16.Snapshot after Installing Postman Chrome App

Entering Request URL

Next, we are going to call GetCustomer API. For doing that, we are entering the URL in the following format.

Note - “#####” is the Port number

URL - http://localhost:#####/api/CustomerInformation

Query string - ?&pageNumber=1&pageSize=5

API URL - http://localhost:#####/api/CustomerInformation?&pageNumber=1&pageSize=5

We are passing [page number =1] and [PageSize=5] in query string.

ASP.NET
Fig 17.Snapshot while entering Request URL

After entering URL, next, we need to set headers. We have set “Content-Type” as “application/json” which is a Response Header.

ASP.NET
Fig 18.Snapshot while Setting Headers

After setting the header, now, just send the request by clicking on "SEND" button.

Real time debugging of GetCustomer Action Method

In “pagingparametermodel” Model, you can see that it is populated with a value of Query string which we have sent.

ASP.NET
Fig 19.Snapshot while debugging GetCustomer Action Method

Response of GetCustomer Action Method

In the response, we are going to get only five rows because we have sent page size as five.

ASP.NET
Fig 20.Snapshot after receiving Response

Response Header of GetCustomer Action Method

In response, we also get the header “Paging-Header” which gives a brief idea of how many rows exsist as well as your pagesize, currentpage, totalPages, along with “previousPage” and “nextPage,” these tell you if the request of data has “previousPage” and “next page” or not.

ASP.NET
Fig 21.Snapshot of response header

Now we have learned how to do paging with Web API in an easy step by step way.