ASP.NET Web API has built-in support for some OData query parameters, and I want to write a tutorial that will use Web API, OData parameter to query data (sorting, filtering and paging) instead of implement it by ourselves in classic ASP.NET MVC like before. For more information of ASP.NET Web API and OData parameters please take a look at the article of Mike Wasson.
ASP.NET Web API has built-in support for some OData query parameters, and I want to write a tutorial that will use Web API, OData parameter to query data (sorting, filtering and paging) instead of implement it by ourselves in classic ASP.NET MVC like before. For more information of ASP.NET Web API and OData parameters please take a look at the article of Mike Wasson.
I assume that you have knowledge of ASP.NET MVC, Web API, JavaScript and jQuery, so this article content is all about showing of how to query data from an ApiController action.
We will create a new ASP.NET MVC 4 project named WebApiQueryingEx, this project is a product management page, it allow you to query the product list, filtering, paging and ordering the search result.
Product class
We need a Product class to store information of product in our store
public class Product
{
public int Id { get; set; }
public string Name { get; set; }
public int Quantity { get; set; }
}ProductRepository
In ProductRepository class, we will have a static variable called _products, it’s initialized when the application start and store a list of 100 products. ProductRepository has a static constructor to fill data into _products, and it has a method call GetAll to get all products, the GetAll method return a IQueryable object.
public class ProductRepository
{
private static IList<Product> _products;
static ProductRepository()
{
_products = new List<Product>();
var random = new Random();
for (var i = 0; i < 100; i++)
{
var product = new Product() {
Id = i,
Name = "Product " + i,
Quantity = random.Next(1000)
};
_products.Add(product);
}
}
public IQueryable<Product> GetAll()
{
return _products.AsQueryable();
}
}ProductsController
We need an API Controller called ProductsController to response HTTP GET request from client side to get products. The very simple ProductsController is implemented as below.
public class ProductsController : ApiController
{
private readonly ProductRepository _productRepository = new ProductRepository();
public IQueryable<Product> GetAll()
{
return _productRepository.GetAll();
}
}HomeController & Index.cshmtl
We have a HomeController to provide home page when user enter to the website, it has only one action called Index.
public class HomeController : Controller
{
//
// GET: /Home/
public ActionResult Index()
{
return View();
}
}The view Index.cshtml (for action Index of HomeController) has three columns, the first column show Search function and search result, the second column is to show a query that get the top five of products that have the biggest quantities, and the third column is for showing products by paging. Then index.cshtml is implemented as below:
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Product managements</title>
<script type="text/javascript" src="../../Scripts/jquery-1.6.2.min.js"></script>
</head>
<body>
<div>
<h2>Product Management</h2>
<div style="float: left; padding: 15px; border: 1px solid; margin: 5px;">
<h3>Search by Product Name</h3>
<div>
<label>Search: </label>
<input type="text" name="search-box" id="search-box" />
<input type="button" name="search-button" id="search-button"
value="Search" onclick="Search($('#search-box').val())" />
</div>
<div id="filter-list">
</div>
</div>
<div style="float: left; padding: 15px; border: 1px solid; margin: 5px;">
<h3>Top Quantity List</h3>
<div id="top-quantity-list">
</div>
</div>
<div style="float: left; padding: 15px; border: 1px solid; margin: 5px;">
<h3>Product Lists</h3>
<div id="product-list">
</div>
<div id="product-paging">
</div>
</div> </div>
</body>
</html>Searching products by name
We will have a JavaScript method called Search to provide search-by-name function to get products by name criteria. We use $filter parameter to get the products, which contain the criteria in their names.
function Search(criteria) {
$('#filter-list').html('ID - NAME - QUANTITY');
$.getJSON("/api/products?$filter=substringof('" + criteria + "', Name) eq true",
function (data) {
$.each(data, function (key, val) {
var str = val.Id + ' - ' + val.Name + ' - ' + val.Quantity;
$('<div/>', { html: str })
.appendTo($('#filter-list'));
});
});
}If you have taken a look to Index.cshml code, you will see that we have a call to Search method on search-button button onclick method.
<input type="button" name="search-button" id="search-button"
value="Search" onclick="Search($('#search-box').val())" />When you run the /Home/Index, you can type any criteria on the #search-box textbox and click on Search button to get the result, the result will be filled below the Search button.
Get top-five of products that have largest quantities
We have to implement a JavaScript method to get top-five of products that have the largest quantities, this method will send a HTTP request to /api/products with two parameters $top and $orderby to sort the result and get only the first-five result after sorting. The URI that our GetTopFive method will request is “/api/products?$top=5&$orderby=Quantity desc”
function GetTopFive() {
$('#top-quantity-list').html('ID - NAME - QUANTITY');
$.getJSON("/api/products?$top=5&$orderby=Quantity desc",
function (data) {
$.each(data, function (key, val) {
var str = val.Id + ' - ' + val.Name + ' - ' + val.Quantity;
$('<div/>', { html: str })
.appendTo($('#top-quantity-list'));
});
});
}The result:
Paging the result
To apply paging when getting all products, we will use two parameters $top and $skip in request URI to get the paging result. The JavaScript method GetProducts is to enable paging product list.
function GetProducts(pageId, pageSize) {
$('#product-list').html('ID - NAME - QUANTITY');
var skipCount = (pageId - 1) * pageSize;
$.getJSON("/api/products?$skip=" + skipCount + "&$top=" + pageSize,
function (data) {
$.each(data, function (key, val) {
var str = val.Id + ' - ' + val.Name + ' - ' + val.Quantity;
$('<div/>', { html: str })
.appendTo($('#product-list'));
});
var prev = pageId > 1 ? pageId - 1 : pageId;
var next = data.length > 0 ? pageId + 1 : pageId;
var str = '<a href="#product-list" onclick="GetProducts(' + prev
+ ',10)">Previous</a> <a href="#product-list" onclick="GetProducts('
+ next + ',10)">Next</a>';
$('#product-paging').html(str);
});
}We update the JavaScript code to call GetProducts when page is ready:
$(document).ready(function () {
GetTopFive();
GetProducts(1, 10);
});Result is:
This is not a fully functional paging solution, but it is enough to demonstrate paging technique using API Controller and OData query parameter.
Conclusion
This article is to introduce you a new way to query data without coding too much on server side by using Web API and OData query parameter. Hope that you will enjoy with Web API!