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:

Visual Studio 2015
SQL Server 2008
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

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

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

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

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

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

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

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.

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.

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.

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.

15 Comments

Join the conversation! Your thoughts help the community grow.

Akash Omre7y ago
Nice its very Simple Clarification.
Susanta Rout7y ago
Please share jquery part
ChanikyaGuptha8y ago
How 'GetCustomer' is calling it self automatically?
Khizar Nasir8y ago
What if we have to use more than one table .. then what are we going to do?
- Saineshwar Bageri8y ago
  You can write LINQ query accordingly and check performance of query
Katz Grumoso8y ago
Sir thanks for sharing this, its very clear and helpful. just a little question, the query retrive all customer from the database, so when the data contains more than one million of customer, for each request, the query retrive one million of rows and then paginate,is a appropriate workflow? or doesnt matter?, thanks sir for the response :) note:right now I'm inserting one million of customer in the CustomerTB for testing
- Saineshwar Bageri8y ago
  It will work well just proper index your table
Tridip Bhattacharjee8y ago
Sir tell me when your following code will execute then it will fetch any data from db or not? see this is your code var source = (from customer in _context.CustomerTBs. OrderBy(a => a.Country) select customer).AsQueryable();
Tridip Bhattacharjee8y ago
I saw your paging code and feel 2 to 3 times database hit will occur...am i right?
- Saineshwar Bageri8y ago
  If we pull 50000 records in one pull then it will create issue in performance, but if we do paging it then we pull 10 to 20 records in one hit it is good , database will not take time to get this records.
- ChanikyaGuptha8y ago
  @Saineshwar Bageri, but you are getting the all the data in "var source". Doesn't it means puling 50000 records?
Anilananda Chakrabortty9y ago
How to add a textbox in the pagination so that i jump in a particular page... I want a input field and number type also...
- Saineshwar Bageri9y ago
  It is used for sharing data sir not with gridview anilananda sir
Anilananda Chakrabortty9y ago
Hello sir, @Saineshwar Bageri
Manav Pandya9y ago
Thanks for sharing sir ....