Net 5 Web API CRUD using Entity Framework and PostgreSQL

hi guys today we are going to build a web api using net 5 entity framework and postgresql by the end of this video you will have this web api where you can add edit delete and get the products and before we start you need a specific software to write our code first we need an ide to write our code and in this tutorial we will be using visual studio code so you can download it from their website code.visualstudio.com and we also need the net 5 sdk so we can write our code for that net 5 and lastly this is an optional docker because we are going to save our data in postgres sql and instead of installing the postgres sql in my machine i prefer to use docker then host the postgres sql and pg admin in there so you can either use docker for your postgres sql just like what i'm going to do in this tutorial or you can just install pg admin and postgresql in your machine so the choice is up to you here in visual studio code we need to install some extension so we can write a c-sharp code so go to extension so currently i already installed it it's called c sharp and then c sharp extensions if it doesn't have this yet you can just go and search here type c sharp and then it will appear here c sharp and c sharp extensions so after installing the needed extensions go to terminal new terminal and then here uh let's navigate to the folder where we want to put our project so for me it will be in the documents and then in the documents i will create a folder called projects and then i will go inside the projects folder then here we will create our project and we can do that by typing that net new web api dash n and then the name of our web api i will call this products api so when you see it said restore succeeded then we can now go to file open folder and then let's open the folder where we put our project so here products api click select folder and then wait for a little while until this thing appears in your obs code it said the required assets to build and debug are missing from products api add them click yes and then after clicking that go to this triangle thing you can see this play button click this it's running our application and then when you get to this page just go to slash swagger and then if you see this page then that means your project is running so you can test this api so just click this try it out and then execute then you you will get this data so where this data came from it came from here in the controller we have this weather forecast controller and it already has this data which we are seeing here so we know this can run so now uh let's uh add a ml file here for our docker to host our postgres and pgadmin so a new file and then let's call this docker compose that yml so here we need uh the version docker which is 3.4 then we will list the services in this docker image or in this container so the first one is the postgres sql database so the image that i need for that is of course the postgres and then we need the latest version then the environment setting for our postgres then the port mapping so we the postgres have a default uh port 45.32 then we will also map it to our port by port 32 of our localhost and then it should always restart then the volumes where we will going to mount our image it is in the database dash data of our now we also need a services or a service for our pg admin so let's just name this pg admin and then the image that we need is the pg admin then the environment setting for our pg admin then the ports the pg admin runs in port 80 but let's map it to our port 50 50 50. and then it should also restart always and then the volumes where we're going to mount this image it should mount to pg admin okay so let's create the volumes and then the pg admin so this is our yaml file for our docker container that contains the postgresql and then the pg admin so we can go back to terminal then we can just type the docker compose then up and then let's add this detach so it's docker compose up it give us this error environment so we mistype something it should be environment okay save it and then run it again okay once it said done you can open your docker dashboard and then you can see here it is running so just click in this triangle and then you can see the pg admin click on this open in browser so it's uh it said that this page isn't working so it's probably still uh trying to boot up the image let's close this and go back to docker again and then click it again open in browser and then now we have this i login so for the for this user and password we can go back here in our docker file or yaml file and then copy the default email that we put and then the password and then for the servers we need to connect to our postgrestb so create server and let's call this productsnb and then for the connection the host will be this postgres sql database then the port is pipe.32 and then the name of our database is productstv then the username is admin password is admin1234 uh the settings here is same as the one that we set here so we can just click save now and then if you look at the database we now have this productsdb okay so we can now we now have our database so we can now back to our code and start coding we are going to use entity framework to connect to postgres sql so we need to install that so that net add package and then the package for postgres npg sql dot entity framework core that postgres postgresql then the current version is the version 5.0 0.2 then hit enter then we also need to install the net tool so we can create migrations i already installed mine so i probably got an error when i tried to reinstall it again but it's okay just to show you it's um so this is it that net tool install and then global.net ef so the next thing that we need to install here is the is the entity framework design so let's do that here that net add package microsoft dot entity framework core that design and the current version is the 5.0.5 now after installing it you can verify if you got the packages here in our solution versus this one you can see we have here in the package reference we have the np gsql and it framework postgresql as well as here the entity framework core that design now let's add models for our web api so here at the new folder let's call this models let me change this to capital m then inside the models let's add a new class let's call this product and then here in the product we just have a basic property public in product id and then we have a name which is a string and then get set and then another one decimal price and then date time or date created so let's resolve this reference using system okay now after creating our model let's add a new folder let's call this data and then first we need to create an interface for our data context so right click new c-sharp interface let's call this i data context and then here we will just have our db set for our product which we will call it products let's resolve this and then another one okay then let's also add our save changes okay now we have our i data context let's add our actual context let's call this data context this one should inherit the db context and also the i data context that we just created earlier so we need here the db context options then the data context options and then pass it to the base class options and then also we need to add our db set for our product okay we are now done in our model and data context it's now time to add our connection string in upsettings.json so here we need to add connection strings then let's add the default connection so for the default connection we have our host equals to localhost then the port will be the pipe.32 the database name is product db let's just remove the spaces productsdb and then username equals to admin password is admin1234 now we need to go to our startup.cs file and update the configure services to add our data contacts then here we need to add and configure our data context so to do that we need to type services that add db context and for the db context our db context is data context that we just created earlier so let's resolve this let's add using products api.data and then we need to pass and options for the options it should be the options that use np gsql so let's add the entity framework core here and then the connection stream it should be the get uh no not yet it should be configuration that get connection string and then the name of the connection string we call it uh default connection if you are wondering why this is default connection you can go back here in app settings.json and then just get this whatever name you put in here go to startup class and then just paste it here then we also need to add a dependency injection for idata context so to do that it should be services that add scope so it's it is i data context and then the instance will be provider provider that get service data context okay we are done setting up our model and data context we can now create our migration so to do that just type that net ef migrations add initial migration after it completed you should now have this migrations folder so let's try to update our database.net ef database update now when it's said done we can go back to our pg admin open our productsdb and then schemas tables yeah you can see we have now these products we have this two table products and ear migrations so we already set up our model our data context and created the tables in our database now it's time to create our repository that will get the data and also post and also save the data in our database so here let's add a new folder let's call this repositories and then the repositories let's add a new interface i will call this i product repository we should have methods here for get get all create update delete so the first thing that we will uh do is the get so the get will return a single product when a user or when we pass an id of the product it will return a single product so this is our get then we have our get all for the ad we are not going to return anything just saving the product same with the delete and update now let's implement our product repository add a new class here prada repository this should implement the i product repository okay then here in the constructor let's inject the i data context then instead of using this i prefer to use the underscore okay now let's implement our repositories so for the add what we're going to do is context that products that add product and then we just going to await and call context that save changes async then this one should be async same goes with the other methods okay now for the delete let's uh look for item to delete so if there is no item to delete what we're going to do is we will draw another reference exception new null reference exception then if we find an item to delete what we're going to do is we will remove it to products and then we will save changes this should be await then now for our get we'll just do the same return oh wait we we will just use the context that products that find a scene then the id for get all return await context that products that to list async and then for our update we will do the same thing that we did in the delete so item to update equals to await context that products that find us in product that id product id and then if we don't find an item to update we will just throw another reference exception otherwise we will update the item so item to update that name equals to product that name and then item to update that price equals the product that price so this is the only thing that we're going to update we're not going to update the id and date created so we can just save this now and now let's add our product repository to our startup file configuration so let's add services that add scope i product repository and product repository okay then let's go back to let's go to controllers folder let's add a new controller here called products controller this should inherit the controller base and then this is an api controller and then the route should be route controller so here we will need our i product repository let's add an underscore here and then let's remove this this dot and change it with underscore then let's create our actions one by one so for the get let's create public async task action result and then this will return a product then let's call this get product we'll accept an id for the product id this should be an http get that will accept an id so bar product equals to await product repository that get let's pass the id here so if if uh product equals question let's return a bad request or maybe not bound otherwise let's return an okay and then include the product here okay then we have some missing reference here let's fix that then let's do our http get for get all bar products equals to await product story that get all then let's return it okay and then products now we need the http post to create our product so public sync does action result create product and then here we're not going to pass the actual product here let's create a dto so add new folder let's call this dtos and then let's create a new class here let's call it create product dto and then for discrete product detail we will have a string name and then also a decimal for the price so that's the only thing we need here so let's go back to our controller so we need the create product dto let's call discrete product dto let's create a product entity here bar product equals to new product then we will pass the name equals to create product dto that name then the price was create product dto that price and then date created equals to date time that now and then we will pass this to our repository the add product and then let's just return okay so this will work i just want to show you something new in c sharp nine so if we put product here we don't need to type product here we can just do new and then the this uh open and close parenthesis and this will work uh this is something new in c sharp name so you can uh create an instance of a product using bar product was to new product or you can also use this it depends on your preference whatever you want to use it's fine so now let's create our action for delete delete product product repository dot delete then return ok then the last one is the http put to update our product now we should also add this one here in our delete then let's create our method or action thus action result let's call this update product so we have in id and then again uh let's create a dto here where's our dto folder this one new class let's call this update product dto and then we will only have public stream name and also the price those are the only properties that can be updated in our data for our product data so back here we have the product dto and then here just like what we did earlier in the great product we will we will create an instance of a product name equals to update product that name and then price equals to update product dto that price and then we will pass this to our repository then return okay up to this point we created our model we created our data context configured it in our startup file add the connection string to appsettings.json we created our repository and also configure it in our startup file and we created our controller so now our api is ready to test let's go here and then click this start debugging button so we have an error in our controller let's go back here let's take a look okay so here in the controller we forgot to close this bracket let's close that save it and then back to this so this is uh the advantage of having this start debugging then rather than uh [Music] doing this i will show you the other one another way of running it we can use that run but if we do this we miss the picture for debugging so it's better to run it like this so let's go to slash swagger we should now be able to see our products here so let's try the post try it out so let's call this product one and then for the price is 10 execute okay so it was created so let's uh get all products try it out and then execute so we get our product it's hub of product id 10 let's create another one product 2 and then let's say the price is 30 execute so when we click the execute product we should get two products yeah we got two products and then let's try the get try it out and then let's give the idea of one okay we got it then update we have we let's try it out and then one then the name of the product or let's update the product to let's change the name to hello hello product and then the price is 20 execute so we got an error so we got a null reference error so why is that let's go back here okay because here we need to include the product id equals to id so let's run our application again so once more let's run this update so it succeeded let's uh try to get all the products again so now it said hello product and the price is 20. let's uh try the delete let's delete the product with id1 execute okay it said uh 200 so it's okay get all products we should only get one product now okay so our crude application is working so i think this is the end of this video so thanks for watching guys see you next time bye

Channel: alexcodetuts

Views: 11,350

Keywords:

Id: gUKWkBGrwwQ

Length: 44min 30sec (2670 seconds)

Published: Mon Apr 12 2021