.Net Core MySQL Microservice - Entity Framework Core MySQL

Hi. Welcome to CodingDroplets. In this video  we are going to create a simple asp.net web api microservice which uses mysql database to  save the data. We are currently developing a demo microservice solution in which there are multiple  micro services which uses different databases. So first let me give you an overview of the  solution which we are creating. We have already created the customer service which uses sql server  database. This service is only having the customer module in it. Now in this video we are developing  the second service named product service which uses mysql database. So this microservice will only  be having the product module in it. You can also include sub modules related to product module like  product category, product brands, manufacturer etc. But for demo purpose I am only creating product  module. Now in the next video, we will create another micro service named order service which  uses mongodb database. That module will be handling the orders of different customers which includes  the products they ordered, the quantity of each products in the order etc. After that we'll create  an api gateway which will route the api requests to these three services. Then finally we'll create  a web application which only communicate with the api gateway. From the application we'll be able  to create customers, products and orders. We'll be able to do the CRUD operations in all the  modules from the web application. CRUD means create, read, update and delete. In this video we'll  also add container orchestrator support for the new service which we are going to create  and configure the docker compose yaml file. So before starting you should know few things. This video is part of a series named microservice architecture. You can find the playlist link in  the video description to watch the other videos in this series. So now let's start developing  the dotnet core web api with mysql database. I have opened the demo microservice solution  which we have created in our last video. In the solution explorer you can see the solution  folder named microservices and inside that we have the customer web api. Also we have the docker  compose project which will get created while adding the docker orchestrator support. Now inside  microservice folder I am creating a new project. Let me select asp.net core web api project  template and click on next button. Let the project name be product web api. Then clicking  next button again. As done in the previous video I am choosing dotnet 6 framework. Then  unchecking the configure for https checkbox. Also unchecking the open api support. You can  enable this if you need swagger. Now the project got created. Let's remove weather forecast  controller and weather forecast cs files. Those are the demo files created  by visual studio. We don't need them. For database read-write operations, I am  making use of entity framework core library. So let me install that from nuget package manager.  Navigating to browse tab and searching for 'MySql.EntityFrameworkCore'. The library  is listed in the first place. Let me install it. It is installed now. We can see the library now  in the installed tab. Next we can create the model class for our entity. So let me create a folder  named models inside the project. Now let me add a new class inside the models folder and let me  name it as product. The first property we need in the product entity is the product id which is the  unique id for the entity. The data type is integer. Next property is product name which is string data  type. Then we can also include product code. Let it be also string data type. Finally product price  which is of decimal data type. Next we can add some attributes that we can use in entity framework. Let me add table attribute for the product class. We can provide a custom table name along with this  attribute. So the table name will be product in lowercase. I'm also providing key attribute for the  product id to denote that it is the primary key. Also I'm providing a custom column name for  product id. Let me also provide custom column names for other properties as well. product_name,  product_code and product_price. Completely in lower  case. Next let's create the database context class. Let me name it as 'ProductDbContext'. This  class should be inherited from dbcontext class of entity framework. Now I am creating  a constructor for this class. We can provide db context options as the constructor parameter  and call the base class constructor by passing the db context options object to it. Inside the  constructor I'm creating a try-catch method. If any error occurs, I'm just writing the error  message in the console window. Now inside the try method, let me create an object for database creator.  Then just verifying the database creator object is not null. Now using this object I am creating the  database if it doesn't exist. Then creating tables if there are no tables in the database. Next we  can declare the db set for the product entity. Let the name be products. So now the database  context class is ready. Next we can add the dependency injection. Let me open program.cs from  the solution explorer. Here we are going to add the database context dependency injection. Initially  I'm hard coding some values. We'll change it while configuring the docker compose. Let the db host be  local host. Then db name be 'dms_product'. Also let me hard code the db password. Now let me add the dependency injection. 'Builder.Services.AddDbContext' of product  db context. Then 'o' such that o.UseMySQL. We need to pass the connection string as a parameter  here. Let's create a connection string using the hard-coded values in it. Now we can provide this  connection string as a parameter for 'UseMySQL' method. Next we can create an api controller inside  the controllers folder. Choosing api from the left side menu and selecting empty api controller  template. Let's name it as product controller. First let me create a constructor for this class  and providing product db context as a parameter. As we have done the dependency injection  we can receive the productdb context object as a parameter here. Let me also declare a  private read-only product db context object and assign the received object to it. Now we can create the api methods. First let me create http-get method with the name get  products which returns all the products. Inside the method we can just return dbcontext.products. It will return all the products in our database table. Next method is http-get method which accepts the  product id along with the url. The method name is get by id. This method is for returning the product  details of the product with the provided id. In the method we can fetch the product using find async  method and pass the product id as the parameter. Then we can just return the product object. Next  is a http-post method for creating a new product. This method accepts the product object as a  parameter. We'll pass the product details as the request body in json format. I am using  the add async method to add the product. Also we need to save the changes after adding  the product. Then just returning an ok response. Next is http-put method to update an existing  product. We can use the update method for updating a record. Here also we must save changes after  updating it. Then just returning an ok response. The final method is http delete for deleting  the products. This method also accepts product id along with the url. In the method first we  can fetch the product using the provided id. Then calling the remove method to remove the  product from the database. Then saving the changes and returning an ok response. So that's it needed  in the api controller. Now we have the methods to create, read, update and delete. Now we can test  the api methods using postman. So let me set the new project as the startup project and run it. Now the application is running and you can see that it is listening on port 5197. Let me copy the base url. Just before testing with postman, I am opening MySQL workbench which is a visual  database designing or graphical user interface tool for MySql. Now there are no databases  listed in the schemas section. So let's open postman and test the api methods. On the left side  pane named my workspace is listing the api methods which we have created in the last video for  customer microservice. Let me create a new folder under demo micro service solution and naming it  as product web api. Now let's add a new request and changing the http method to post. The  post method is used to create new products I am pasting the base url here and providing  the complete url of the controller. In our controller the route attribute is for mentioning  the controller url. It is api/controller name. For the post request, we must pass the product  details as the request body in json format. Let the product name be product 001. Then product code  be b1. Finally the value for product price property. Let it be hundred. Now let's send the request  and you can see we received a 200 ok response. That means the api execution is successful. Let's  open mysql workbench again and check now. I'm just clicking on the refresh button and you can see  the database got created now. Inside the database we have our table named product. Let's list out  the table rows and we can see the product details. Let's create one more product by changing the  values in our api request body. Again we got successful response. Let's have a look in mysql  workbench. so the post request is working fine. Let me save this request in postman so that we can  use it later for testing let me name it as create. Now adding another request. Let the method be http-get. Providing the complete url of the  api controller. Now this should execute the get products method and list all the  products in our database. Let's try it. We can see both the products which we have added, in json format. So let me save this request as well with the name 'GetProducts'. Let's create the  next request for fetching the product details by filtering with the product id. This method  is again http-get. But we should also provide the product id along with the url. Now you  can see it has only displayed the product details which is having a product id of 1.  Let me change the product id to 2 in the url and now it is showing the second product. Saving  this request as well with the name get by id. Now another request to update the product details. So the method should be http-put. Then providing the url. For this method also we must provide the  product details in the request body. Let me copy the request body from the post request. But here we  also need to provide the product id. It will update the product which matches the provided product  id. Let's make some changes in the product details. Now clicking on the send button and  we received the successful response. Let's check in mysql workbench. I'm just  executing the select query again and you can see the product details got updated. Let  me save this request with the name update. Now the final request for deleting the product. The  method should be http-delete. For this request also we must provide the product id along with the  url. The product id of the product to be deleted. I'm providing product id 1. Now let's see whether  it is deleted or not. It got deleted successfully. So all the methods are working fine. Let me save  this request with the name delete. Next we are going to add container orchestrator support for  this project as well. Just before adding that let's open current docker compose yaml file. We  have done this configuration for customer micro service in our last video. To add container  orchestrator support, we can right click on the project. Then go to add option. There  we have container orchestrator support. Selecting docker compose as container orchestrator  and linux as the container operating system. Now a new docker file got added in our  project. But this time instead of creating a new docker compose project, visual studio  added the product api container details in our existing docker compose yaml file. You can  see a new service named product web api. Now let's configure the docker compose yaml file as we did  before. We are going to add mysql container as well. First let me provide a container name for the  product api container. Then adding the backend network which we have created in our last video. This container will be also under the same network. Next let me assign a port number. I am providing  8004 for port 80. The application will be listening on port 80 in the container. Now we can provide  some environment variables for constructing the connection string as we did for sql server. The  db host is productdb. We'll create a new service for mysql with the same name. Then providing  db name let it be dms_product. Then the password. You can assign a different  password if you need. But let it be the same for this demo. I am also changing the db name of  the previous customer database. Let it be dms_customer. Now we can add a new service  for mysql. So providing the same name what we have given in db host environment variable. Before  providing the other details, let's open mysql official image documentation in docker hub. I am  searching for mysql and here we have the official image. Let's open it. These are the supported  tags of different versions and operating systems. Also we can assign the root password by  providing the environment variable named MYSQL_ROOT_PASSWORD. We  can use the first tag. Let me open docker file in github by just clicking on it. Here you  can see the container will expose port 3306 and 33060. So we can assign port mapping for 3306. That is the default port for mysql connectivity. Back to our docker compose yaml file. First  let's provide a container name. Then the image. The image name is mysql. We can copy the  tag from docker hub documentation Next we can provide the environment variable for  assigning the root password. Then map port 8003 for 3306 which is the default port for mysql. Now  we can assign the backend network. This container also should be under the same network. Now let's  modify the db context dependency injection section to use the values in the connection  string from the environment variables. So I am using the 'Environment.GetEnvironmentVariable' method. Let me copy this and modify db name and password as well. Now we can provide the  environment variable names. db_host for db host. Then db_name for db name. Finally  the password. Here I am changing the name to DB_ROOT_PASSWORD. In my sql  root is the user with complete permissions. So that's it. Now let me build the docker compose  project to ensure that there are no errors. Build got succeeded. So let's make the docker compose  project as the startup project and run it. Now visual studio is preparing the containers to debug. Now you can see there are four containers running. Customer api, customer db, product api and product  db. You can see the containers in docker desktop as well. But the product api is using 60065 instead  of 8004. I think 8004 is getting used by some other application in my machine. That's not a problem. We  can test the product api by providing 60065 port number. Now in mysql workbench, let me connect to  the container mysql service. Let the connection name be product db. Port number is 8003. Let me test  the connection and it is asking for the password. We can just copy it from the docker compose yaml file. The connection is successful. So let me open it now. In schemas section, we don't have any database. Now let's create a product using postman. We need to change the port number to 60065. Got 200 ok response. Now in mysql workbench, we can see the database. The product  successfully saved in mysql container database. Let's also test the get products api  method by just changing the port number. That is also working fine. So now we  have created our second micro service. We'll be creating one more microservice with  a mongodb database in our upcoming video. After that we'll create an api gateway to communicate  with all these microservices and finally a web application that perform the CRUD operations. So that's it for this video. Hope you liked it. Please subscribe, like and share this video.  see you all in the next video. Thank You!

Channel: Coding Droplets

Views: 8,965

Keywords: .net core mysql microservice, asp.net core web api with mysql, .net core web api mysql, .net mysql docker, asp.net core mysql docker, mysql microservice, .net web api mysql, mysql entity framework core, mysql ef core, .net core microservice, .net core docker, .net core 6 docker, docker microservices .net core, .net core docker web server, docker .net core microservices, docker for .net core, .net core 6 mysql, mysql with .net core 3, entity framework core mysql

Id: b1BSu0Wb2Rw

Length: 19min 10sec (1150 seconds)

Published: Wed May 25 2022