MongoDB In-Depth: The Difference Between RDBMS and MongoDB

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.

MongoDB In-Depth: Installing MongoDB on Windows

In the previous post we installed MongoDB on Linux on this post we are going to install MongoDB on Windows.

Here are the steps:

1. Go to https://www.mongodb.com/download-center/community, then click on the "Download" button

MongoDB In-Depth: Basic MongoDB Commands

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

ACME Bank: Step 10: Getting Values From MySQL Database In Asp.Net Core Web Api Controller

 In the last post we created our ACME Bank database in MySQL, the next step is to get the values from the Values table in the Asp.Net Core WebApi controller.  

Let's create some test data so that we could retrieve the values from the database.  The Values table could contain anything.  So I am going to store famous philosophers throughout history.  These philosophers are so famous that they only have one name: 

You can run the SQL insert statement below to seed the data in MySQL:

 

INSERT INTO acmebank.Values (
    Name
)
VALUES
    (
        'Socrate'
    ),
    (
        'Plato'
    ),
    (
        'Spock'
    ),
    (
	'Thanos'
    );

So your Values table should look like this when you executed the insert query

ACME Bank: Step 9: Create Our Database In MySQL Using Asp.Net Core And EntityFrameworkCore

 In this post we are going to create our first Entity Framework migration and creating an actual database in MySQL. 

In order to do that we need to add a few NuGet packages to our Asp.Net Core project including the Entity Framework Core package.  But, before we do that we want to find out what what version of .NET Core we are running.  Obviously I have it memorized, but for the rest of you, you can type the command dotnet --version to figure out what the version is :)  It's always a good idea to try and match the package version with the .NET Core runtime you have.  It's one of the more annoying thing with Angular and Asp.Net Core, it's changing constantly. So as you can see my version 3.1.xxx so I should try to install packages that are made for 3.1.x.

The first package we are going to install is the Microsoft.EntityFrameworkCore.  So open the ACMEBank.API project with Visual Studio Code.  Press Ctrl+Shift+P and type NuGet and select

ACME Bank: Step 8: Install MySQL Workbench On Fedora

 In this post we are going to install MySQL Workbench on Fedora.  MySQL Workbench is a great GUI database management tool for MySQL.  Even though we can do everything we need with MySQL on the terminal it's nice sometime to see the tables and data visually.

Here are the steps to install MySQL Workbench:

1, Go to the yum repository for MySQL at https://dev.mysql.com/downloads/repo/yum/ select the Fedora repository, and click "Download"

ACME Bank: Step 7: Add A Dev User In MySQL

 In this post we are going to circle back to MySQL and continue setting up our MySQL database, so that we can use it in our application.  Since we don't want to use our root for log in and we turned off root login for remote connection.  We have to create a dedicated user for our application.  Here are the steps to create a dedicated dev use in MySQL

1. Log into MySQL with root using this command mysql -u root -p

2.  In the MySQL prompt type CREATE User 'devuser'@'localhost' IDENTIFIED BY 'P@ssw0rd'; to create the user, obviously if this is your production environment you would want to use a more secure password

3. Then type GRANT ALL PRIVILEGES ON *.* TO 'devuser'@'localhost' WITH GRANT OPTION; to grant the user all the privileges on all the objects

4. Now type FLUSH PRIVILEGES; to update and apply the privileges

ACME Bank: Step 3: Install MySQL on Fedora Linux

The most important thing for most modern dynamic application these day is choosing a database to store your data.  If you are on a Windows environment then you use SQL Server database that is a fine choice. But if you are on Linux MySQL is a great choice also.  In this post we are going to go through how to install MySQL server on our Fedora development machine.

Here are the steps to install MySQL on Fedora Linux:

1. First we need to add the MySQL repository to our machine by tying in the following command in the terminal

sudo dnf -y install https://dev.mysql.com/get/mysql80-community-release-fc32-1.noarch

