Tech Junkie Blog - Real World Tutorials, Happy Coding!: 2019

Thursday, October 10, 2019

RIGHT JOIN works like the INNER JOIN, it just returns all the records that are on the right side of the = sign on the RIGHT JOIN clause. For example let's say you want to get a record of all customers who orders a certain product.

Monday, October 7, 2019

Views are virtual tables that you can create that others can use without knowing the complexity of the query, but can be used like a table. Also they can provided an added security by giving developers access to a view instead of the underlying table. Views does not contain data in itself the data stays at the tables that the views are created from. Complex views can degrade performance since they contain no data the query must be processed every time. Let's say a junior developer just came on board and he doesn't really know SQL that well. You can create a view of the more complex views to work with until he gets better with his SQL.

CREATE VIEW EmployeeTerritoriesDescriptions AS
SELECT e.FirstName + ' ' + e.LastName AS Name, t.TerritoryDescription,t.TerritoryID
FROM Employees e
INNER JOIN EmployeeTerritories et ON et.EmployeeID = e.EmployeeID
INNER JOIN Territories t ON t.TerritoryID = et.TerritoryID

The view above queries the employees territories using joins, by creating a view the person using the view does not have to know the underlying table structures that is in the database they can just use the view.

Thursday, October 3, 2019

In SQL the WHERE clause is the most common join you will see, it relates one or more tables together. For example you want to get the employeeis territory information in the Northwind database but you there are all in different tables.

As you can see from the above diagram the employee information is in the Employees table, while a linking table is used to link the employee to the territory in the EmployeeTerritories, and then there's the Territories table which contains the actual name of the territory in the Territory. How do we proceed to retrieve this information? With a WHERE clause of course. The WHERE clause allows us to retrieve information from all these tables and combine them into one result set. Here is the query that you would write with the WHERE clause:

SELECT e.FirstName + ' ' + e.LastName AS Name, t.TerritoryDescription,t.TerritoryID
FROM Employees e,Territories t, EmployeeTerritories et
WHERE e.EmployeeID = et.EmployeeID
  AND et.TerritoryID = t.TerritoryID

The query above joins the Employees table to the EmployeesTerritories table matching the two tables by the column EmployeeID, then after we get the records with matching records between those two tables, we and the word "AND" to add additional joins based on our first join. This time we want to match the TerritoryID in the EmployeesTerritories table with the TerritoryID column in the Territories which contains the TerritoryDescription field that we wanted. So with the WHERE clause we were able to work with three tables at once in one query.

You can filter the results even more by looking adding more filtering conditions in the WHERE clause. Let's say you want to get only employees who belongs to the Boston territory, from the first query you know that Boston has a TerritoryID of 02116. So to get the employees who belongs to the Boston territory you would write the query like the one below:

SELECT e.FirstName + ' ' + e.LastName AS Name, t.TerritoryDescription,t.TerritoryID
FROM Employees e,Territories t, EmployeeTerritories et
WHERE e.EmployeeID = et.EmployeeID
      AND et.TerritoryID = t.TerritoryID
      AND t.TerritoryID = 02116

Here are the results

Wednesday, October 2, 2019

The easiest and simplest way to explain what a subquery is to say that it's a query within a query. For example if you want to get the employee that belongs to specific territory in the Northwind database without a join, you would have to use a subquery. Like the following subquery.

SELECT EmployeeID, (FirstName + ' ' + LastName) AS Name
FROM Employees
WHERE EmployeeID IN (SELECT EmployeeID 
       FROM EmployeeTerritories 
       WHERE TerritoryID=01581) 

Things You Should Know About Subqueries:
  • They are not the most efficient performance wise
  • You can only retrieve a single column in the subquery, retrieving multiple columns will throw an error
Another way to use subqueries is to use it with Aggregate functions like the query below, which gets the average price for the category with ID value of 1:

SELECT CategoryName,
 (SELECT AVG(UnitPrice) 
 FROM Products WHERE CategoryID = 1) AS AvgPrice
FROM Categories
WHERE CategoryID = 1

Tuesday, October 1, 2019

SQL GROUPING allows you to segregate data into groups so that you can work on it separately from the rest of the table records. Let's say you want to get the number of products in a category in the Northwind database Products table. You would write the following query:

 SELECT COUNT(*) NumberOfProductsByCategory
