Details

Written by: Stanko Milosev

Category: WebApi

Published: 17 November 2014

Last Updated: 06 April 2022

Hits: 7065

This page helped me to write this article. Idea was to write one my hobby project where I will display data from mySql database where I am keeping Serbian Real Estates collected from different agencies (at this moment two).

This article will have two parts, one is for creating web api, and second one is jQuery part (lazy load, filling screen, load more data on resize event)

Start new project, choose ASP.NET MVC 4 Web Application, and call it for example RealEstateWebClient:

Choose web api:

 Since this project is for Serbian Real Estates, my model looks like this:

  public class RealEstateModel
  {
    public int Id { get; set; }
    public string Company { get; set; }
    public string City { get; set; }
    public string Location { get; set; }
    public string Type { get; set; }
    public int SquareMeters { get; set; }
    public float Price { get; set; }
    public string Link { get; set; }
    public int Page { get; set; }
    public int Active { get; set; }
    public string UpdateTime { get; set; }
    public string UpdateDate { get; set; }
    public string InsertTime { get; set; }
    public string InsertDate { get; set; }
  }

Now we can add controller. Right click on "Controllers" node in Solution Explorer:

Choose "Empty API controller", and call it RealEstatesController for example:

My controller looks something like:

using System.Web.Http;

namespace RealEstateWebClient.Controllers
{
  using System;
  using System.Collections.Generic;

  using MySql.Data.MySqlClient;

  using RealEstateWebClient.Models;

  public class RealEstatesController : ApiController
    {

      public IEnumerable GetAllRealEstates([FromUri] int from, int to)
      {
        string MyConString = System.Configuration.ConfigurationManager.ConnectionStrings["MovieDBContext"].ConnectionString;

        string sql = "select * from RealEstate limit " + from + ", " + to;

        try
        {
          var connection = new MySqlConnection(MyConString);
          var cmdSel = new MySqlCommand(sql, connection);

          connection.Open();

          MySqlDataReader dataReader = cmdSel.ExecuteReader();

          List pom = new List();

          while (dataReader.Read())
          {
            pom.Add(new RealEstateModel
            {
              Id = int.Parse(dataReader["id"].ToString()),
              Company = dataReader["company"].ToString(),
              City = dataReader["city"].ToString(),
              Location = dataReader["location"].ToString(),
              Type = dataReader["type"].ToString(),
              SquareMeters = int.Parse(dataReader["squaremeters"].ToString()),
              Price = float.Parse(dataReader["price"].ToString()),
              Link = dataReader["link"].ToString(),
              Active = int.Parse(dataReader["active"].ToString()),
              UpdateTime = dataReader["updatetime"].ToString(),
              UpdateDate = dataReader["updatedate"].ToString(),
              InsertTime = dataReader["inserttime"].ToString(),
              InsertDate = dataReader["insertdate"].ToString()
            });
          }
          return pom;

        }
        catch (Exception e)
        {
          List error = new List();
          error.Add(
            new RealEstateModel
            {
              City = e.Message
            });
          return error;
        }


        return null;
      }

    }
}

Here notice how I call connectionString from Web.config:

string MyConString = System.Configuration.ConfigurationManager.ConnectionStrings["MovieDBContext"].ConnectionString;

Of course you have to install first MySQL, you can do that with NuGet, just write MySQL in the search box.

Then notice how parameters of my function look like:

public IEnumerable<RealEstateModel> GetAllRealEstates([FromUri] int from, int to)

that is because we will call our API with from - to parameters

---

Our controller is ready, now we need client part. Right click on project, click Add -> HTML page:

In my case I call it index.html, and it looks like this:

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
    
    <head>
        <title></title>
        <meta content="width=device-width, initial-scale=1" name="viewport">
        <script type="text/javascript" src="/jquery-2.1.1.js"></script>
        <script type="text/javascript" src="/index.js"></script>
        <link type="text/css" href="/index.css" rel="stylesheet">
    </head>
    <body>
    
        <table id="realEstatesTable">
 
            <thead>
                <tr>
                    <th>Id</th>
                    <th>Company</th>
                    <th>City</th>
                    <th>Location</th>
                    <th>Type</th>
                    <th>SquareMeters</th>
                    <th>Price</th>
                    <th>Link</th>
                    <th>Page</th>
                    <th>Active</th>
                    <th>UpdateTime</th>
                    <th>UpdateDate</th>
                    <th>InsertTime</th>
                    <th>InsertDate</th>
                </tr>
            </thead>
            
            <tbody>            
            </tbody>

        </table>

    </body>

</html>

Then css will look like I already described here, and now comes jQuery.

First we will create function to load data:

function loadData(from, to) {
	uri = 'api/Realestates/?from=' + from + '&to=' + to;
	$.getJSON(uri)
		.done(function (data) {
			from = from + 10;
			fromGlobal = from;
			
			$.each(data, function (key, item) {

				$('#realEstatesTable > tbody:last').append(
					'<tr>' +
						'<td>' + item.Id + '</td>' +
						'<td>' + item.Company + '</td>' +
						'<td>' + item.City + '</td>' +
						'<td>' + item.Location + '</td>' +
						'<td>' + item.Type + '</td>' +
						'<td>' + item.SquareMeters + '</td>' +
						'<td>' + item.Price + '</td>' +
						'<td> <a href="' + item.Link + '" target=_blank>' + item.Link + '</td>' +
						'<td>' + item.Page + '</td>' +
						'<td>' + item.Active + '</td>' +
						'<td>' + item.UpdateTime + '</td>' +
						'<td>' + item.UpdateDate + '</td>' +
						'<td>' + item.InsertTime + '</td>' +
						'<td>' + item.InsertDate + '</td>' +
					'</tr>'
				);
			});
			if ($("#realEstatesTable").height() < $(window).height()) {
				loadData(from, to);
			}
		});

};

Here notice how my URI variable looks like:

uri = 'api/Realestates/?from=' + from + '&to=' + to;

API is called with big "R" and with "s" on the end for unknown reason... I guess because I named controller as RealEstatesController

Then notice in done method of getJson recursive call:

if ($("#realEstatesTable").height() < $(window).height()) {
  loadData(from, to);
}

With condition:

$("#realEstatesTable").height() < $(window).height()

I am checking if height of table (mount of loaded data) is less then height of the screen, if table height is smaller - load data, otherwise don't load it any more.

Then load data if user scrolled to the bottom of page:

$(window).scroll(function () { 
  if ($(window).scrollTop() + $(window).height() == $(document).height())
  {
    loadData(fromGlobal, numberOfItems);
  }
});

Here notice condition:

if ($(window).scrollTop() + $(window).height() == $(document).height())

Also, if user, for example, had browser in restored mode (not maximized), then we need to load data again if the screen is not filled:

$(window).resize(function () {
	if ($("#realEstatesTable").height() < $(window).height()) {
		loadData(fromGlobal, numberOfItems);
	}
});

Example you can download from here. Also here you can see live demo, but I am not sure for how long will I keep it...