.NET Core Web API Microservice with SQL Server Entity Framework Core

Hi. Welcome to CodingDroplets. We are going  to create a simple microservice solution which contains multiple microservices and each  micro services will use different databases like sql server, mysql, mongodb etc. In this video we'll  create the first microservice, an asp.net core web api microservice which uses sql server database  for saving the data. And in the upcoming videos we'll create other microservices and finally  we also create an api gateway which will route data to all these microservices. In this video  we'll also add docker orchestrator support for our project 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 let's get started. I have opened visual studio and let me  click on create a new project button to create the new microservice solution. Here I am  searching for a blank solution and selecting it. Then clicking on next. Let me provide a name for  the solution. Let it be demo microservice solution. Then clicking create button to create the solution.  Now in the solution explorer you can see a blank solution. Next I'm adding a new solution folder  by right clicking on the solution. Select add. Then choose new solution folder. We can name it as  microservices. Under that folder I'm creating a new project. Just right click on the folder and inside  add option, I am clicking on new project. Now let me search for api and you can see a project template  named asp.net core web api. So I am selecting that and clicking on next. Now let the project name be  customer web api. Then next. Here I am using dotnet 6 framework and we can uncheck the configure for  https checkbox. Also I am unchecking enable open api support. But you can check that if you need  to enable swagger. In this video I'll be using postman for testing the api methods. Let's click  on create to create the project. So now you can see the project in the solution explorer. Visual studio  will create weather forecast controller and weather forecast cs files for demo purpose. We don't need  those. So let me delete both the files. For sql server database read and write operations, we need  entity framework core libraries to be installed. So let's open nuget package manager and install  the needed libraries. Let me navigate to browse tab. First we need to install microsoft.entity frameworkcore.design. I'm installing it. Next we need microsoft.entityframework  core.sqlserver. Installing that as well. The last one is microsoft.  entityframeworkcore.tools. These three libraries are sufficient for  us to do the sql server database operations. Next we can create a model class for our  entity. Let me create a folder named models. Inside that folder, I am  creating a class named customer. In customer entity we need four properties. First one is customer id which is a unique id of the entity. Then customer name, mobile  number and email. For the entity class, we can use table attribute for mentioning the  table name and schema in the database. Next we can add the key attribute for customer  id to denote that it is the primary key. Also we can add the attribute, database generated  of database generated option dot identity to denote that the column generates the value  automatically upon insert. I'm also providing column attribute to mention a custom column  name. Let me provide the column attribute for other properties as well. I need the column  names to be as mentioned in the database. Customer underscore name, mobile underscore no and  email. Next we can add the dependency injection for sql server entity framework in program.cs  class. Currently I am hard coding dbhost, dbname and dbpassword values. We'll be changing  this while configuring the docker compose. Next I'm declaring a variable named connection  string which will use the above variables to construct the connection string. Finally adding  the dependency injection by providing builder dot services dot add db context of customer db context. Currently we don't have customer db context class. We'll be creating it now. So let me create  a new database context class in the project. I am naming it as customerdbcontext. This class  should be inherited from the dbcontext class which is a part of entity framework. Now I  am creating a constructor for this class and providing db context options as  the constructor parameter. It should also call the base class constructor to which  we can pass db context options as the parameter. Now inside the constructor I am providing a  try catch method. If any error occurs we'll write the error message in the console  window. Next I am creating a database creator object which I need to use for  creating the database if it doesn't exist. So we are just checking whether the database  creator object is not null. If it is not null we are creating a database if the database  creator object cannot connect to the database. Next we are creating tables if the  database is not having any tables in it. That's it needed in the constructor. Next  we are declaring dbset for customer entity. Let's name it as customers. Now we can use this customer db context class in our dependency injection. Next let's  create an api controller in our project. I am choosing an empty api controller  template. I am naming it as customer controller. First let me create a constructor for this  api controller class and we can receive the customer db context as a parameter as we  have done the dependency injection. So I'm creating a private read-only customer db context  object and assigning the received object to it. Next we can create a method  for fetching the customer data. It is a http get method. In this  method we just need to return the customerdbcontext.customers. So  that will return all the customers. I am creating one more http get method which  accepts the customer id in the url. This method should return details of the customer with that id. So let's fetch the customer by filtering with the customer id. We can use find async method for that. Now we can just return the fetched customer object. Next is a http post method for creating a  new customer. This method accepts customer data as a parameter. We can send the customer  as the request body while calling this method. I am saving the customer data using add async  method of the database context. Also we need to provide database context dot save changes for  saving it. Then just I'm returning an ok response. Next is http put method to update an existing  customer record. This method also accepts the customer object as a parameter. We can just  call the update method of the database context to update it. After that saving changes as we did  before. This method also returns an ok response. The final method is http delete  method to delete a customer. This method accepts customer id in the url. First  let's fetch the customer with the received id. Then I have provided the remove method of the  database context to delete it from the database. Finally saving the changes and returning an ok  response. So now our api controller is completed. Now let's test this api using postman. Just  before that I am opening sql management studio. Here you can see I don't have any databases  in this machine. Now let me run the application. So it is running now. You can see  the application is listening on port number 5284. Let me copy the base url.  Now we can test the api methods using postman. First let me create a new collection in postman  and naming it as demo microservice solution. Under that collection I am creating a new folder  and naming it as customer web api. In that folder we can add a request. Now let me paste the copied  base url then providing the path of the customer api controller. In the project you can see the  route attribute in which the path is mentioned. The controller decorated with square brackets will  get replaced with the controller name. So this is a http get method and this should execute our get  customers method in the api controller. You can see that we have received a 200 okay response.  That means the api execution is successful. There are no customers in our database and that is why  we cannot see any data. Now let me open the sql management studio and just refresh the database  folder. You can see now the database got created. Also it created a table named customers in the  database. The table is not having any records in it. So back to postman and I am saving this request. So that we can use the same request to test later. Let me rename it to get customers. Now let's  create another request and providing the same url. This time we can choose http post method  to create customers. For post request we should send the customer data as the request  body. We can provide the data in json format. So first property is customer name and let the  customer name be john. Second property is mobile number. I'm providing a dummy mobile number. Third property is email and let the value be [e-mail]. Now let me click on the send  button and you can see we have received 200 okay response here. Let's check the customer data  in sql management studio as well. The data has been saved successfully in the database. Let me create  one more customer by providing a different name, mobile number and email. We have received a success  response. Let me also check in the sql management studio. We can see the record in the database. Let  me save this request as well in postman and naming it as create. Now creating another request for get  by id. For get by id we must use http get method. But we must provide the customer id along with  the url. I am fetching the customer data with id 1. Now it has listed only one customer record  which is having the provided customer id. Let's change the id to 2 and now you can see  it has displayed the second customer data. Let me save this request as well in postman with  the name get by id. Now creating another request and changing the method type to put. The request is  used for updating the record. We must provide the customer data to be updated as the request body as  we did in the post request. In the same way we can provide the customer data in json format. Let me  copy the json content from the post request which we have created before. But this time we also must  provide the customer id so that it will update the customer record that matches the particular  customer id. So i'm updating the customer with id 2. Let me make some changes in the name and email. Let the mobile number be the same. We got 200 ok response. Let's check in sql management studio. Now  you can see that instead of adding a new record it has updated the record with the provided id. So  let me save the request and naming it as update. Now creating the final request to delete  the record. The method should be delete. We must provide the customer id along with the  url for deleting the record. Let's delete the record with id 1. The request is successful. Let's check in sql management studio and you can see the record got deleted successfully. So  saving this request as well with the name delete. Next we are going to add container orchestrator  support for this project. We also include sql server database as a separate container. For adding container orchestrator support, just right click on the project. Then go to add  option and choose container orchestrator support. I am using docker compose as the container  orchestrator. Then choosing linux as a container operating system. Now visual studio created a  new docker file inside our project and a docker compose project in the solution. Visual studio  will start pulling the needed docker images. Let it run in the background. We can just check the newly  created docker file. There are several instructions to create the docker image of our project. You  can see that there is a docker instruction to expose port number 80. So our application will  be listening for port 80 inside the container. Now let's open the docker compose yaml file. We are  going to modify this file as per our need. We also need to include sql server service in the docker  compose yaml file. First let me create a network under networks section. Let the network name be  backend. We'll assign this network for both the api and the database containers. So for the api let me  assign backend network by mentioning it in networks. Next let's create a new service named customerdb. This is for sql server. First I am providing a custom container name. Let it be customer-db. Then providing the image. In the last video we have already seen about sql server image in  docker hub. I am using the same image. Next we can provide the environment variables - accept end user license and system admin password. I am assigning the same backend network for this  container as well. We can also provide a custom container name for the api container. So that it  will be easier to identify. Now I am assigning some environment variables which we are going to use  for constructing the database connection string. First one is db_host. Here we can  provide the database container customerdb. Then db_name. Let it be customer. Then db_sa_password for assigning the system admin password. We can copy the same password from the db  environment variable. Now we can modify the connection string to read values from this  environment variables. So to read values from environment variables we can use get environment  variable method. We must pass the variable name as a parameter. First let me copy this and modify  db name and db password. Now let's provide the environment variable names. db_host  for db host. Then db_name for db name and db_sa_password for  db password. Next we need to map port numbers for this services. For the database I am assigning  8001 for 1433. 1433 is the default port number for  sql server. For the api I am assigning 8002 for port 80. We have already seen in the  docker file that the container will expose port number 80. Fine. That's it. Let me build the docker  compose project and confirm there are no errors. So the build got succeeded. Now we can try  debugging the docker compose from visual studio itself. It is creating the needed containers  some existing containers are removed and recreated. In the container window we can see the assigned  environment variables for both the containers. In the api container we can see the db host, db  name and password. I'll show you the containers in docker desktop as well. You can see both  containers with the assigned port numbers. Now let me connect sql management studio to  the sql server in the newly created container. The server name should contain port number as  well as we are not using the default port number. So I am providing localhost,8001. Then the sa password. We can copy it from the docker compose file and I got  logged in. Now there are no databases here. So let's try to call an api from postman first. Let's create a new customer. We need to change the port number to 8002 and let the customer  data be the same. We got a 200 ok response. Let's check in the database now. Just refreshing  it and you can see the database got created. Let's open the table and check now. You can see the  customer record. So everything is working perfectly. Let me also execute the get customers request by changing the port number. It is returning the customer records. So now we have created our  first simple microservice. We'll be creating another microservice with a different database  in our upcoming video and finally we'll create an api gateway to communicate with all the  microservices. 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: 24,713

Keywords: entity framework, .net 5 web api, entity framework core tutorial, asp.net core web api tutorial for beginners, asp.net core api, asp.net core web api code first, asp.net core web api tutorial nitish, asp.net core web api docker, .net core web api docker, dotnet core web api docker, .net core api docker, dotnet core api docker, .net core api microservice, dotnet core api microservice, .net core web api microservice, sql server entity framework core, ef core .net web api

Id: 2p01iafOxUw

Length: 19min 5sec (1145 seconds)

Published: Wed May 18 2022