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

Since the Id column is auto generated we don't need to assign a value.  Now we are ready to retrieve the values in our code.

The first thing we want to do is inject the EntitiesContext that we created in the last post in the ValuesController constructor so that we could use it in the rest of the class. 

With the code below:

       private readonly EntitiesContext _ctx;
        public ValuesController(EntitiesContext ctx)
        {
            _ctx = ctx;
        }
 

Then we want to change the HttpGet method to retrieve all the values in the table using the Get() method with no parameters. With this code:

       // GET api/values
        [HttpGet]
        public IActionResult Get()
        {
            return Ok(_ctx.Values.ToList());
        }

_ctx is the EntitiesContext that we passed in and IActionResult return object tells Asp.Net Core that the method will return an Http Status Code along with the revelant data. It uses Linq to return the Vvalues entities in a IEnumberable list objet. The default return format is JSON so you don't have to do anything to show JSON to the client.

Now it's time to make another code change to get an individual record with an Id by changing the code of the second HttpGet method. The one that takes an Id as it's parameter.

So the route for this method is something like this http://localhost:5000/api/values/4 and the URL for the first method is http://localhost:5000/values

Change the code for the HttpGet method that takes an Id to this, to retrieve a single value from the database using Linq's FirstorDefault method

        // GET api/values/5
        [HttpGet("{id}")]
        public IActionResult Get(int id)
        {
            return Ok(_ctx.Values.FirstOrDefault(v => v.Id == id));
        }

The next step is to build the project by pressing Ctrl+Shift+B and selecting build on VSCode

Now open a terminal in the ACMEBank.API folder and type dotnet watch run, to run the application

Now launch Postman and click on the + sign and make a "Get" request by typing in the URL http://localhost:5000/api/values and you should get all the values in the database like the screenshot below.

Now add a /4 to the URL so the URL should be http://localhost:5000/api/values/4 and you should get Thanos back

Previous: Create Our Database In MySQL Using Asp.Net Core And EntityFrameworkCore