MongoDB In-Depth: Setting Up And Starting MongoDB For The First Time

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.

SQL Server 2014 : Creating a Sysadmin Using a Windows 8.1 Account

The one of thing that Windows 8 forces you to do is to sign in with an e-mail account.  I am not here to debate if it's a good thing or a bad thing.  But I just wanted to say, Microsoft why do you make our lives so complicated.  I just wanted Windows 7 with a touchscreen.  I digress :(   Anyways, if you install SQL Server on Windows 8 there is a little quirk that you have to deal with.  When you search for an account to add to your dba user login, you have to search on the entire username including the stuff after the @ sign.  Once again, I digress :(

Anyways here is how you add a dba to SQL Server 2014 on a Windows 8 machine.

1.  Connect to your instance of SQL Server, then expand the "Logins" node

SQL Server : Installing AdventureWorks Sample Databases from Microsoft

1. Type in the following URL into your browser's address bar

     AdventureWorks Database Download

2.  Click on the "Download" button on page

SQL: The NOT IN Operator

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.

SELECT * 
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

SQL: The IN Operator

The IN operator in SQL means that you are retrieving records in the database that matches the values in a comma separated list. Here is an example of how you can use the IN operator in the products table in the Northwind database.

SELECT * 
FROM Products
WHERE SupplierID IN (1,2)

In the above example all the products with the SupplierID of 1 or 2 are retrieved.

Hour 10 ASP.NET Core : Add appSettings.json Configuration Files For Database

Now that we have most of our static contents taken care of for our application, meaning we did everything we could without a database.  It's time to create our database.  But before we can do that we need a place to store our connection string to the database.  Usually we just store the connection string in the web.config file in our web application.  However, since ASP.NET Core is trying to break free from the old way of doing things, there's a new way to store configuration information which is more flexible the old web.config way.  As usual it also requires more work.

Create a Data Science Virtual Machine on Microsoft Azure

It is time consuming and a lot of work to set up your own data science virtual machine locally.  If you don't mind the cost, the alternative to is to setup a data science Virtual Machine in Azure and let Azure take care of the plumbing such as the hardware requirements and etc, because a data science Virtual Machine is a little beefier than an regular mainstream laptop.

ASP.NET Core : Add NorthwindContext To Startup Class And Create Database

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;

ASP.NET Core : Configure project.json File To Support Entity Framework Core

In the previous post we created the DbContext for the Northwind Cafe application.  In this post we will configure the project.json file to support Entity Framework Core.

Here are the steps:

1.  Open the project.json file

ASP.NET Core : Entity Framework Core Models For Northwind Cafe

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 care 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.

SQL: Using Parentheses To Get Expected Result

SQL Server as well as other DBMS has an order of evaluation that can throw you off. Especially when you have more than one comparison in the WHERE clause. In this example I will show you the difference between using a parentheses and not using one, and how by using parentheses can give the results that you want.  Suppose you want to get the products with CategoryID 1 and 2 that are priced less than 15 dollars in the Products table in the Northwind database. Here is the query without the parentheses:

SELECT CategoryID,ProductName,UnitPrice
FROM Products
WHERE CategoryID = 1 OR CategoryID =2 AND UnitPrice < 15

When you run the query above you would expect that all the records retrieved will have a unit price of less than $15 dollar but that is not the case. Below is the result from the query.

End-to-End ASP.NET MVC 5 : Create Entity Data Model With Entity Framework

In our previous blogs we've created an ASP.NET MVC from scratch.  In this blog we are going to use Entity Framework as the ORM (Object Relational Mapping) as a conduit to our database, so that we can query our data as an object.  An ORM as the name implies maps database tables, views, and stored procedures as objects in a programming language so that developers can work with the data as objects.

Step-by-Step Instructions:

1.  First we need to add the Entity Framework 6.1.3 to our ASP.NET MVC, we accomplish by right-click on "References" then select "Manage NuGet Packages"