DotNetCurry Logo

ASP.NET Web API Pagination using OData operators

Posted by: Mahesh Sabnis , on 5/12/2014, in Category ASP.NET
Views: 48413
Abstract: This article demonstrates how to implement Pagination & Data Filters in ASP.NET Web API using OData and Knockout.js

ASP.NET Web API is a framework that makes it easy to build HTTP services for various types of clients from Microsoft to Non-Microsoft technologies. The new programming model of developing over HTTP is made simple and flexible by using WebAPI. We can design services which can be accessible from a broad range of clients, including browsers and mobile devices.

One of the most frequent requirements while retrieving data using WEB API is that how to implement pagination so that only a subset of data is fetched from the server. To implement this, OData queries with WEB API can be used. You can get more information about the OData Queries from here.

Let us build a simple application that can filter multipage data using OData queries directly.

 

 

The Web API Demo

Step 1: Open VS 2013 and create a new Empty MVC application. Name it as ‘MVC50_WEBAPI_Pagination’. Since we will be using jQuery and Knockout.js framework, in this project right click and using ‘Manage NuGet Package’, get the latest jQuery and Knockout script files.

Step 2: Add a new SQL Server Database in the project as below:

sqldb

Name it as Application.MDF. This database will be added in the App_Data folder. Double click on the MDF file, the database will be shown in the ‘Server Explorer’. Add the new table in the database of name ‘EmployeeInfo’ as below:

dbtable

The SQL Script is as below:

CREATE TABLE [dbo].[EmployeeInfo] (
    [EmpNo]       INT          IDENTITY (1, 1) NOT NULL,
    [EmpName]     VARCHAR (50) NOT NULL,
    [DeptName]    VARCHAR (50) NOT NULL,
    [Salary]      INT          NOT NULL,
    [Designation] VARCHAR (50) NOT NULL,
    PRIMARY KEY CLUSTERED ([EmpNo] ASC)
);

Add about 20 rows of sample data.

Step 3: In the Models folder, add a new ADO.NET EF model, Complete wizard, provide the Database name as the Application.MDF added in the application in Step 2. After completing the Wizard the mapping will be shown as below:

table_mapping

Step 4: In the controller folder, add a new API controller based upon the ADO.NET EF added in previous step. Name it as ‘EmployeeInfoAPIController’:

webapi-controller

api_controller

You will get the methods for GET|POST|PUT and DELETE.


Including OData Libraries

Step 5: To do this, right click on the Project and select the Manage NuGet package and select and install ‘Microsoft ASP.NET Web API OData’ package as below:

nuget_package

This will add the reference of ‘System.Web.Http.OData’ assembly in the project.

Step 6: Since we are going to implement Pagination, we need to modify the GetEmployeeInfos method to return AsQueryable(). Open EmployeeInfoAPIController.cs and change the GetEmployeeInfoes() method as below:

//Modify the API Controller
[Queryable]
public IQueryable<EmployeeInfo> GetEmployeeInfos()
{
return db.EmployeeInfoes.AsQueryable();
}

The Queryable attribute specifies that the action method now supports the OData query syntax.

 

Step 7: In the Controller folder, add a new Empty MVC controller of name EmployeeInfoController. This will be an Index action method. Generate a new Index.cshtml view from this action method.

Step 8: Open Index.cshtml, add the following CSS and script reference:

<style type="text/css">
thead {
background-color:palegoldenrod;
}
</style>
<script src="~/Scripts/jquery-1.9.1.min.js"></script>
<script src="~/Scripts/knockout-2.2.1.js"></script>

Step 9: Add the following HTML in the page:

<div>
Top Records:
  <select id="lstpagesize">
   <option value="0">0</option>
   <option value="1">1</option>
   <option value="2">2</option>
   <option value="3">3</option>
   <option value="4">4</option>
   <option value="5">5</option>
  </select>
  Skip Records:
  <select id="lstpageindex">
   <option value="0">0</option>
   <option value="1">1</option>
   <option value="2">2</option>
   <option value="3">3</option>
   <option value="4">4</option>
   <option value="5">5</option>
  </select>
  <input type="button" id="btngetdata" value="Get Data" data-bind="click: EmpModel.GetRecord"/>
</div>
<table border="1">
<thead>
  <tr>
   <th>EmpNo</th>
   <th>EmpName</th>
   <th>Salary</th>
   <th>DeptName</th>
   <th>Designation</th>
  </tr>
</thead>
<tbody data-bind="template: { name: 'EmpData', foreach: EmpModel.Employees }">
</tbody>
</table>

The above code defines the Drop Down list using HTML <select> tag. The Button of name ‘btngetdata’ is bound with its click event to the EmpModel.GetRecord method. Similarly the table is declared with the header information. The table body is bound with the HTML template of name ‘EmpData’ and foreach parameter is passed with observable array of name EmpModel.Employees. We will be declaring the HTML template and the ViewModel in next forthcoming steps.

