SQL Server + EF Core 6 + ASP.NET Core Web API + CRUD + Load Related Data + Stored Procedures + Views

hello everyone in this video i'm going to talk about how you can scaffold your microsoft sql server into models views and stored procedures and how you can call crud operations call your views and suit procedures from asp.net core web api using entity framework code for this demo we are going to use northwind database people who don't have this database i have a sql script which will generate this database for you guys it's there on github i'll show you that in a minute but we'll scaffold this database and we'll perform all the crud operations in web api will also load related data in the web api we'll also call a stored procedures from this web api to see if we can do that or not and we'll also call a view from our hp.net code web api project to see if we can do that or not like i mentioned north northwind is a relational database and you'll find the script to generate this database in my github repo let's go ahead and do that for that i'm gonna go to my browser here you can see this ef core all database considered repo i'll share this link in the video description if you go to ms sql server folder in here we have this northwind sql script if you run this this should generate the database for you i'm gonna go to my sql server management studio and here i'm gonna run this query to generate that database so if i execute this then this will generate that database for me if i refresh this database then you can see this database getting generated here here we have tables customers employees and we'll be dealing with customers today to show the demo we also have some stored procedures if i go to programmability and open source procedures you can see this we have some sort procedures we are going to call this customer order detailed store procedures from our web api and then we have some views we are going to call this customer and suppliers by city view for our demo so let's go ahead and create a web api and call all these models and store procedures and views from our web api so for that i'm going to go to my visual studio this is visual studio 2022. i'm going to create a new project and i'm going to search for web api template and i'm going to select sb.net core web api for c-sharp and then click on next here is going to ask me the name of the project i'm going to make sure that it's getting created in the same folder so that i can share this project with you this project will be on github so that you can compare your code with it when you practice it so i'm going to name the project as not the end dot ms sql and then go to next window here i'm going to select the target framework as dot net six it got released i think a week ago you can uh when you install video studio 2022 you should get that with it and i'm not going to enable docker and i don't really care for the controller and open api i'm just going to leave it that way and then i'm going to click on create it's going to create asp.net code web api template project which returns random weather forecast for the next five days let's quickly look at this project and then we'll run this project so they used to have startup.gs they don't have that anymore there's only program.cs and inprogram.cs is where the entry point is this is where we are creating the builder and then once we have the builder we're using that builder to add some services and once we you can see that we have added the swagger service so that we can call our web apis with a nicer ui and then we are building app and then we are configuring that app we have this map controller and a controller is also added into this project and finally we are running the app right so we have this weather forecast model and we have a controller this controller returns random forecast for the next five days let's go ahead and run this and see if we can get this working so i'm going to click on this uh start without debugging button and you can see that it returned our web api we can see this nice ui because of the swagger package that we added it's part of the template and let's go ahead and call this weather forecast i'm gonna try it out and then click on execute and this will return weather forecast for the next five days and you can see it in a nice json format now let's go ahead and configure our database with our web api so that we can perform crud operations on our customer on our customer table here so this is a customer table and i would like to perform crowd operations on this customer table from our web api so first thing we'll have to scaffold this database right so i'm going to go to my visual studio and here i'm going to use an extension which is called as ef core power tool if you search for this extension will find it on the top it's called as ef core power tool i've already installed it you'll have to restart your machine in order to use it so i'm gonna use that for my project too i'm gonna right click on this project and then you can see this option here ef core power tool and click on reverse engineer here you can add a connection i've already added a connection but if you want to add a connection you can click on add and here i'm going to look for host sql express you can do this with sql server 2. i'm using sql express for this could be done with sql server 2 and you can see this northwind database in in the drop down that we just created i'm going to select that and click on ok and that connection gets added in my in my pop-up here here i'm going to select ef course 6 that we would like to scaffold and then i'm going to click on ok once i click on ok then it's going to ask me what all things that i would like to scaffold here i'm going to select all the tables in the database that i would like to scaffold i'm going to select all the views which are there in the database that i would like to scaffold and we also have stored procedures all of the stored procedures in the database that i would like to scaffold once i click on okay it's going to ask some more questions like do you want these files which are getting scaffolded in a particular folder what name space do you do you want to assign to them so the folder in which i would like my models to get scaffolded i'm going to name that folders as models of course and i'm also going to assign the namespace which is going to be northwind dot model so i'm going to put models here i would like to scaffold my db context in data folder so i'm going to say that this is going to be data and the namespace of it is going to be northwind.msequal.data you can also pluralize and singularize the objects which are getting generated i always have a hard time pronouncing blu-rays anyways so i'm going to select i would like to select that option and there are some other options too like data annotation including uh connection string into generated code i don't like to do that so i'm not gonna select that you can also select this install ef core provider package into the project so we are going to need provided packages right we're using sql server database so we're going to need the packages for that i'm going to check that but i think there's a bug in this extension it doesn't uh install these packages for the project but we'll do that manually that's not a big deal and i'm gonna you can also have some advanced options i'm not gonna get into this but once i click on ok then it's going to generate models ndb context for my project if i go to models folder here you can see that it created models for all the tables all the views and all the stored procedures for my database into the models folder and if i go to my db context this norton db contest actually opened here it mapped those models with all the tables with all the tables which are there in our database so you can scroll down and see what all things have been configured here but let's go ahead and fix these errors for that i'm gonna have to install a couple of packages for that i'm gonna go to my chrome browser the first package that we would like to install is microsoft entity primal code design i'm going to copy this package reference and go to my project here i'm going to go to my project and add a package reference and we'll also have to add a package reference for our provider database provider i'm going to go to this another nuget package and i'm gonna get this package too i'm gonna share these links with you in the video description i'm gonna grab this package reference and go to my project here and i'm gonna um add this package reference here once i add that then i'm gonna save my project file once i save the project file then those package reference will get added into my project and if i go to my context file here you can see those errors went away i'm gonna quickly build my solution here if i build a solution then all those errors will be gone and now we should be able to run our project but we just created models in db context for our project we did not connect our database to our project so we're going to add we're going to need to add our connection string and a service which is going to connect to this database so let's first go ahead and add a connection string for that i'm going to open my app settings and here i'm going to add a connection string in in my app settings i'm going to select connection string and here i'm going to add a connection string called as northwind db and if you want to get the connection string you can get it from visual studio for that i'm going to open my sql server object explorer you can get that from here if you go to view you'll get the sql server object explorer from here and here i've already configured my server here but you can add a sql server like this and select your sql server from network or from local machine so i have selected this you can see northfield getting populated here and i'm going to open this and i'm going to right click on northwind and go to properties and here you'll see a connection string property that we can use in our app settings so i'm going to select all copy that and then paste it here and then we can use this connection string in our project now that we have the connection string in app settings let's go ahead and add this db context as a service for our project so that we could inject this in our project anywhere and use it for that i'm going to go to my program.cs where we are adding swagger i'm gonna add db context right right after it and i've added a snippet for it which is this so if i add these four lines of code which is build services and add tb context i'm passing the class name of the db context which we created and then um in the options i'm saying we would like to use sql server and i'm passing the connection string that we just added in our app settings here i'm going to add a namespace so that it doesn't throw error which is microsoft entity framework code now that we have the service now that we have the connection string we can create a controller from our visual studio which will give us the card operations for that i'm going to go to my controller folder and i'm gonna right click on this and then i'm gonna add a controller and here i'm gonna select api and in api we have this option of api controller with actions using entity framework code which will create crud operations from your model so i'm gonna click on add here and here you can select the model that you would like to use your operations for and you can select the db context which is going to be northwind context and it's going to also name your controller as customers controller these stacks are getting cropped because i've increased the point of visual studio but this will look nicer if you're using the default font and this is the customer controller the name of the controller i'm going to click on add this is going to add couple of uh packages in my visual studio and it's also going to create a controller which will have create read update and delete operations for my customers and we will run our project uh once it is done generating that code so i'll give it a couple of seconds to generate that piece of code okay so it created this controller which is customers controller and in this we have a method to get all the customers i'm using this context which is getting injected here which is northwind context we added this as a service and it's getting uh it's calling get customers it's loading all the customers here this is one web api it's getting one customer depending on the customer id that you're going to pass so this is also an http get method we have a put method which is going to update a customer so if you would like to update a customer by its id you can do that too you can see that we are finding that uh we are looking for its state that customer state and then we are saving that customer and then we have post customer where we are adding the customer into our db set and then we are saving the changes to save the new customer into the system and then finally we have delete customer by its id we will find that customer and we'll pass that customer uh in this remove method of the tv set and then finally we'll call save changes async so that we can save the changes and delete that customer let's go ahead and run this and see if we can perform credit operations from our web api so i'm going to quickly run this and then go to my browser you can see that it listed down the thread operations here uh we just had weather forecast in the beginning now we have all these five web apis too which is pretty quick to generate now let's go ahead and perform all these operations so here i'm going to get all the customers i'm going to try it out i'm going to execute it and this will load all the customers in my database so you can see that there are so many customers which are getting loaded in this json format if i go to my database and read on this query you can see that these customers are now getting loaded in json format so that's how easy it was to connect to database and get all the customers let's go ahead and create a customer for that i'm gonna grab this last customer and we'll make one change and that customer will change its id and will post a new customer here so in the request body i'm going to paste that last customer that i copied and here i'm gonna change its id to wall gx and then i'm gonna execute it if i execute that customer gets created in my database and you can look it up by its id i'm gonna copy its id and you can go to this get method where you can pass the id and get the customer too if i pass the id and get the customer you can see the customer is getting populated here you can also update a customer so i'm going to copy this json and then go to this put method which is updating the customer and i'm going to try it out and here i'm going to select the json that we just copied and the contact number on contact name i'm going to set as previous years right and pass the id as wall cx and then if i say execute then it will update my contact name tip if i look it up again here you can see that name getting updated here you can also of course delete a customer i'm going to go to this delete method and try it out and it's going to delete that customer you can see that it said it deleted properly and if i try to look it up it's going to say not found because we just deleted that customer so if i click on find you can see that it's saying not bound because we just deleted the question so this is how you can perform crud operations and how easy it is to scaffold the database and perform all these operations from web api now that we have covered scaffolding and performing create read update and delete let's go ahead and load related data so loading related data that means i would like to relate i would like to load something which is related data so if you look at this query here i'm loading customers and orders for that customer so it's like an inner join query if i run this then it loads orders for this customer and this is something that i would like to show in a web apm for that i'm gonna add some piece of code in my customer controller and this code is going to be on github like i mentioned before so you don't have to worry about uh not finding this code i'll put the put the link in the video description so if i go to tool box here i've added this piece of code which is going to load related data here i'm going to pass a customer id and then i'm going to use context to find that customer and then i'm going to call this include method to include orders for that customer and then return force or default now i'm going to run this but we're going to run into a problem that we can fix and i would like to show how you can fix that so i'm going to run this and that web appear will get populated here and here if i pass a pass a customer id i would like to find what which customer id that we can pass so i'm gonna just pass this customer id and um if i go to my browser here and pass that customer id to load the orders for that customer then it's going to throw an error because you can't it's going to see possible object cycle detected so you can't show an object which is nested with other objects directly you're going to need to ignore that serializing property so let's go ahead and do that for that we're going to need to add a package which is going to be microsoft ac dot net core mvc newton software json i'm going to grab this and then go to my project and then in the project i'm going to add that as a reference now that we have added this package let's go ahead and add this reference uh this as a service so i'm gonna go to my program.cs and here i'm gonna add a couple of lines to add that as a service for that i'm going to go to my toolbox and add a line which is going to be builder services add mvc and add newton's newtons of json and you can see the serializer setting where we are saying the reference loop handling we would like to ignore that so i'm gonna add that into my code and i added a using statement so that it doesn't throw any errors now let's go ahead and see if we can load related data or not so i'm going to run this again and go to my browser here i'm gonna pass the customer id again which is alf key and i'm gonna try it out pass the customer id that's not the customer id this is the customer id i'm gonna pass that execute it and now it's not gonna throw any error it's going to load the customer information and the audio information for that customer so there are six orders one two three four five and six that's the end of it and then you can see there are six audits for that customer so this is how we can load related data now let's go ahead and load a stored procedure for that i'm gonna open the store procedure so this is customer order details surposition i would like to call that let's go ahead and call it from here first and show you um what results are getting populated here so i'm going to pass an order id and click on ok and it's going to return three products for that order id and we would like to show this result in json format for that i'm gonna go to my project here again and then add some piece of code which is just one line of code so if i go to my toolbox here again i'm gonna add another web api which is going to be customer order details async which takes order id and here there's only one line you can see this we have this context that we injected and we have this get procedure method so if i go to my data folder if you have this northwind context procedures plus which has this get procedures method it returns all the procedures for the database so that's what we are doing here and we are returning customer order details stored procedure here and we would like to call that stored this year so let's go ahead and run this and see if we can call that procedure or not if i go to my browser here again i'm going to pass that order id i can get that order id from here and then i'm going to pass it here when i execute it it's going to load those three products that we just saw in our sql server so those will get loaded in json finally i would like to load a view which is also a single line i'm going to add that code from here it is i added a web api which is customer and suppliers by city which is a view in my sql server if i go to my view and then select this then it's going to load 120 records city company name contact name and relationship and if i go to my web api here i'm calling contacts and this is customer and supplies by city it's a model which was created so this is the model which was created and i would like to i would like to return all of the results for that view right so i'm gonna run this again and if i go to my browser again it's going to populate that customers and suppliers by city web api and if i try it out execute it it's going to return all the results for the view which are there so if i look at the results you can see the city company company name contact name and relationship which are the same columns that we are returning from our view so this is how you can scaffold database perform cred operations you load related data call a sold procedures call a view and interact with your database in json format using asp.net core web api and ef core if you have any questions you can reach out to me on my twitter or facebook handle or you can ask questions in the comment section below thank you so much for watching i'll see in the next one bye

Channel: Curious Drive

Views: 39,074

Keywords: .net, vsual studio, programming

Id: DoYmpAPoixI

Length: 26min 51sec (1611 seconds)

Published: Wed Nov 17 2021