Stored Procedures with Entity Framework Core in .NET 6 🚀

hey friends patrick god here thank you so much for dropping by and you see it on the title ef core with stop procedures this is what we want to talk about today we'll use three stored procedures to where we just select some stuff one without any parameter then one with a parameter and then we will also use a stored procedure where we're doing an update so a little bit more complicated but in essence it's really really simple all that stuff and that's already it's ef core six using stored procedures efcor is limited regarding storage procedures you will see this in the tutorial and if you learned something and like this tutorial i would really appreciate it if you click the like button maybe even subscribe to my channel and if you want to learn more about the.net and want to get these tutorials in your inbox then maybe the newsletter is something for you because then you get these tutorials in your inbox and you will also be the first to know if there's something new coming up like a new online course for instance like the.net web development boot camp which is coming very soon all right so that's it and now enjoy the tutorial all right let me give you some context here first we've got a little web api project here already with some models in the root folder and the character controller the fat controller i know in the professional environment you shouldn't do that but for this quick and dirty tutorial well let's just do it like that all right because we want to focus on the stored procedures here with ef core 6. so what we want to do is we've got this database here the.net rpg database this is from my.net jumpstart course so if you want to learn more about that please have a look at the video description for the link and in here what we have is well again some models for instance we've got some users let's have a look here we've got the user patrick and the test user with id 1 and 2 and these users can have role playing game characters here we're building a little role playing game in this text based role playing game in this course and you see it here user ids one two and one and here are the characters with the name raceland frodo and sam and also the hit points tested that already you see it here what you want to do is we want to change the hit points later on so let's just reset to this to a hundred and now in here i've got some stored procedures already so two with a select one with no parameter whatsoever and the second one with a parameter where we just want to get the characters of a specific user and this one here and the first one only receive all characters and then later on we want to update the hit points with the start procedure here so these are simple stored procedures and i think this is okay and sufficient for this little tutorial if you want to do more well please try it out by yourself now first this controller here the the very first method is simply returning the complete list of all characters in the database so let's start this thing there it is one method here with the the road api character and this is the example value and we try this out hit execute and we get all the characters right so racelandfrodo and sam perfect but this is not the start procedure so let's go back to the database and let's just right click and then create this thing or let's just create the script and here you see it select everything from characters so really really simple stuff and to run this stored procedure now with entity frame record there is this little method called from sql raw all right so let's just copy this thing here and give this a route this time for instance simple sp for store procedure and then we also call this get our characters stored procedure because we want to call the stop procedure now instead of simply a tool list async we use the method from sql raw and in here now we just enter the name of our stored procedure so select all characters and then to list async again all right and that should be it let me have another look characters from sequel raw select all characters to list async this actually should be it so no visual studio don't want that let me just stop the application and maybe try to run this one more time and all of a sudden it seems to work interesting so now here is the second method character stop procedure we try this out hit execute and it is returning all our characters right so this is really really simple so i would say we go to the next store procedure this thing here let's have a quick look here we see we've got this parameter here the user id and the actual uh sql statement and then is then simply select everything from characters where the user id is then this user id here so let's write the method for that again we just copy this because i'm a lazy coder here so actually we get and then let's say we just add the user id as a parameter and call this then get characters maybe again with the parameter user i d and then here again we use from sql raw and then select user characters all right we need string interpolation here or don't need it but we can use it because we want to use the user id as a parameter and if you have more parameters then you have to separate them by a comma and then this should work as well and now again interesting here is that we access the characters here right the db set character and then we have this function from sql raw so this is already something that is limited to android thermocore here in this case this this thing has to return the actual entity then or a list of this entity right so if you want to use more complex stored procedures well they can do anything you wanted would anything you want them to do but they have to or they only can return the actual entity even without uh relations right even without relationships so if you wanted to include the weapons or the skills or something let's have a quick look here at the database you see here um we've got the the character right but then here for instance we've got the weapons with the character id or the the the character skill well also with the character's id so you cannot return that as far as i know please correct me if i'm wrong but as far as i know you cannot use or return these relationships then uh with stock procedures you would have to use well link for instance to include then the relationships right so something like that got already videos for that on my channel in this case we would then say okay let me include the weapon and the skills and so on all right so this is just a side note now here we want to get the characters from a specific user so select user characters with the user id let's save that i have no idea what's going on with visual studio today so let me just stop the app one more time and try to start it again and here's now the other method with the user id again we want to return characters so let's say we use the user with the id one we hit execute and we get two characters racelyn and sam and let's say we use the user with id2 then we only get one character and this is frodo and when we double check in the characters table here we see user id one and two and here also two now what had what's actually happening when i try to use id3 well nothing comes back right so this these are the select statements the stored procedures with the select statement and now the well most complicated one maybe in this example update character hit points so let's have a look here what we can do is we see we have two parameters now the character id and the hit points and this is actually the statement here or the actual procedure where we just say update characters we set the hit points to the hit points parameter where the id of the character is character id and now this is a little bit different because here now we use the method execute raw execute sql raw right so let's just copy this one more time it's again a get method where we well let's just change this to hit points then the parameter character id and then let's just say hit points and here now we're going to return is actually just the number of the affected rows all right so let's call this now update character hit points with the forgot one bracket here all right with the character id first we can remove this and then the hit points and here now we access the database this is important and then we get execute sql raw async not async doesn't really matter here update character hit points with the character and then also the hit points all right and then that should be it all right we've got the routes returning an integer now the number of affected rows and update character hit points the character id and the hit points database execute sql raw update character hit points is the name of the stored procedure so let's save that this time it seemed to work got it here and now i am curious if this works so character id let's double check uh we have here with our character table characters let's say we've got frodo with id5 and now he gets 200 hit points we hit execute and it says one so one row is affected now let's just double check with our stop procedure here we try this out hit execute and we see frodo has 200 hit points nice and now when i say id 1 for instance doesn't exist we hit execute execute and we see zero rows affected now one more hint about that when you use the default template here for stored procedures you see a line like that set no count on when you leave it like that then the well we can actually test this then the method will return not the number of the affected rows so let's no let's just let's update this so modify and then here set no count on hit execute and when i now send this one more time execute see it here minus one um for id fi one and let's say now id five and i set this again to a hundred hit execute i still see minus one but when i get all the characters again i see it actually worked right so this is important you have to remove this line if you want to see the number of the affected rows because then we can execute this one more time and then we see okay one row was affected all right and this is everything real quick ef core with start procedures that's it i will push this to github although you won't really see the stored procedures but maybe the code is all also interesting for you to just copy and paste it if you're lazy like me so check out the github repository in the video description and that's it for this tutorial yep as i said quick and dirty and really simple right actually you just have to use these specific methods from sql raw execute sql raw and with that you can run your stored procedures with the help of entity framework core now if you learned something and like this tutorial i would really appreciate it if you click the like button maybe even subscribe to my channel and don't forget to uh have a look at my newsletter to get these tutorials earlier in your inbox and also upcoming online courses like the dotnet web developer bootcamp maybe this is something for you and apart from that maybe you just want to hang out a little on this channel then we can become best friends check out these videos here on the side i would really appreciate that thank you so much for your time thank you so much for dropping by and watching all my tutorials and i hope i see you next time take care

Channel: Patrick God

Views: 46,232

Keywords:

Id: nHv9jCPtwT0

Length: 13min 50sec (830 seconds)

Published: Tue Sep 06 2022