Click here to Skip to main content
15,125,968 members
Articles / Web Development / ASP.NET
Article
Posted 12 Jul 2018

Stats

6.8K views
7 bookmarked

AngularJS and REST API: Part 4

Rate me:
Please Sign up or sign in to vote.
1.00/5 (1 vote)
12 Jul 2018CPOL5 min read
AngularJS and REST API Tutorial - Part 4

Image 1

Introduction

In AngularJS and REST API: Part 2, I showed a database table called Airport with airport data from https://openflights.org/data.html. (The same table is also used in Part 3.) There are two issues with this table:

  1. There is a lot of unnecessary data duplication because the name of the City where the airport is located is spelled out completely. San Diego, for example has three airports, so the city name "San Diego" is duplicated in the table three times. Removing this redundancy is a process called normalization.
  2. The state the City is in does not appear in the data. Granted, you can often deduce the state from the name of the city, for example, most everyone knows Los Angeles is in California, but what if the city is Springfield? (There are more than 30 Springfields in the US!)

In this part 4, I will address both issues. To solve the first issue, I have created a new table called City where each row contains information for a city. Each row in the City table has a unique ID, CityId, and that ID in turn will be used to identify the city in the new airport table, AirportNew. In database parlance, the CityId is a foreign key, and the relationship is one-to-many since there can be many airports in one city (like San Diego or Sacramento), but an airport can only be in one city. How a one-to-many relationship is configured in Entity Framework is described below. To address the second issue, the state is in the City table (as well as the name of the city, county, and latitude and longitude).

Using the Code

Download the AirportDatabaseProjectPart4.zip file and extract it. It consists of a directory called AngularJS_REST_API_AirportLocator which contains the JavaScript and HTML files, six SQL script files, and the Visual Studio 2015 project AirportsAPI. Open the AirportsAPI project with Visual Studio. You will need to change the AirportEntities and AirportNewEntities connection strings in Web.config to point to your database by setting [YOUR_SERVER_NAME] and [YOUR_DATABASE_NAME] the name of your server and database, respectively. Build it by pressing F6; the project should build successfully with no errors. Press F5 to run the AirportsAPI project in debug mode. As described in AngularJS and REST API: Part 2, you will need to change 'YOUR CREDENTIALS' in ListController.js to your Bing credentials. I'm using Entity Framework's database first model so I have provided six SQL scripts to create and populate the database tables. In the CreateAirportTable.sql, CreateCityTable.sql and CreateAirportTableNew.sql script files, change [YOUR_DATABASE_NAME] to the name of your database and then execute the three scripts in SQL Server Management Studio (SSMS) to create the three tables. After the tables have been created, you can populate them by executing AirportDataBaseLosAngeles.sql, InsertIntoCityTable.sql, and InsertIntoAirportNewTable.sql scripts in SSMS.

Table Joins

Most readers are aware that table joins are one of the most powerful features of a relational database, and are used to combine rows from two or more tables based on a related column, in this case, CityId. To illustrate, here is the SQL to join the AirportNew and City tables using a left join which returns all records from the left table (AirportNew), and the matched records from the right table (City). Since every CityId in the AirportNew table has a corresponding CityId in the City table, there are no NULL rows.

SQL
select AN.Name, AN.CityId, AN.ICAO, AN.Latitude, AN.Longitude, City.Placename as 'City Name',
City.AdminName2 as County, City.AdminCode1 as State from AirportNew AN left join City
on AN.CityId = City.CityId
Name CityId ICAO Latitude Longitude City Name County State
Hayward Executive Airport 1 KHWD 37.659199 -122.122002 Hayward Alameda CA
Livermore Municipal Airport 2 KLVK 37.693401 -121.820000 Livermore Alameda CA
Chico Municipal Airport 4 KCIC 39.795399 -121.858002 Chico Butte CA
Buchanan Field 5 KCCR 37.98970 -122.056999 Concord Contra Costa CA
...              

A right join, as you would expect, returns all records from the right table (City), and the matched records from the left table (AirportNew). However, since there are CityIds in the City table that are not referenced in the AirportNew table, there are some NULL rows. In other words, the cities 'Coalinga' and 'Huron' are in our City table with CityId of 8 and 9, but there are no rows in the AirportNew table with a CityId of 8 or 9, hence the NULL rows as shown below:

SQL
select AN.Name, AN.CityId, AN.ICAO, AN.Latitude, AN.Longitude, City.Placename as 'City Name',
City.AdminName2 as County, City.AdminCode1 as State from AirportNew AN right join City
on AN.CityId = City.CityId
Name CityId ICAO Latitude Longitude City Name County State
Buchanan Field 5 KCCR 37.989700 -122.056999 Concord Contra Costa CA
Jack Mc Namara Field Airport 6 KCEC 41.780201 -124.237000 Crescent City Del Norte CA
Lake Tahoe Airport 7 KTVL 38.89390 -119.995003 South Lake Tahoe El Dorado CA
NULL NULL NULL NULL NULL Coalinga Fresno CA
NULL NULL NULL NULL NULL Huron Fresno CA
...              

Configuring One-To-Many in Entity Framework

Since I am using Entity Framework's Database First model, I added an ADO.NET Entity Data Model and Visual Studio auto-generated the City and AirportNew classes. In order to implement the one-to-many relationship, Entity Framework creates a collection navigation property public virtual ICollection<AirportNew> AirportsNew in the City class:

SQL
public partial class City
{
	public City()
	{
		this.AirportsNew = new HashSet<AirportNew>();
	}
	public int CityId { get; set; }
	(remaining properties)
	...
	public virtual ICollection<AirportNew> AirportsNew { get; set; }
}

And it creates a reference navigation property public virtual City City in the AirportNew class:

SQL
public partial class AirportNew
{
	public int ID { get; set; }
	public int VendorID { get; set; }
	public string Name { get; set; }

	public int CityId { get; set; }
	public string IATA { get; set; }
	(remaining properties)
	...

	public virtual City City { get; set; }
}

API

The API for this Part 4 is the same as Part 3, except data is fetched from the AirportsNew table, and the word 'New' is added to the API so for example, AirportsByRect is now AirportsNewByRect: curl -X GET http://localhost:55213/api/AirportsNewByRect/34.4/-119.3/33.7/-117.9/.

I've also created new HTML files in AngularJS_REST_API_AirportLocator to use the AirportNew table; the new HTML files are AirportLocaterApproach1New.html, AirportLocaterApproach2New.html and AirportLocaterApproach3New.html. Like the HTML files in the previous projects, they demonstrate the AngularJS ng-repeat, AngularJS Autocomplete, and AngularJS md-tab and md-list directives, respectively. In all three, I've added a button that displays the CityId; clicking on it displays the City information from the city table as shown in the image above.

Conclusion

The concepts of table joins, foreign keys, and one-to-many relationships are essential to working with relational databases. Entity Framework has conventions for implementing these database features.

Version 4.0.0.0

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

charles922
Software Developer (Senior)
United States United States
Chuck Peasley is a developer in Orange County, CA

Comments and Discussions

 
QuestionConnect to MySQL database Pin
charles92211-Dec-18 9:23
Membercharles92211-Dec-18 9:23 
A question was asked on how to connect to a MySQL database. First, the tables can be created in MySQL Workbench by modifying the TSQL scripts in the project to MySQL syntax; there are a number of resources on the Internet to do so. A modification to AirportNewController.cs such as the following is one way to accomplish this (you will need to add
using MySql.Data.MySqlClient;
):

[Route("api/AirportNew2/{id}")]
[HttpGet]
public AirportNew AirportNewById2(int? id)
{
         if (id == null) return null;

	using (MySqlConnection conn = new MySqlConnection("Server=localhost;Database=mysql;UID=root;Password=MyPassword"))
		{
		conn.Open();

		using (MySqlCommand cmd = conn.CreateCommand())
		{
			cmd.CommandText = string.Format("select * from AirportNew where ID={0} limit 1;", id.Value);
			MySqlDataReader reader = cmd.ExecuteReader();
			if (!reader.HasRows) return null;

			reader.Read();
			AirportNew airport = new AirportNew(
				Int32.Parse(reader.GetString("VendorID")), 
				reader.GetString("Name"),
				Int32.Parse(reader.GetString("CityId")),
				reader.GetString("IATA"),
				reader.GetString("ICAO"),
				Decimal.Parse(reader.GetString("Latitude")),
				Decimal.Parse(reader.GetString("Longitude")),
				Int32.Parse(reader.GetString("Altitude")),
				Decimal.Parse(reader.GetString("Timezone")),
				reader.GetString("DST"),
				reader.GetString("TZ"),
				reader.GetString("Type"),
				reader.GetString("Source"));
					
			return airport;
		}
	}
}

QuestionPHP To Display Rows from Database Table Pin
charles92215-Nov-18 14:01
Membercharles92215-Nov-18 14:01 
QuestionCorrupt projet file Pin
FredyAlfredo13-Jul-18 8:08
MemberFredyAlfredo13-Jul-18 8:08 
AnswerRe: Corrupt projet file Pin
Sean Ewington14-Aug-18 5:28
editorSean Ewington14-Aug-18 5:28 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.