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

An Ambitious Journey: Angular, Asp.Net Core Meets AWS In The Real World

 In the next month or so I will start a series of blog posts that will take you from scratch on how to deploy an Angular application that makes API calls from an Asp.Net Core web api backend using Entity Framework Core as it's ORM and MySQL as the database.  Most tutorials would end there, but I will take it further and deploy the application on AWS so that it lives on the world wide web.  Since I don't want to start from scratch I will be modernizing the ACME bank application that I've started with AngularJS.  If you are working with AngularJS you know that it's time to update and upgrade because it's at its' last legs and Angular is the future.  Some say the journey is more rewarding than the destination.  I hope, you will join me on this journey.  Thank you, here is a diagram of what's to come

Hour 16: ASP.NET Core Display Products

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

Hour 15 AspNet.Core Use Repository Pattern To Show Poducts

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();
    }
}

Hour 14 ASP.NET Core: Seeding The NorwindCafe Database

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)
        {
            context.Database.EnsureCreated();

            if(context.Categories.Any())
            {
                return;
            }

            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)
            {
                context.Categories.Add(c);

            }

            context.SaveChanges();
        }
    }
}

Hour 13 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;

Hour 12 ASP.NET Core : Create The NothwindContext ( EntityFrameworkCore )

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

Hour 11 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 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.

ASP.NET Core: Create The Category Repository

In this post we will create the Category Repository to retrieve information from the database about the the different categories in the NorthwindCafe database.

Here are the steps to create the Category repository class in the NorthwindCafe application:

1.  Create the ICategoryRepository interface for dependency injection, create a file call ICategoryRepository.cs in the "Models" folder with the following code

using System.Collections.Generic;

namespace NorthwindCafe.Web.Models
{
    public interface ICategoryRepository
    {
        IEnumerable GetAllCategories();
        Category GetCategory(int Id);

    }
}

ASP.NET Core: Seeding The NorwindCafe Database

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 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)
        {
            context.Database.EnsureCreated();

            if(context.Categories.Any())
            {
                return;
            }

            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)
            {
                context.Categories.Add(c);

            }

            context.SaveChanges();
        }
    }
}

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 : Create The NothwindContext ( EntityFrameworkCore )

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

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.