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.



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.



Design of CustomerTB table



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…


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


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.


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 


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


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”.


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.

Fig 7. Choosing Model

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


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.


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.


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


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


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”.


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.


Fig 14.Snapshot after adding PagingParameterModel

Code snippet of PagingParameterModel

namespace DemoPaging.Models
{
    public class PagingParameterModel
    {
        const int maxPageSize = 20;

        public int pageNumber { get; set; } = 1;

        public int _pageSize { get; set; } = 10;

        public int pageSize
        {

            get { return _pageSize; }
            set
            {
                _pageSize = (value > maxPageSize) ? maxPageSize : value;
            }
        }
    }
}

Note - We 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.


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. 

using DemoPaging.EFModel;
using DemoPaging.Models;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Http;

namespace DemoPaging.Controllers
{
public class CustomerInformationController : ApiController
{
/// <summary>
/// Constructor for Creating instance of CustomerDBEntities
/// </summary>
CustomerDBEntities _context;
public CustomerInformationController()
{
    _context = new EFModel.CustomerDBEntities();
}


[HttpGet]
public IEnumerable<CustomerTB> GetCustomer([FromUri]PagingParameterModel pagingparametermodel)
{

    // Return List of Customer
    var source = (from customer in _context.CustomerTBs.
                    OrderBy(a => a.Country)
                  select customer).AsQueryable();

    // Get's No of Rows Count
    int count = source.Count();

    // Parameter is passed from Query string if it is null then it default Value will be pageNumber:1
    int CurrentPage = pagingparametermodel.pageNumber;

    // Parameter is passed from Query string if it is null then it default Value will be pageSize:20
    int PageSize = pagingparametermodel.pageSize;

    // Display TotalCount to Records to User
    int TotalCount = count;

    // Calculating Totalpage by Dividing (No of Records / Pagesize)
    int TotalPages = (int)Math.Ceiling(count / (double)PageSize);

    // Returns List of Customer after applying Paging
    var items = source.Skip((CurrentPage - 1) * PageSize).Take(PageSize).ToList();

    // if CurrentPage is greater than 1 means it has previousPage
    var previousPage = CurrentPage > 1 ? "Yes" : "No";

    // if TotalPages is greater than CurrentPage means it has nextPage
    var nextPage = CurrentPage < TotalPages ? "Yes" : "No";

    // Object which we are going to send in header
    var paginationMetadata = new
    {
        totalCount = TotalCount,
        pageSize = PageSize,
        currentPage = CurrentPage,
        totalPages = TotalPages,
        previousPage,
        nextPage
    };

    // Setting Header
    HttpContext.Current.Response.Headers.Add("Paging-Headers", JsonConvert.SerializeObject (paginationMetadata));
    // Returing List of Customers Collections
    return items;

}

}
}

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.


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.


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.


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.


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.


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.


Fig 21.Snapshot of response header

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