FROM Products

The query above gives you the following results:

The query gives you the number of products in each category, however it's not very useful. You don't really know what category the count is for in each record. You might want to try to change the query into something like this:

SELECT CategoryID,COUNT(*) NumberOfProductsByCategory
FROM Products

The above query is more useful the preceding one, however it only gives you the CategoryID number not the CategoryName in the Categories table. Being the perfectionist that you are you say to yourself, I can do better. "Yes, I can". I think that was a campaign slogan. So you try a join like this:

SELECT c.CategoryName,c.CategoryID
RIGHT JOIN Categories AS c ON c.CategoryID = p.CategoryID

The above query joins the Categories table with the Products table to be able to select from both tables therefore giving the ability to select the CategoryName field.

 You probably think to yourself, hey if I can get the CategoryName with the RIGHT JOIN I can just add the GROUP BY filter and be done with it. Yah, my Shaolin Master from the SQL Wing will be proud. So you write the following query:

SELECT c.CategoryName,p.CategoryID,COUNT(*) AS NumberOfProductsByCategory
FROM Products p
RIGHT JOIN Categories AS c ON p.CategoryID = c.CategoryID
GROUP BY c.CategoryID

But behold you get an error! OMG! !@@##$%##$% Luckily your Shaolin Master walked into your room and saw that you were distraught. Your master ask you why you so distraught? You tell the Master, I don't understand, the join was working by itself but when I add the GROUP BY it throws me this stupid SQL error:

Column 'Categories.CategoryName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Your master says, if the expression is used in the SELECT statement then the same expression must be used in the GROUP BY part of the statement. You think ah ha! I can just add the columns in the SELECT list of columns to the GROUP BY filtering in the query. So you write:

SELECT c.CategoryID,c.CategoryName,COUNT(*) AS NumberOfProductsByCategory 
FROM Products p
RIGHT JOIN Categories AS c ON c.CategoryID = p.CategoryID
GROUP BY c.CategoryID,c.CategoryName

And like magic the result came to your screen. Tears of joy were streaming down your face because you've gotten the results that you were looking for.

Option 2: You can select what you want from the Products table and then use the GROUP BY in another select in a RIGHT JOIN to the get the COUNT() for each category. You will get the correct results with this query as well.

SELECT c.CategoryID,c.CategoryName,NumberOfProductsByCategory
FROM Categories AS c
RIGHT JOIN (SELECT CategoryID, COUNT(*) AS NumberOfProductsByCategory 
   FROM Products GROUP BY CategoryID) AS p ON p.CategoryID = c.CategoryID

As you have noticed the GROUP BY queries are missing the WHERE clause. What happened to the powerful WHERE clause? The reason we don't have a where clause is because WHERE does not work with GROUP BY results. That's where HAVING comes in. HAVING takes care of the filtering of the GROUP BY, while the WHERE clause takes care of the non GROUP BY results. For example let's say you want to get the categories with the most expensive products you can write a query that filters the product price with the WHERE clause and then filter the group data with the HAVING clause. You can write the following query to get the expensive products and their categories:

SELECT c.CategoryID,c.CategoryName,COUNT(*) AS NumberOfProductsByCategory 
FROM Products p
RIGHT JOIN Categories AS c ON c.CategoryID = p.CategoryID
WHERE p.UnitPrice > 10
GROUP BY c.CategoryID,c.CategoryName
ORDER BY NumberOfProductsByCategory DESC

The query above gets products that are more than $10, and gets the categories that has more than 9 products. The two filters are separate but they work together nicely with the WHERE and HAVING


Monday, September 30, 2019

Equality searches are great and efficient when you want exact matches or range of values. However, there will be times when you need to search a text field for not so perfect matches, perhaps a partial match is needed. Certain scenarios requires to search for patterns, such as an email address. That's when the LIKE operator is useful in SQL. The only caveat is that LIKE operators can only work with text fields. Examples: 1. A word/text with a % at the end, searches for all the records that begins with the letters before the percent sign

SELECT ProductName,UnitPrice
FROM Products
WHERE ProductName LIKE 'Chef%'

The query above returns all the records in the Products table that begins with the word "Chef"

 2. A word with % sign on both ends, means that the result will be any records that contains the enclosed word/text within the % sign

