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 2012 and create a new Empty MVC application. Name it as ‘MVC40_WEBAPI_Pagination’. Since we will be using jQuery and Knockout.js framework, on this project right click and using ‘Manage NuGet Package’, get the latest jQuery and Knockout script files.
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:
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, 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:
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’ as below:
You will get the methods for GET|POST|PUT and DELETE.
Step 5:
Let’s include the OData libraries now.
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:
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 GetEmployeeInfoes method to return AsQueryable(). Open EmployeeInfoAPIController.cs and change the GetEmployeeInfoes() method as below:
//Modify the API Controller
[Queryable]
public IQueryable<EmployeeInfo> GetEmployeeInfoes()
{
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 on 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 below:
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:
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:
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)
Tweet
About The Author
1 comment:
Nice Article !!
Post a Comment