How To Perform Searching With Paging In ASP.NET Web API

Link for downloading the application Source Code. ( The size of source is large that's why have uploaded it on GitHub).

In this article, we are going to learn how to perform a search with Web API. We have already covered how to do paging with Web API. If you want to have a look on it, just click on the link.



For 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 it, I have added a table with the name CustomerTB.



Design of CustomerTB table



After creating the 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 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 “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 here, select Web API Template and since we are not going to create Unit testing for this project, do not check this option. Finally, click OK.


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 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 Name of ADO.NET Entity Data Model we are going to Name it as “EfCustomer”.


Fig 6. Specify Item Name

As soon as you click on OK button a new wizard called Entity Data Model will appear.

In that, choose the “EF Designer from Database” option from above options and click on the Next button.


Fig 7. Choosing Model

Then it will prompt with a new wizard for your Database Connection.


Fig 8. Choosing Data Connection

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

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


Fig 9. Setting Database Connection

After entering the Username and Password details, a list of database names will pop up. Here we would normally 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 and 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 next, we are going to add API Controller.

Adding API Controller

In this part, we are going to add an Empty API controller with 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 Model in Models folder with the name “PagingParameterModel” which will have paging and search 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;
            }
        }

        public string QuerySearch { get; set; }
    }
}

Note

PagingParameterModel model is what we are going to use as our input model.

Next step, now we are going to Add Action Method to the “CustomerInformation” API Controller.

Adding GetCustomer Action Method in CustomerInformation API Controller

Adding GetCustomer Action Method which will handle the Http Get requests and also will take “PagingParameterModel” as input Parameter.


Fig 15.Snapshot after Adding Constructor and GetCustomer Action Method

Note: - AsQueryable just creates a query, the instructions 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 send the get request to the API which will populate the model with data after that we are going to get all List of Customer and then for further querying we are going to make it as “AsQueryable”.

Secondly, we are going apply a where clause to the List of Customers for searching the names of customers if “QuerySearch” parameter is sent from query string if it is not send then it will not apply where clause.

 After applying search we are going to get (“Total no of Customer”) count and we are going to assign it to a count variable, next we are going to assign value to “CurrentPage” variable which will be from PagingParameterModel Model.

 It has property pageNumber and in similar way, we are going to assign value to PageSize property.

After that we will calculate TotalPages and further, we are going to apply a skip and take operators to source which will return Paged List of Customer. Lastly, we are going to create a “paginationMetadata” object which we are going to sent in response header, 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();

    //Search Parameter [With null check]
    // ------------------------------------ Search Parameter-------------------

    if (!string.IsNullOrEmpty(pagingparametermodel.QuerySearch))
    {
     source = source.Where(a => a.Name.Contains(pagingparametermodel.QuerySearch));
    }

    // ------------------------------------ Search Parameter-------------------

    // 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,
        QuerySearch = string.IsNullOrEmpty(pagingparametermodel.QuerySearch) ?
                      "No Parameter Passed" : pagingparametermodel.QuerySearch
    };

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

For downloading Postman Chrome App

Installing the Postman Chrome App.

https://www.getpostman.com/docs/introduction

After installing the Postman Chrome App then you can open the Postman Chrome App, below is a snapshot of Postman Chrome App.


Fig 16.Snapshot after Installing Postman Chrome App

Entering Request URL

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

Note

“#####” is Port number

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

Query string - ?QuerySearch=Saineshwar&pageNumber=1&pageSize=15  

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

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


Fig 17.Snapshot while entering Request URL

After entering the URL next we need to set Headers, in this we are going to set “Content-Type” as “application/json” which is a Response header.


Fig 18.Snapshot while Setting Headers

After setting our header we now just send the request by clicking on the SEND button.

Real time debugging of GetCustomer Action Method

In the “pagingparametermodel” Model you can see 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 all rows with Customer name “Saineshwar” because we have passed QuerySearch as “Saineshwar” and on that list of Customers the paging will occur.


Fig 20.Snapshot after receiving Response 

Response Header of GetCustomer Action Method

In response, we also get header “Paging-Header” which give and a brief idea of how many rows are there with that what’s your pagesize , currentpage , totalPages along with “previousPage” and “nextPage” which tell you request of data has “previousPage” and “next page” or not and in last it also gives metadata of search parameter “QuerySearch” which we have passed in request. 


Fig 21.Snapshot of response header

Finally, we have learned how to perform search operation with Web API in an easy step by step way.