SELECT ProductName,UnitPrice
FROM Products
WHERE ProductName LIKE '%Hot%'

The query above searches for any records that contains the word "Hot" in the ProductName field. It brings back all the records that contains the word "Hot" regardless of the position that it resides in.

3. A word/text with a % at the beginning, searches for all the records that ends with the word/text after the percent sign. It's works in kind of the reverse of what you think will happen

SELECT ProductName,UnitPrice
FROM Products
WHERE ProductName LIKE '%Sauce'

The above query searches for all the records that ends with the word/text "Sauce" in the ProductName field in the Products table

 4. Let's try something a little bit tricky. Let's say your boss wants you to search for a spread that he likes, but does not know the exact spelling for. He would tell you it's call something like a "boys" n "berry" spread. To get that .00001% raise that you've always wanted you told your boss, I can do it!. So how will you search for such a spread?

SELECT ProductName,UnitPrice
FROM Products
WHERE ProductName LIKE '%Boy%y%'

The above query searches for a word that contains the text "Boy" and ends with the letter "y", and the result is, ta da! "Grandma's Boysenberry Spread" with that result you were able to get your .00001% raise and is finally able to afford half a Popsicle that you've been eyeing all week. All is well in the IT land once again.

Conclusion: The LIKE operator comes in handy when you need to match a text pattern in a text field. However, it takes longer to execute than an equality match. So use it sparingly, only when needed.

Thursday, September 26, 2019

The SUM() function is used to sum up all the values in the specified column.

SELECT SUM(UnitsInStock) AS TotalInventory
FROM Products

The above query gets the total number of units in stock for all products

Wednesday, September 25, 2019

The INNER JOIN functions like the WHERE clause by relating two or more tables using matching data. The difference is that the INNER JOIN is used in the FROM clause. So to the the employee's territory like the one we wrote in this blog. We would change the query into the query below to use the INNER JOIN instead of the WHERE clause.

SELECT e.FirstName + ' ' + e.LastName AS Name, t.TerritoryDescription,t.TerritoryID
FROM Employees e
INNER JOIN EmployeeTerritories et ON et.EmployeeID = e.EmployeeID
INNER JOIN Territories t ON t.TerritoryID = et.TerritoryID

With the result:

Tuesday, September 24, 2019

In our last post we created our business entities, in this post we are going to use our business objects to display information to the users.  I this post we are going to show the accounts to the users.  In the previous posts we have been setting up the environment the plumbing sort of speak.  Starting now we are going to build the application.  The first thing we want to do is counter-intuitive, we are going to take the database out of the equation and start using mock data to get so that we can see how our application will run.

In our previous post we created some user stories.  In this post we are going to work on one of the stories:

  • As a user I should be able to view my accounts. (2 pts)

It's a simple process, but we know exactly what we have to do with user stories.  It's a different mindset than the traditional requirements.  The typical requirement that you are used to seeing is

1.1.1 System shall allow the user to view his/her accounts

It's basically saying the same thing, but the first one the user stories is in plain English and it's a lot easier to comprehend.  You can take it to the business user, the web designer, or the web developer and it's easily understood.  That's the benefit of Agile development. 

First let's take the Iffe out of the account.js file so that it's easier to work with and then we want to make changes to the bankController.js file