Step 10: Add the HTML template below the above HTML tag added on the Index.cshtml:

<script type="text/html" id="EmpData">
<tr>
  <td>
   <span   style="width:100px;"  data-bind="text: $data.EmpNo" />
  </td>
  <td>
   <span style="width:100px;"  data-bind="text: $data.EmpName" />
  </td>
  <td>
   <span style="width:100px;"  data-bind="text: $data.Salary" />
  </td>
  <td>
   <span style="width:100px;"  data-bind="text: $data.DeptName"  />
  </td>
  <td>
   <span style="width:100px;"  data-bind="text: $data.Designation" />
  </td>
</tr>
</script>

The above HTML template defines table row with <span> in each table cell, each <span> is bound with the EmployeeInfo properties.

Step 11: Add the following script in the view below the HTML template:

<script type="text/javascript">
//The mode defining the observable array
var EmpModel = {
  Employees:ko.observableArray([])
};
//The method to get the records
EmpModel.GetRecord = function ()
{
  EmpModel.Employees([]);
  //Get the Page Records to be skipped based upon the page index
  var recordPerPage = $("#lstpagesize").find(":selected").val();
  var selectedPageIndex = $("#lstpageindex").find(":selected").val();
  //The URL for the WEB API
  //This selects the Top records and the page index
  //and skip those records from the data to be fetch
  var url = "/api/EmployeeInfoAPI?top=" + recordPerPage + '&$skip=' + (selectedPageIndex * recordPerPage) + '&$orderby=EmpNo';
  //Makes an ajax call
  $.ajax({
   type: "GET",
   url: url,
   success: function (data)
   {
    EmpModel.Employees(data);
   },
   error: function (err)
   {
    alert(err.status + "<--------->" + err.statusCode);
   }
  });
};
ko.applyBindings(EmpModel);
</script>

Editors Note: The jqXHR.success(), jqXHR.error(), and jqXHR.complete() callbacks have been deprecated in jQuery 1.8. To prepare your code for their eventual removal, use jqXHR.done(), jqXHR.fail(), and jqXHR.always() instead.

The method EmpModel.GetRecord makes an ajax call to WEB API. The url is as

var url = "/api/EmployeeInfoAPI?top=" + recordPerPage + '&$skip=' + (selectedPageIndex * recordPerPage) + '&$orderby=EmpNo';

This url has the OData query operator like top, $skip, $orderby. This query operators will now help to select the records from the server to implement pagination.

Step 12: Run the application and navigate to EmployeeInfo/Index. The following result will be displayed:

res_1

Now select Total Records as 2 and the Skip Records as 1. You will find the first two records from the above two tables will be skipped:

res_2

Conclusion

Exposing OData queries over ASP.NET WEB API is very easy to implement. Enabling querying of data like this makes the API really powerful across various possible clients. Data filters, pagination can be easily implemented to reduce huge amount of data fetched from the server and hence can save the bandwidth.

Download the entire source code of this article (Github)

Was this article worth reading? Share it with fellow developers too. Thanks!
Share on LinkedIn
Share on Google+
Further Reading - Articles You May Like!
Author
Mahesh Sabnis is a DotNetCurry author and Microsoft MVP having over 17 years of experience in IT education and development. He is a Microsoft Certified Trainer (MCT) since 2005 and has conducted various Corporate Training programs for .NET Technologies (all versions). Follow him on twitter @maheshdotnet


Page copy protected against web site content infringement 	by Copyscape




Feedback - Leave us some adulation, criticism and everything in between!
Comment posted by Mohammad on Monday, May 12, 2014 9:48 AM
Nice article, however it would be nice if you could make a simple demonstration without using knockout js, and do pagination using forward and backward buttons.
Comment posted by JLuis Estrada on Wednesday, May 21, 2014 5:30 PM
This is far from a real-life example. Please stop doing that
Comment posted by Limo on Friday, May 23, 2014 10:28 AM
Real-life or not this article serves its purpose well! Nice and simple.
Comment posted by Nikolai Manek on Sunday, May 25, 2014 1:26 PM
Very nice article. You just saved me an hour of work.
Comment posted by Tohid Azizi on Friday, May 30, 2014 8:24 AM
Thank you for the fantastic article. Very nice.
PS: You have a broken link man! Check this link, it doesn't work:
https://github.com/dotnetcurry/aspnet-webapi-paging
Comment posted by Steve5877 on Saturday, June 28, 2014 11:27 AM
Not sure why, but "top" isn't working. "skip" works fine, but I keep getting all of the records after the skip back.  I tried switching the order of the skip and the top but same result.
Comment posted by Steve5877 on Saturday, June 28, 2014 12:14 PM
Found it. You're missing a $ before the word top in the script.
Comment posted by MAhesh Sabnis on Wednesday, November 12, 2014 3:36 AM
The article provides  guidelines for implementations with OData. Further as per the requirements of the project/application on which you are working, it can be modified.

Thanks a lot to all readers.
Regards
Mahesh Sabnis