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.
The weekend assignment was to complete the tutorials at Knockout JS in order to prepare for… front end!