Here is how the account.js file should now look like.

    function Account(balance, type) {
        this.balance = balance;
        this.type = type;

    Account.prototype.deposit = function (d) {

        this.balance = this.balance + d;
        return this.balance;

    Account.prototype.withdrawal = function (w) {
        this.balance = this.balance - w;
        return this.balance;

    function inherits(baseClass, childClass) {
        childClass.prototype = Object.create(baseClass.prototype);

    function Premium(balance, type) {
        this.balance = balance + (balance * .05);
        this.type = type;

    inherits(Account, Premium);

    Premium.prototype.transfer = function (from, to, amount) {
        from.balance -= amount;
        to.balance += amount;

    Premium.prototype.deposit = function (d) {
        this.balance = this.balance + d;
        this.balance = this.balance + (this.balance * .05);
        return this.balance;

    Premium.prototype.rebalance = function () {
        this.balance += this.balance * .05;

Here is the code we change in the bankController.js file

(function () {
    'use strict';

    angular.module('bankController', [])
        .controller('bankController', ["$scope", function ($scope) {
            $scope.accounts = [new Account(1000, 'Checking'), new Account(10000, 'Saving'), new Premium(100000, 'Premium Checking')];


Monday, September 23, 2019

Combinators in CSS is term used to define a style that combines more than one selectors together.

For example let's say you have the following markup

        <div id="combinator-div"><h1>I'll be back.</h1></div>
        <h1>Combinator me</h1>

Let's say you only want to apply the styles for the <h1> tag that's inside the combinator-div, you can use a combinator style which is a combination of the div id and the h1 tag as combination to style a very specific element on the page.  The style would look like the following

#combinator-div h1 {font-family: sans-serif; font-weight: bold; color: green;}

If you run the page with the code then you would get the following output

As you can see only the I'll be back <h1> is styled, the other <h1> is just not cool enough to get styled.

Friday, September 20, 2019

If you are developing Java EE applications, then WildFly is an excellent web application that you can set up easily.  In this post we are going to setup WildFly on a Windows machine.  The instructions should be similar for a Linux machine, it's just that the files ends with the .sh extensions.

Here are the steps to setup WildFly on your machine:

  1. Go the WildFly website and download the latest version,

Thursday, September 19, 2019

In this post we are going to use Google Fonts in our HTML markup, using Google Fonts is really easy to do.  All you have to do is go to then select the fonts that you would like to use.  Let's we want to use the Manjari, then All we have to do is click on the + sign next to the font.

Wednesday, September 18, 2019

In our previous post we created some mockups, now we are going to create some user stories so that we can started development with some idea of what should be included in the application.  This is just a quick planning session.

We'll pretend like we are running a Sprint, we can think of this as a very loose Sprint planning.

Sprint 1 User Stories;

  • As a user I should be able to view my accounts. (2 pts)
  • As a user I should be view my user profile. (1 pt)
  • As a user I should be able to view my account details. (2 pts)
  • As a user I should be able to view my past transactions. (1 pt)
  • As a user I should be able to view my upcoming transactions ( 1 pt)
  • As a user I should be able to search my transactions (2 pts)
Sprint 2 User Stories:
  • As a user I should be able to transfer money from my account (1 pt)
  • As a user I should be able to withdrawal money from my account (1 pt)
  • As a user I should be able to deposit money from my account (1 pt)
  • As a user I should be able to withdrawal money from my account (1 pt)

Tuesday, September 17, 2019

In the previous post we created the JavaScript business objects in our AngularJS application.  While that's great and all we should we really take a step back create some rough mockups of our banking application so that we will know what we will be building.

First let's mock up the home page:

On our home page we have a profile section were we can access our account/profile. Then we have the ACME bank logo/branding, and finally the accounts that we have in the bank.

The next mock-up is the the Accounts Details page, when we click on one of the accounts:

As you can see from the mock-up it's pretty straightforward.  You have an identification of the account, then a display of upcoming  transactions, and past transactions.  There's also a search box for searching of transactions.

Finally we have the Money Transfer screen which enables the users to transfer money into and out of the account.  It just has the basic information, where the money is coming from, where it's going to, the transfer date, the amount and the transfer button.

As you can see the mock-up is pretty simple and easy to change.  Actually it's just pen and paper.  I am a big fan of low-fidelity design.  Meaning simple and low effort.  We can change it anytime.

Previous: AngularJS SPA: Building The Bank Application JavaScript Objects (Business Entities)

In the last few posts we have been working with our models and retrieving the data natively in the application, but most of the project you will work with in real life will probably have you call some kind of api with an endpoint.  In this post we are going to substitute the products call what we made with an api.

Here are steps to create our first Api:

1. First we want to set up our application to use postman to test out our api. To do that right-click on the project and click on "Properties", in Properties screen click on "Debug" and uncheck "Launch browser" and make a note of the port number of the App URL.  We will make the api calls with postman initially to make sure that our api works.  Postman has become pretty popular with api development because it allows us to make api calls and see the results.  You can get postman here I would get the desktop version because it's more robust.

2. Now we are ready to create a ProductController to be our api controller, Asp.NET Core has the api control built-in so we don't have to do anything special for a controller to be a web api controller. So create a file call ProductController in the Controllers folder.  The ProductController should have the following code

using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;
using NorthwindCafe.Web.Data;
using System;

namespace NorthwindCafe.Web.Controllers
    public class ProductController : Controller
        private readonly  IProductRepository _repository;
        private readonly  ILogger _logger;

        public ProductController(IProductRepository repository, ILogger logger)
            _repository = repository;
            _logger = logger;

        public IActionResult Get()
                return Ok(_repository.GetProducts());
            catch (Exception ex)
                _logger.LogError($"Get products failed {ex}");
                return BadRequest("Get products failed");

The code above is pretty straightforward.  You have the define private variables _repository and _logger for the repository to hold the injected objects in the constructor. Then you define a method call Get with the decorator [HttpGet] to handle get requests.  The Get() method returns the list of products and retruns Ok = 200 status code if everything is ok and returns a 400 error status code if there's an exception.  The other important thing is the [Route("api/[Controller]")] decorator.  This is what you typed into the browser.  So for this route you would type http://localhost:50051/api/product into Postman.

3. So now we are ready to test our code in Postman, first we need to run our code, press CRTL+F5
Then select "GET" method on Postman call, type in the URL localhost:50051/api/product and you will see the list of products returned in Json, usually the resource in this product should be pluralize, but I forgot the s.  So the URLs should be localhost:<port>/api/products but since it's just development we can let it slide.  However, if you work with other people you might want to pluralize it.

Monday, September 16, 2019

In order to host websites in Tomcat we need to create a project to host servlets.  In this post we are going to create Dynamic Web project in Eclipse.

Here are the steps to create a web project in Eclipse:

1. Right-click on "Servers" → "New" → "Other"

2. Expand the "Web" node then select "Dynamic Web Project", then click "Next"→

Saturday, September 14, 2019

SELECT UnitPrice, ProductName
FROM Products
ORDER BY UnitPrice DESC, ProductName

The query above sorts the results based on the most expensive products, and then the product name. Useful if you want a secondary sort criteria. For example if there are multiple products that are $14.00 then those products will be sorted by their names after the price has been sorted.

Friday, September 13, 2019

In this post we are going to set Tomcat as a server in Eclipse so that we can interact with Tomcat through Eclipse.

Here are the steps to configure Tomcat to work with Eclipse:

You have to download the zip file version of the install to make Tomcat work with the Eclipse.  I had no luck with the Windows Installer

So go to

Then click on the .zip version of the download, and then extract the files into the

Now extract the content of the zip file into the folder C:\apache-tomcat-9.0.24

Thursday, September 12, 2019

Wednesday, September 11, 2019

Tuesday, September 10, 2019

Monday, September 9, 2019

Before we begin inserting data into mongoDB we need to understand the difference between Relational Database Management System (RDBMS) and mongoDB.  RDBMS is a database many of us worked with in the past.  You have tables and relationships between those tables.  We will use the ACME Bank that we were build our AngularJS SPA application as an example.

This is how the design would be on a RDBMS

Sunday, September 8, 2019

The "Server Explorer" tool in Visual Studio 2013 is a good tool at your disposal if want to interact with the database in GUI environment.  To create a new data connection to the database in the "Server Explorer" perform the following actions:

  1. Click on "Server Explorer" tab in the left hand side, then click on "Add Connection"

2.  In the "Data source" list box, select "Microsoft SQL Server", for data provider select ".NET Framework Data Provider for SQL Server", then click "Continue"

Saturday, September 7, 2019

There are times when you are handed over an .mdf file and are tasked to create a new database out of it.  There's no log just an .mdf file.  The easiest way to create a new database is to use the query editor in SQL Management Studio type the following into the Sql Query window

 ON (name= 'Northwind',
 filename = 'C:\Northwind.mdf')

The query above will create a new database for you call "Northwind", all you have to do is specify the location of the .mdf file and the "FOR ATTACH_REBUILD_LOG" will rebuild the log for you. You might run into a "Access denied" error but it's a permissions issue. Make sure the user that is running the query has permission to the file.

Friday, September 6, 2019

In this post we are going to go over some basic commands that you can run on a machine that has MongoDB installed.

One of the first thing you want to do is to connect to MongoDB so that you can run more commands.
Here is the command to connect to MongoDB

mongo --host=localhost --port=27017

All you need is the name of the host and port number, 27017 is the default port number and since we running the command on the MongoDB host, the host is localhost

Thursday, September 5, 2019

In the previous post we installed MongoDB on a Linux orperating system.  On this post we are going to setup the data folder that MongoDB needs and connect to MongoDB in the command line. 

Here are the steps:

  1.  Create a folder call /data/ then create another folder call db underneath it with the mkdir command, I've already created the folder so I can't do it on the command prompt again.
           But you basically run the following commands
               1.  mkdir /data
                2. mkdir /data/db

    2.  Now we want to start the MongoDB service with the command mongod --dbpath "/data/db"

    You only have to do this once, afterward you can just type mongod and the service should start.           You would need sudo rights to run these commands.

3. Now you can connect to you MongoDB instance with the mongo command

Wednesday, September 4, 2019

In a typical git scenario you would have a new branch for developers to work on, the when he or she is done. You do your code reviews and what not.  After you are satisfied with the results you would want to merge the new branch into the master branch.

In this post I am going to show you how to merge an existing branch into a master branch

Here are the steps:

1. First you want to checkout the master branch to work on it with the command git checkout master

Tuesday, September 3, 2019

In previous posts we created a new git repository locally and on github and then sync them up.  In this post we are going to create a new branch in the local git repository and push it to the remote repository.

First let's create a new branch with the following command in the local git repository

git checkout -b "Hour2"

Now following the tutorials in this post Hour 2: Enable ASP.NET Core to Serve Static Files

After you are done open the command line in the folder and type git add .

Monday, September 2, 2019

In the previous post we created a local git repository, in this post we are going to create a remote repository in github and link it to the local repository that we created.

Here are the steps to create a new repository in github:

1.  Log into your github account and click on the "New" button next to the text "Repositories"

Sunday, September 1, 2019

The NOT IN operator in SQL means that you are retrieving records in the database that does not match the values in a comma separated list. In other words it retrieves the inverse of the IN statement by itself. Here is an example of how you can use the IN operator in the products table in the Northwind database.

FROM Products
WHERE SupplierID NOT IN (1,2)

The above example all the products will be retrieved except for products with SupplierID of 1 or 2, here are the results

Saturday, August 31, 2019

When you need to do an insert into multiple database table you need to the get the ID of the insert so that you could use that ID for the next insert. Here is how you would do that with the Scope_Identity()which gets the last inserted ID back to you if you execute your query with the ExecuteScalar() method.

                SqlCommand cmd = new SqlCommand("INSERT INTO Users (" +
                    "LoginName," +
                    "FirstName," +
                     "LastName," +
                     "Password," +
                     "Email," +
                     "DOB," +
                     "Sex" +
                     ") VALUES (" +
                    "@Email," +
                    "@FirstName," +
                     "@LastName," +
                     "@Password," +
                     "@Email," +
                     "@DOB," +
                     "@Sex)" + 
                     " Select Scope_Identity();",conn);

Friday, August 30, 2019

Thursday, August 29, 2019

In the previous post we installed Git on Windows.  On this post we are going to set up Git with our user name and assign Bracks as our Git editor so that we don't have to edit git through the command line console all the time.

Here are the steps to setup your user name and editor for git:

1. First let's set our user name, with the command git config --global "Your name goes here"

Wednesday, August 28, 2019

A lot of examples on logging in ASP.NET Core shows you how to add console logging to your ASP.NET Core application.  But it's pretty useless in a real world application.  What you really want is to write to a file or a database.  There's a package called Microsoft.Extensions.Logging.Log4Net.AspNetCore that will make your application use log4Net.

Here are the steps to use log4Net:

1.  Right click on your NorthwindCafe.Web project and select "Manage NuGet Packages"

Tuesday, August 27, 2019

In the previous post we successfully used the repository pattern to retrieve products from the database and set them to the Product objects.

In this post we are going to display those products in the Index.cshtml page

Here are the steps:

1. Open the Index.cshtml page in the Views/Home folder
2. Now declare a model of enumerable of products at the top of the page like this 

@model IEnumerable<Product>

You will noticed that the Product class has a red underline on it, when you mouse over Visual Studio will say that the Product reference is missing

Monday, August 26, 2019

Now that we have our data in the database it is time for us to show the data to our users and we going to use the repository pattern to show our data.  A repository pattern is basically a conduit between the database and our business objects that has built in CRUD operations.  It is ideal for LOB applications.  There are many websites and books that explains the repository pattern better than I do.  Feel free to explore them.

Here are the steps to create a product repository for the products in the database:

1. Create a folder in the NorthwindCafe.Web solution call "Data"
2. Create an interface call IProductRepository in the "Data" folder, this will be the interface that we implement our repository from.

The code of the IProductRepository should look like this

using NorthwindCafe.Web.Models;
using System.Collections.Generic;

namespace NorthwindCafe.Web.Data
    public interface IProductRepository
        IEnumerable<Product> GetProducts();

Sunday, August 25, 2019

The COUNT() function returns the number of rows in the specified table. There are two ways you can use COUNT(), which are the following:

  1. COUNT(*) count all the rows in the table including
  2. COUNT(column) return all the rows that contains value for the column, excluding the columns with null value

FROM Customers

Saturday, August 24, 2019

The AVG() function gets the average of a column, the following query gets the average of the UnitPrice column in the Northwind Products table.

SELECT AVG(UnitPrice) AS AveragePrice
FROM Products

Friday, August 23, 2019

In the previous blog we created the NorthwindCafe database with Entity Framework Core.  Now we are going to seed the database so that we can work with the data.

Here are the steps to seed the NorthwindCafe database:

1.  Create a file call DBInitializer in the NorthwindCafe.Web  "Models" folder, in the file type in the following code

using System.Linq;

namespace NorthwindCafe.Web.Models
    public class DbInitializer
        public static void Initialize(NorthwindContext context)


            var categories = new Category[]
               new Category {Name = "Coffee", Description="Coffee", Products = new Product[] { new Product { Name = "Dark Roast", Description = "Dark Roast", Price = 2.0M } } },
               new Category {Name = "Tea", Description="Tea", Products = new Product[] { new Product { Name = "Chai", Description = "Chai", Price = 1.5M } } },
               new Category {Name = "Pastry", Description="Pastry", Products = new Product[] { new Product { Name = "Cupcake", Description = "Cupcake", Price = 1.25M } } },
               new Category {Name = "Food", Description = "Food", Products = new Product[] { new Product  { Name = "Hamburger", Description = "Hamburger", Price = 5.0M } } }

            foreach (var c in categories)



Thursday, August 22, 2019

In this post will are going to finally create the database that we have been preparing for in the last previous blog posts.  It's a two step process, first you have to add the NorthwindContext to the application in the Startup class, then you have to run the Entity Framework migration tool.

Here are the steps to create your NorthwindCafe database:

1.  Open the Startup.cs file, then type the following lines in the ConfigureServices method
            var connectionString = Configuration["Data:NorthwindContextConnection"];

            services.AddDbContext<NorthwindContext>(options => options.UseSqlServer(connectionString));

The line above gets the connection string from the appSettings.json file that we've created earlier. Then use the AddDbContext method in the services instance.  Dependency injection will take care of the plumbing for you.  Using lamba expression we tell the Entity Framework to use the Sql Sever provider for Entity Framework core.

Make sure you have the following namespaces in your Startup class

using NorthwindCafe.Web.Models;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.EntityFrameworkCore;

Wednesday, August 21, 2019

In our previous post we created the models for our Northwind Cafe application.  In this blog we will create the DbContext class which is the conduit between your entity classes and the database.  Think of it as a bridge that the database and the entity framework has to cross to get to each other.

Follow the steps below to create the NorthwindContext:

1. Create a class in Models folder call NorthwindContext

Tuesday, August 20, 2019

In the previous post we added a configuration file call appSettings.json file to store our connection string to the database that we are going to create through Entity Framework.  Even though Microsoft provides us with the Northwind database, we don't really want to use it because it's outdated.  We are going to modernize the database by rebuilding it from scratch with the code first approach with Entity Framework Core.  If you look at the existing Northwind database you will see that there's a lot of redundant data and tables.  For example there are tables for Customers, Employees, Suppliers and Shippers.  Those are basically roles, and we will take care of those roles later on in the series using the Identity framework.  What we are going to do is start out simple with just the Products, Categories, Orders, OrderDetails table and add on to those tables as we progress in building the application.

Monday, August 19, 2019

With text-transform you can transform your text into Autobots or Deceptacon? Not exactly, but what  you can do is transform your text to uppercase, lowercase, capitalize it. It's pretty self explanatory.

Here are the examples of a text-transform:

p.upper {text-transform:uppercase}
p.lower {text-transform:lowercase}
p.cap {text-transform: capitalize}

Here are the HTML markup:

            <p class="upper">uppercase me</p>
            <p class="lower">LOWERCASE ME</p>
            <p class="cap">captain america me</p>

Here is the output:

Previous: CSS: The em Unit

Sunday, August 18, 2019

The DATEPART function extracts the date part of a date, for example using the 'yyyy' expression allows you to extract the year from a given date. The query below queries all the employees who were hired in the year 1994 in the Northwind Employees table.

SELECT FirstName + ' ' + LastName  AS Employee, HireDate
FROM Employees
WHERE DATEPART(yyyy,HireDate) = 1994

Saturday, August 17, 2019

The MAX() function gets the highest value in the specified column, and the MIN() function gets the lowest value in the specified column

SELECT MAX(UnitPrice) AS HighestPrice, MIN(UnitPrice) AS LowestPrice
FROM Products

The query above gets the highest and lowest prices for the Products table in the Northwind database


Friday, August 16, 2019

Have you always wished to have more control with your text position, but feel like you limited with the text alignment of

Align Left text-align: left;
Align Center text-align: center;
Align Right text-align: right;
Justify text-align: justify;

Well you could always do that with CSS with the text-indent property, the only caveat is that it has to be a block based element.  Meaning it automatically puts a line break after the tag.  If you need a refresher on block vs line element, I have an excellent post on this topic here.  I know a shameless plug, oh and please click on my ads so that I can retire on a tropical island!

Anyways back to the tutorial.  So with the text indent you have total control over the indentation you need on your block element.  It could either be a length value or a percentage value.

Well first let's define a div that we want our text to be in, then the text-indent will be based on that div.

Let's say we have the following styles:

div { width: 800px}

p.tdp {text-indent: 15%}

And the following markup

    <p class="tdp">Indent this text please Indent this text please Indent this text please Indent this text please</p>
   <p>This is just normal text This is just normal text This is just normal text This is just normal text This is just normal text</p>

Thursday, August 15, 2019

There are often times when you see the measurement in CSS that looks something like this font-size: 2em; what does that actually mean? Well an em simply means its the multiplier of the font-size of the element. For instance 2em means that it will multiply the font-size of the element by 2x.

Let's say we have a div with a font-size of 15px, and there's a span tag with a font-size of 2em.  The span font-size of the span would be 30px, because the span is part of the div element.

Let's say have the following styles:

div {
  font-size: 15px;

span {
  font-size: 2em;

And with the following HTML markup:

<div>The font-size of the div element. <span>The span font-size</span>.</div>

The output is the following:

Wednesday, August 14, 2019

I believe the last iPhone I owned was an iPhone 3?  I don't remember, but it was a while back.  It was actually a very good phone.  I love Apple products, but the price just seems to increase on each iterations and the features were less and less with each upgrades.  I found myself with a life or death situation.  Should I go to the dark side (Darth Vader breathing) an "Andriod Phone".  Oh my god, stop it I said to  But at the time the Android phones were getting good.  So I decided to take the plunge and never looked back.

Anyways back to the present day, I have Apple envy because it has this great ecosystem, that I can't tapped into because I don't have an iPhone.  That is until I got the new iPad, which is the cheaper version of the iPad, but it's not really cheap at all it's the best iPad out there for the price.  I got it so that I can watch movies and read magazines while I was commuting.  What I found surprising was that once again I am tapped into the Apple ecosystem with my iPad.  I can sign up to all the new services that Apple is touting.  I am currently subscribed to News+ because I am a news junkie.  But if you think about it most of the new Apple services are ideal for consumption on a bigger screen.

Since I have unlimited plan on my Android phone, I used it as a hotspot for my iPad.  Put my iPad in a stylish mini bag, and I am good to go.  My iPad became a substitute for an iPhone, for less than 1K that's not bad. I know what you are thinking an Android phone and an iPad working together?  That's like the rebellion working with the empire.  But that's the closest thing to world piece right now and we should take it :)

If you want to contribute to my retirement fund you can buy it here on Amazon, it's actually a really good tablet for the price.  I mean the screen is amazing.  I got the older model and I am still using everyday. iOS is awesome, that's the one thing I like about the empire's product (I mean Apple's product).

Search This Blog