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 6: Running Your Asp.Net Core Application For The First Time

Before we run our Asp.Net Core application for the first time we are going to copy a controller from Asp.Net Core 2.2 to help us create the skeleton for our application.  A skeleton are the plumbings we do at the beginning of our application to make sure all the mechanisms work.  It's called the ValuesController which has been replaced with a more complicated WeatherForecastController.  The ValuesController is just a lot easier to work with to get things set up.

Just create a new controller in the "Controllers" and call it "ValuesController.cs" Here is the code for it.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;

namespace ACMEBank.API.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class ValuesController : ControllerBase
    {
        // GET api/values
        [HttpGet]
        public ActionResult> Get()
        {
            return new string[] { "value1", "value2" };
        }

        // GET api/values/5
        [HttpGet("{id}")]
        public ActionResult Get(int id)
        {
            return "value";
        }

        // POST api/values
        [HttpPost]
        public void Post([FromBody] string value)
        {
        }

        // PUT api/values/5
        [HttpPut("{id}")]
        public void Put(int id, [FromBody] string value)
        {
        }

        // DELETE api/values/5
        [HttpDelete("{id}")]
        public void Delete(int id)
        {
        }
    }
}

ACME Bank: Step 5: Setup Visual Code For C# Development With Extensions

 For those of you who works with Visual Studio, working with C# comes out of the box, but with Visual Studio Code you need to install some extensions from the Marketplace to make it suitable for C# development.  

Here are the extensions you need to install:

• C#: This extension by Microsoft gives us IntelliSense, references, code editing and highlighting and debugging support

ACMEBank: Step 4: Create An Asp.Net Core Web API With The Asp.Net Core CLI

 In this post we are going to use the .NET Core CLI to create a web api application for our app.  The first thing to do is create an a folder to hold our files.  Since we are using Linux you want to sudo su into the terminal and create the following folder /opt/app/ACMEBank.  Once that's done cd into it and you are ready to type in the command to create the Web Api.

Here are the steps:

1. What you want to do first is to make sure that you have .NET Core install by typing in dotnet --info, there should be information on your .NET Core install.  If you don't have .NET install you can follow the directions in this post.

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

ACME Bank: Step 2: Setting Up Development Environment With .NET Core, NodeJS , VSCode and Postman

In this post we going to get our development environment with NodeJS, VSCode and Postman

The Urls are the following, if you are developing in Linux you should install the Linux version.  All these tools are cross platform so you can develop in either Windows, Linux or MacOS:

.NET Core: https://dotnet.microsoft.com/download

Node.JS: https://nodejs.org/en/

Visual Studio Code: https://code.visualstudio.com/#alt-downloads

Postman: https://www.postman.com/downloads/

For .NET Core make sure you download the "recommended" version and not the preview version. Also with NodeJS, usually it has LTS status.

Installing .NET Core SDK and NodeJS On Fedora

In order to install .NET Core SDK on Fedora open the terminal and type in the following

sudo dnf install dotnet-sdk-3.1 -y

The -y tells Fedora to skip the y/N prompt and just go on with the install

Once the SDK is installed you can verify the install with the command dotnet --version

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 18 Asp.Net Core: Create A Web API For NorthwindCafe With Postman

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

https://www.getpostman.com/downloads/ 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
{
    [Route("api/[Controller]")]
    public class ProductController : Controller
    {
        private readonly  IProductRepository _repository;
        private readonly  ILogger _logger;

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

        [HttpGet]
        public IActionResult Get()
        {
            try
            {
                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.

Hour 17: ASP.NET Core Add log4Net Support To NorthwindCafe

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"

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.

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.

Hour 9: ASP.NET Core : Add Tag Helpers To NorthwindCafe Application

In this post we will add Tag Helpers support for our application.  Tag Helpers are programmable attributes that you can use throughout the application.

Follow the steps below to enable tag helpers:

1.  Right-click on the "Views" folder and create a "View Imports" file

Hour 8: ASP.NET Core : Adding Font-Awesome 5+ For Northwind Cafe Navigation Icons

In this tutorial we will add icons to your navbar.  In the previous tutorial we added a responsive layout with bootstrap.  In this post we will add some icons to your navigation.  Font-Awesome gives you professional looking vector graphics, which are implemented using CSS.

Hour 7: ASP.NET Core : Create A Responsive Layout With Bootstrap 4+

In our previous blog we created a simple _Layout.cshtml file that does not have any markup just to make things simple.  In this blog we will use Bootstrap to make the layout look more professional and responsive, so that it can be viewed in any screen size.  The previous layout looks like screenshot below.