MySQL + EF Core + ASP.NET Core Web API + CRUD + Load Related Data + Views + Stored Procedures

hello everyone in this video i'm going to talk about how you can perform mysql database operations using entity framework core and how you can use hp.net core web api with it to show your data in json format for this demo i'm going to use norton database we are going to use a script which will generate this database i'll share that script in a moment here but when we run that script it generates the tables and data for these tables it also generates a view that you're going to call from our web api and it also generates a stored procedure that we're going to call from our web api we are also going to see how you can load related data and show the data in json format in this web api so let's take a look at the script for that i'm going to go to my github repo which is ef code a database all databases considered here i have my sequel folder i'm going to open that and in that i have this database folder in which i have this northwind sql script which generates the tables and the data for my database so let's go ahead and run the script and see if it does that or not but then i'm gonna go to my sql workbench and here i'm gonna create a schema and i'm gonna create a schema for northwind and apply that and then i'm gonna select this northwind database so that the script that i'm running will get run for this database and then i'm gonna open the script that i was just talking about and i'm going to execute that script once i execute that then you can see that now we have tables i'm going to refresh my northwind schema now we have tables and we also have data for these tables we also have a view that we're going to call from our web api and we also have a stored procedure that we're going to try and call from our web appear so now let's go ahead and scaffold this database and [Music] call crowd sort procedures and views from our web app but then i'm gonna open my visual studio 2022 and create a new project and here i'm gonna select web api and i'm gonna select hb.net code web if you have a c-sharp and click on next and i'm gonna share this project with you on my github account it'll be in this mysql folder so if you go to efcor all databases considered you'll find that project here i'll push the project once we're done with the demo and it also has steps that we're going to follow i'll talk about that in a minute so the name that i'm going to give for this project is going to be northwind mysql because we are dealing with mysql database and then i'm going to click on next here i'm going to select framework as dot net 6 because this is the latest as of now we are not going to select any authentication we are going to configure our api with https i'm not gonna dive into docker or what open api is but we are going to create a controller which is created for feather forecast so i'm gonna click on create this will create a asp.net core web api project which returns weather forecasts for the next five days and it's a random weather forecast we'll run this project and we'll see if it's running or not for that i'm going to click on this control f5 button display button which will open our browser and it will show the api which is running for this project template so it opens swagger swagger comes out of the box with asp.net core web api project which makes it easier to call these web apps you can see this we have this one web appear which is weather forecast if i click on get it's going to return weather forecast for the next five days and it's a random weather forecast so let's go ahead and see how the project is set up and then we'll try to scaffold our database so in this project we have this program.cs program.css entry point for any docnet project you can see that here we are creating a builder we're using web application to create a builder and then there is some code to add services into our our builder in our application and you can see that swagger is one of the services that we just used and then we are using builder.build to build the application and then we are using this app to configure our app and run that app so this is the program.cs we are going to add some services here to make it work for our database we also have weather forecast this is just a model it shows date temperature in celsius and summary and temperature in fahrenheit and we have a controller which gets the weather forecast for the next five days and it is random so we are going to use this web api project to return the data in json format for that first thing that i'm going to have to do is add connection string in my project so if you go to my repository here again there are some steps that i've added you can open this text file there's some steps that are followed followed to make it happen and you can use the steps too i'm going to also [Music] put these steps in video description so that it's easier for you guys to copy to the first step is to add connection string so i'm gonna add the connection string for northwind database the server is localhost database name is northwind user id is root and the password is root if you're using a sensitive application please don't use user id as root and root make sure that you're using uh a strong username and password but for this demo i'm gonna use root and root i'm going to copy this connection string and let's go ahead and add that in our project for that i'm going to open my app settings this is where you can keep your connection string and here i'm going to add a connection string and i'm going to call it as north wind northwind db and i'm going to pass that connection string here now we are going to use this connection string to scaffold our database but before we scaffold we are going to need to add some packages in our project so let's go ahead and do that for that i'm going to right click on my project and open terminal and you can add packages using nuget package manager and the commands that i'm gonna run you can also do that from new kid package manager console but i like to use terminal where you can use nuget package manager console if you like so the first command that i'm going to run is to add some packages the packages are going to be these two packages the first package is microsoft entity framework code design and the second package is pomelo and entity framework core mysql so these are the two packages i would like to add so one of them is a provider and one of them is going to help a scaffold and we are going to use this packages so that we can scaffold and connect to our database so i'm going to copy these two commands and then i'm going to come to my terminal here and i'm going to paste those commands this these commands will add these packages for my project so if i go to my project now you can see that these two packages are added into my project right now we are going to use a tool that is going to help us scaffold the database so if i go to my steps here again you can see this there is a tool that i'm installing this is dot net tool install global tool which is dot net ef so if you do not have this tool you can use this command to install that tool i'm going to run this command to show you that i already have this tool if i run this command then you can see that it's showing that dotnet ef is already installed if you want to update your tool if you already have it make sure that you're updating your tools so you can run this command which is dotnet tool update global.net here and once you run this then this will update your tool i already have the latest version of this tool and now we are going to use this story to scaffold the database and when i say scaffold that means scaffolding will generate models and db context which will be present your database so models will represent your database tables and context will map those models with your database table so let's go ahead and run that command that's the heartbeat of this demo so this is the big command that we're going to run which is going to scaffold the database so the command goes like this it says dotnet ef ef is the tool that we just installed and we would like to create a db context for that we would like to scaffold a database that you can get it from this northwind db so northwind db is the same name for the connection string that we're using here this is the norton db that it's using and then we would like to use the package that we installed which is pomelo entity framework or mysql and then the output directory for the models is going to be models the output directory for the context is going to be data the name space for the models is going to be north wind models the name space for the context is going to be northwind data the context name is going to take northwind context and we would like to forcefully run this command that means if your database administrator is changing database schema multiple times you can run this command again to update your models and then i'm going to say that no on configuring we don't want this method in rdb context because we are going to configure our tv context from services so you don't need this whole huge command you can just run this piece of the command to generate the models and db context but i like to pass some parameters to make my solution look nicer so you can run this command so let's copy this and i'm gonna run this command so this is going to build the project and i want you to keep an eye on the solution you can see that it will generate data and models folders here which will represent our database so i'm going to minimize this and here you can see that it created this product model that you're going to create controller for and this is same as what we have columns in our database it also created one-two-one relationship with category and supplier and it created one-to-many relationship with order details so that has been generated too now i'm gonna go to this northwind context which is generated so this is the database context class where it creates the db sets which is going to help us perform all the database operations and in on model creating it configures all the models with our database table so that it knows which model represents to which people now that we have this db context and models let's go ahead and add a service for this northwind context so that we can use this service to create a controller so i'm going to close all these files and then i'm going to go to my program.cs and just like the project template is adding some services i would like to add some services here too the service that i'm going to add is from my toolbox you won't have this code in your visual studio i added this code to make the demo go easier but you can always get the code from github link i'll mention that link in the video description but if you are if you're looking for this code in your view city you won't have this code i'm gonna double click on this toolbox to add some code for this demo this code is adding a db context as a service in my application so that i could use this service in my controller it's throwing some errors let's go ahead and hit control dot to add some name spaces so we can get rid of those errors it's pretty simple line it just four lines of code it's adding a service which is tv context this is the north wind context that we just created and in options i'm telling it to use northwind db connection string and we're also passing the server version for mysql database that we use so these are the two things that we are passing in our db context now that we have the tp context let's go ahead and create a controller for that i'm going to right click on the controller and add a controller and this is going to be a controller for api so i'm going to select api and then i'm going to select api controller with actions using ntd prim core yeah i'm going to add and it's going to ask us the model that we would like to use for this controller the model is going to be for product we are going to deal with product today and it's going to ask us the database contact class so i'm going to use this northwind context class and then the name is going to get assigned as products controller once i click on add then it's going to add couple of packages for our solution and it's going to use those packages to scaffold a controller which is going to perform create read update and delete methods web appear calls and we're going to test them out in a minute it takes a couple of seconds to scaffold and generate the code but it should be done in few seconds here okay so you can see that it generated a controller the controller is getting injected with actually the northwind context is injected in this controller in its constructor so that we can use this context to perform operations now let's go ahead and run this first and then i'll explain the code and we'll perform some thread operation so i'm going to quickly run this and show you how easy it was to start performing database operations using ef core and asp.net web api so i'm gonna open my browser here and you can see these web api calls which were created for our products here i'm gonna click on get products and click on execute that's going to connect to the database and get all the products in the system and show that in json format you can also create a product i'm going to copy this product post product and then i'm going to try and create a product for that i'm going to go to this post method and paste the product from this get api and here i'm going to create a product that i'm going to name as ubs drives chai i do make a good chai so i'm going to add here stripes chai here and then i'm going to click on execute that is going to create a chai here stripes chai in the system let's go ahead and find if we can find that product or not for that i'm going to go to this get product by e id api and then i'm going to pass that id here click on execute you can see that product is getting populated here too you can also update a product but i'm going to copy this json and then go to its put method where i'm going to update here stripes try to curious drive this tutorial because i do make some good tutorials and then i'm going to copy this product id and paste it here which it takes as parameter and then i'm going to execute it to update the data and now you can see that it got updated if i try to find that product again it will come out with the updated data and finally we can delete this product by using delete api i'm going to click on try it out and click on execute that's going to delete that product if i try to find that product again it's going to say not bound because we just deleted that data so this is how you can perform crud operations by creating the controller let's take a look at the code so the code which got scaffolded is this controller like i mentioned it's injecting uh context not when db context so that we can perform these database operations and it's um getting all the products by calling this single line of code which is uh context products to list acing it gets all the products it also has a get product method api which is taking ids parameter it's finding that product and it's returning that product we have a put product which is taking the id and the product it's telling context that this entry has modified so that you can go ahead and update that entry that's how we are updating the product it's also creating a product by using this post method http post where i'm passing the product i'm adding that product into db set and then i'm calling save changes async to add that product into the system and then i'm calling get product to get the newly added product we have delete product but i'm passing the id of the product i'm finding the product i'm passing that product into dv sets they move method and then i'm calling db set save async save changes async method to remove that product from the database so this is how you can perform the cloud operations let's go ahead and load a related data for that i'm gonna open my mysql database here and here i'm gonna run a query which is loading related data here i'm using inner join on my product table so that i could get the supplier for product chart so if i run this you can see this for product chai exotic liquids is the supplier i'm getting all the columns for the supplier so we would like to get this information in json format for that i'm gonna add some code which is not going to work but will add some service which is going to make it work for us but i want to first show you what error you can face so for that i'm going to first add some code to load the related data to load the related data i'm going to use eagle loading where um here i'm adding a api which is get api the name of the api is get supplied from product name but i'm passing the product name as parameter and returning product so in this api i'm using context and product and i'm using include to add my related table suppliers relate table this is one-to-one relationship you can also do one-to-many relationship and i'm also passing the product name into where you can use link to perform to filter your data so this is what i'm doing i'm passing there here and then i'm passing product name into it so that it could only get the supplier for the product name that we are passing now let's run this and this is going to throw an error but that's fine we'll fix that error if i go back to my browser here it adds this get supplier from product to name api here i'm gonna try it out i'm gonna call this api for chi and like i mentioned this is going to throw an error it says a possible object cycle detected and the error is coming because we have not added newtonsoft json as a service into our application so let's go ahead and do that but first we are going to need to add a package so i'm going to go back to my steps notepad here and i'm gonna add this package microsoft hp.net code mvc newton soft json package i'm gonna copy this line and then i'm gonna go to my terminal here and i'm gonna add that package in to my project and we're going to use this package to solve the error that we are getting once the package is added then i'm going to go to my program.cs and here i'm going to add couple of lines of code to add that packages service which is going to be add newton soft service and here these are just two lines of code where i'm saying that you know add services at mvc and add newton's application and here we are telling in options that we would like to ignore the reference loop handling and i'm going to add a namespace so that that error goes away and that's all you need to do in order to fix that issue so i'm going to run this again and see if we are facing any errors or not and now i'm gonna go back to my browser and here i'm gonna pause try again and execute and now you can see that we are getting the data we're getting the product and the supplier associated with that product exotic liquids so this is how you can load related data let's go ahead and load a view for that i'm gonna go again back to my api here and add a api which is going to be for calling a view it's just single line of code it is get alphabetical list of products so if i go to my uh my database here and you can see this we have this view alphabetical list of products and if i select that then it gets the list of products in alphabetical order there are 77 products and it gets them in alphabetical order and that's what i'm doing so when we scaffolded our database it created this alphabetical list of product as the model and alphabetical list of products as a db site that you're going to use to get the data and that's all you need to do in order to call preview so let's run this and see if it's calling our view or not that i'm gonna click on that play button again and then you can see this it added an api which is get alphabetical list of products and here i'm gonna click on try it out and click on execute and you can see it is returning all the products in json format it's alphabetical orders now that we can call a view let's go ahead and add some code to call store so this is going to be a little tricky but we will figure it out so the stored procedure that i want to call is this customer order history right so i'm going to add a new query here and i'm going to send it to as procedure call and here i would like to call this stored procedure but i'm going to pass alf key as parameter [Music] and it will get products for alf key customer and the quantity of all these products and i would like to get this information in json format i would like to call this sort procedure from our asp.net core web api so the first step is to create a model for this when we scaffold the database it doesn't create a model for the store picture so we'll have to create a model for product name and total for that i'm going to go to my video studio and create a folder which is going to only deal with stored procedures it's not going to deal with data and the models that we have already scaffolded the reason why i'm creating a new folder and creating new models in a separate folder is because i don't want to touch these folders which are getting scaffolded because database administrator can change your database anytime and you can just run this command to update your models and you know overwrite the changes which are already that so your changes are not getting lost if you are making any changes please extend your changes or create a different folder in which you can keep your changes here i'm going to create a class then i'm going to call it as customer order history which is going to be the model for our um our response that we're getting from surplus youth i'm gonna create a model product name and uh for total so i'm gonna um use my toolbox here again which is going to add a class it's a simple class which has product name is string and total as integer and now i'm going to add a db context so just like we scaffold little db context for northwind context i'm going to add another db context which is going to be only for stored procedure so i'm going to add a class that i'm going to call as north wind context and i'm going to call it as for procedures and i'm gonna add it and this is going to inherit from db context so i'm going to add some code to show you how we can map our customer order history model that we just created with our with a database response so we created this db set which is customer order history for the model that we just created and on model creating um mapping that entity with the properties with the database response so this is where we are creating that model using model builder now i'm going to inject this northwind context procedures as a service i'm going to go to my program.cs and just like we added a service for northwind context i'm going to add a service for my procedure too i'm i'm gonna do that right here and i'm gonna say that this is going to be for northwind context procedure and now that we have this northwind context procedure service we can inject that in our product controller so i'm going to go to my product controller and just like we injected north wind context i'm going to inject northwind contact procedure and i'm going to add procedure in front of it so that i can differentiate and then i'm gonna add that to my constructor i'm gonna add this northwind context procedure here now that we have this northwind context procedure we can use it to call our store procedure for that i'm gonna go down to my api calls here and add another api call that i'm gonna call it as calling stored procedure which is get customer order history in this i am taking customer id as parameter which is string and then returning customer order history innumerable list of customer order histories and you can get that from context procedure the the procedure the service that we just injected it has dbset customer order histories i'm calling from sql draw where i'm calling the stored procedure and passing customer ids parameter and i'm saying to list async to get the data now let's run this and see if we can call the stored procedure or not so for that i'm going to run my project come back to my browser here and here you can see that this api got added i'm gonna click on that try it out customer id is going to be alt e and here i'm gonna click on execute it's going to return all the information that we were seeing so product name and the total total number of quantity of the products that customer ordered in json format so this is how you can call stored procedures so this is how you can perform credit operations load related data call a view and store procedures for mysql database using entity framework core if you have any questions you can reach out to me on my twitter and facebook handle or you can post the questions in the comment section below thank you so much for watching i'll see you in the next one bye

Channel: CuriousDrive: Solve Coding Challenges & Win Prizes

Views: 28,671

Keywords: .net, vsual studio, programming

Id: pzFY45La2LE

Length: 30min 45sec (1845 seconds)

Published: Wed Dec 01 2021