Fourth Week in Coding Bootcamp: SQL, Entity Framework, Writing REST APis

By: @kd_snyder

On October 12 the fourth week began. Over the weekend I had completed the SQL tutorials at Codecademy and SQLZoo, so I had written a lot of SQL queries and was ready to go! I struggled a bit with the more complex queries in SQLZoo involving subqueries and joining tables, and I need to find some more tutorials to get more practice. I even had some previous knowledge of SQL, as in 2003 I attended an Information Systems course, where I learned about Windows 2000 and SQL, and then obtained the Microsoft certifications MCP, MCSE, and MCDBA. However, whatever knowledge I retained from that needs to be updated!

Cameron taught us about various implementations of SQL Server; the class is using Microsoft SQL Server, along with SQL Server Management Studio. I learned that CRUD does not mean what it sounds like! It means Create, Read, Update, and Delete, which are the basic operations that are needed to work with data. They map to the HTTP operations POST, GET, PUT, and DELETE.

The first project was to write SQL queries to create tables for the LargeBank database, to hold information for customers, accounts, transactions, and statements. This project is the first step of a very exciting larger project where I get to build the back end SQL database, and then write an API to access it, and then write a front end that utilizes that API!

Next, I learned about using the Entity Framework to access the SQL tables, as an alternative to building SQL queries in C#. The Entity Framework is an ORM (Object Relational Mappers) that maps relational data to objects, so it is simpler to access the data in the SQL tables, but it involves some overhead, therefore it is slower for larger databases than simply building the SQL queries in C#. The next project was to write a C# application that uses the Entity Framework to access the SQL tables built in the previous project.This was getting better and better… I could use C# code to access the same SQL tables that I had built with SQL queries!

Then Cameron explained the basic concepts of REST (Representational State Transfer) APIs, how they should be designed and structured, and how to write them. The next assigned project was to use the ASP.Net Web API in C# to write the API for the previously built LargeBank database. There were a lot of new concepts here, and luckily Cameron has been recording his lectures, because I needed to watch his lecture again, and continually pause it to perform the actions that he was explaining, in order to get started writing my API!

The “bonus” this week (not sure who else saw it as a bonus) was that I learned how to write Unit Tests in Visual Studio! The reason for learning this was that a Web browser can be used to test the API GET methods to verify that they are working, but since a Web browser does only GET, and doesn’t do PUT, POST, or DELETE, it was necessary to find another way to test those API methods.

Once again I had to rewatch the lecture where Cameron taught how to write the test methods, and keep pausing it to get my Unit Tests started, but after rewatching, I managed to get my first tests working. It was really a challenge to understand how to interpret the structure of the HTTP results of the API methods, in order to properly verify them in the test methods, but after some struggling, I was able to add enough tests to have a basic test method for each API method. I had to get Cameron’s help for one where i was stuck, but for another one, I was able to figure out the problem myself! I can see that writing complete tests to cover every basic case and error case would be a time consuming, but necessary part of software development.

This week’s guest speaker was Neal Bloom from, and he was very optimistic about our prospects in the job market. He said that we are learning skills that employers are looking for, such as .NET, C#, Javascript, using APIs, writing APIs, etc. I liked hearing that!

The weekend assignment was to complete the tutorials at Knockout JS  in order to prepare for… front end!

Week 4 Review

Origin Studentsqoyr
n. [Oh-re-jen Stew-dent]
An organism that turns Coffee and Soylent into software.

This week we covered 3 main topics.

  1. Databases
    We kicked off this week by learning about Structured Query Language, which is a standard set of instructions to manage databases and provide a set of operations to Create, Read, Update, and Delete data in a database.  There are many different implementations of SQL (SQL Server, MySQL, PostgreSQL) which we covered in light detail before building our first “LargeBank Inc.” schema in SQL Server.
  2. Object Relational Mappers
    Object Relational Mappers are a common tool used in software to map Relational data into the Object-Oriented world. We talked about some different implementations of ORMs in the .NET framework before using Entity Framework to build an experimental C# application that could perform basic CRUD operations on the LargeBank database.
  3. REST APIs
    REST (Representational State Transfer) is an architectural style of writing web services that is very popular in the Real World™. We learned the basics of what constitutes a “RESTful” API, talked about how REST is just a pattern that can be implemented in NodeJS, PHP, ASP Web API, and Django to name a few before writing our first REST API using ASP Web API. This API exposed resources that internally use Entity Framework to return information from the LargeBank database.

There was definitely a lot to soak in this week, but students get lots of hands on time with these different technologies to internalize enough of how they work to build projects.

In Week 5 the goal will be to build a web app that consumes the API written in Week 4. We will be introducing the MVVM pattern (implementing using KnockoutJS) as well as some cool third party libraries (Bootstrap, Font Awesome to name a few) that can be used to spruce up the appearance of our web apps.