Introduction
Following Article will show how to load real time data
while user scrolls. The sample application built here is using RESTful service
for getting real time data from server and JQuery to make ajax call and load
data.
I last wrote an article on Building
and Testing WCF RESTful services and wanted to
explore RESTful service using ASP.Net Web API. I highly recommend you to go through the
following Hello
services webapi rest json and ajax blog, before you start with this article. The blog gives you an excellent introduction
to WebAPI, JSON and JQuery Ajax api.
Create New Web application
Create a new test application in your visual studio 2010.
Adding Latest Jquery
By default jquery-1.4.1.js would be available in the Scripts folder.
Delete them add the latest version by using NuGet package. Right click on the web application project and select “Manage NuGet Package”
Search for JQuery and click install. This will add the latest Jquery files to the script folder.
WEB API libraries
You can add the latest WEB api libraries using the NuGet interface. Search for WebApi and install the latest libraries. This will ensure all the libraries are downloaded and added to the project reference.
LINQ to SQL
I have been fond of LINQ to SQL for a long time now. I just love the flexibility and speed at which the data access layer can be developed using this.
Add a “LINQ to SQL classes” from data template. As we going to use this to communicate with the Northwind database, let us name it “NorthWind.dbml”.
Connect to a northwind database using the server explorer. Drag and drop Product, Supplier and Category tables on the designer.
Model Class
Create model class which would help us in transferring the data from Service to UI.
public class Product
{
public int ProductID { get; set; }
public string ProductName { get; set; }
public string SupplierName { get; set; }
public string CategoryName { get; set; }
public decimal UnitPrice { get; set; }
}
REST Service
RESTful service will be created using the WebAPI. Create a controller class ProductController.cs.
Make sure that this class will inherit ApiController as parent. Only then the controller will respond to the request.
public class ProductController : ApiController
GetProductsByPage method will fetch data based on mode flag. If the mode is SQL, then the data would be retrieved from
SQL database, else it would be returned from XML file.
Method takes 3 input parameters –
a. int pageSize – Will define the page size. Number of records to be fetched for every request.
b. int currentPage – Current page number.
c. string mode – Weather the data should be fetched from SQL server or XML file.
Return: Will return incremental products based on pagesize and current page.
public List<model.product> GetProductsByPage(int pageSize, int currentPage, string mode)
{
List<model.product> products = new List<model.product>();
if (string.Equals("XML", mode, StringComparison.OrdinalIgnoreCase)) {
string xmlPath = HttpContext.Current.Server.MapPath(@"/Data/Product.xml");
XElement xml = XElement.Load(xmlPath);
products = xml.Descendants("Product")
.Select(e => new Model.Product
{
ProductID = Convert.ToInt32(e.Descendants("ProductID").FirstOrDefault().Value),
ProductName = e.Descendants("ProductName").FirstOrDefault().Value,
SupplierName = e.Descendants("SupplierName").FirstOrDefault().Value,
CategoryName = e.Descendants("CategoryName").FirstOrDefault().Value,
UnitPrice = Convert.ToDecimal(e.Descendants("UnitPrice").FirstOrDefault().Value)
}).Skip(pageSize * currentPage).Take(pageSize).ToList();
}
else {
products = (from e in _context.Products
select new Model.Product
{
ProductID = e.ProductID,
ProductName = e.ProductName,
SupplierName = e.Supplier.CompanyName,
CategoryName = e.Category.CategoryName,
UnitPrice = Convert.ToDecimal(e.UnitPrice)
}).Skip(pageSize * currentPage).Take(pageSize).ToList();
}
return products;
}
</model.product>
Routing
You will need to set up the routing so that the requests are mapped to this controller. This is done in Application_Start method of Global.asax file.
void Application_Start(object sender, EventArgs e)
{
RouteTable.Routes.MapHttpRoute(name: "Default",
routeTemplate: "api/{controller}/{pageSize}/{currentPage}/{mode}",
defaults: new
{
pageSize = System.Web.Http.RouteParameter.Optional,
currentPage = System.Web.Http.RouteParameter.Optional,
mode = System.Web.Http.RouteParameter.Optional,
}
);
}
UI code
Following section will detail on how to use Jquery to fetch data on scroll and bind it to UI. Add a sample .aspx page.
Add reference to the JQuery library.
<script src="Scripts/jquery-1.7.2.min.js" type="text/javascript"></script>
Html table to display the products information.
<table id="productstable">
<tr class="row-template">
<th class="row-column">
Product Name
</th>
<th class="row-column">
Product Category
</th>
<th class="row-column">
Supplier Name
</th>
<th class="row-column">
Unit Price
</th>
</tr>
</table>
Div element to show status information
<div id="loader_msg">
Loading results......
</div>
Following block would be used as for templating.
<div id="templates" style="display: none">
<table>
<tr class="row-template">
<td class="ProductName" style="width: 100px;"></td>
<td class="ProductCategory" style="width: 100px;"></td>
<td class="SupplierName" style="width: 100px;"></td>
<td class="UnitPrice" style="width: 100px;"></td>
</tr>
</table>
</div>
JQuery Code
$(document).ready() will be called once the all the DOM elements are loaded on to the browser. By default I have set the pageSize=20 and CurrentPage=0.
Once the document is ready, AddProducts() function will be call which in turn will fetch the data from server and append it to the html table.
We have bound a function with $(window).scroll() event. This will get triggered every time, user scrolls the window. When the scroll reaches end of the window,
AddProducts() function will fetch next set of products based on the pagesize and currentpage.
var pageSize = 20;
var currentPage = 0;
var $productstable = null;
$(document).ready(function () {
$productstable = $("#productstable");
AddProducts(pageSize, currentPage);
$(window).scroll(function () {
if ($(window).scrollTop() == $(document).height() - $(window).height()) {
$('#loader_msg').html('Loading more results......');
currentPage = currentPage + 1;
AddProducts(pageSize, currentPage);
}
});
});
AddProducts function will make an ajax call to the restful service. Once the data is received,
it loops through the list of products to create new rows and append it to the products table.
function AddProducts(pageSize, currentPage) {
var url = "api/product/" + pageSize + "/" + currentPage + "/SQL"
$.ajax({
url: url,
contentType: "json",
success: function (data) {
if (data.length > 0) {
$.each(data, function (index, item) {
var $row = $("#templates").find(".row-template").clone();
$row.find(".ProductName").html(item.ProductName);
$row.find(".ProductCategory").html(item.CategoryName);
$row.find(".SupplierName").html(item.SupplierName);
$row.find(".UnitPrice").html(item.UnitPrice);
if (index % 2 == 0) {
$row.addClass("jtable-row-even")
}
$productstable.append($row);
});
}
else {
$('#loader_msg').html('All Results Loaded......');
}
}
});
}
Sample Code
ProductsDisplaySQL.aspx: Fetches the data from Northwind database. Make sure, you change the database connection string in web.Config for this to work.
ProductsDisplayXML.aspx: Fetches the data from Product.xml file in the Data folder.
Conclusion
I have tried to cover the most basic implementation of “Load data while scrolling”. There could be multiple and better ways to do it and would love to hear it from you.
References
http://www.kendoui.com/blogs/teamblog/posts/12-05-11/hello_services_webapi_rest_json_and_ajax.aspx
http://www.9lessons.info/2009/07/load-data-while-scroll-with-jquery-php.html
