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

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.