Calling a Stored Procedure with required parameters from an API

[Music] foreign going to show you how you can perform a crud operation using stored procedure from an API in.net core okay by crude I mean the insert update delete and uh select from a stored procedure let me show you the stored procedure so here is the stored procedure I'm receiving a uh a text like insert update select and delete and I'm performing the operation according to that I have the parameters for all the column values and these things I am going to send it from the API and run that let's just do a brief of the steps what we are going to do okay so crowd operation with stored procedures using API the first we need to create an API project from Visual Studio add a model class add all the required nugets fix the connection string in apps settings.json add the DB context class and fix the program.cs the next create a store procedure with all the operations and the parameters that we will that will determine the type of action to perform using an e-files condition and finally create the API controller that adds all the action methods for the operation and pass the required parameters let's see that in action all right so if you were looking at this for the first time and you don't know how to create an API let me just go through with the steps so you create a new project and you select the project type as a web API and I'm slow here okay you need to select the asp.net core web API like this one this one move next then you select the project name you select the location where you want to save that move next then you select the framework like dotnet6 or 7 I'm using six uh https you may use you may not and then create once you create that you would see a blank project you would see a blank project like this so nothing would be there in their controllers and this data and model folders won't be there so first thing you do is you create the model class so we add a new folder to the bridge to the project like right click on the project add add new folder and then in this folder name it as models in the model right click on the model folder add new class name it as the name of your table so my table's name is patient once you have this patient class add these field values or whatever columns you want to add the key the first ID column should be a key integer that's a counter and the remaining columns that you need okay so I'll put the codes in the GitHub repository and share that with you so you can understand that okay this thing not mapped is the string which we are trying to send to the stored procedure but we really do not have a column like this so when you want to have something and you do not want to put that in the database you put it Mark it as a not mapped okay so that will not go into the database all remaining things goes to the database as columns okay now this is our model class so if we go by the steps from the presentation okay so after the model class we need to put the nugets so now that our model class is ready uh let's add all the nugets so we go to tools nugget package manager and from the managed nuget packages for the solution we add the nuggets from there um let me show you what all things we have installed here so I installed the first one that comes up the Newton soft comes up first slash Buckle is by default Microsoft Entity framework code or tools that's uh we have installed that the SQL server and The Entity framework core and the Diagnostics so if we would have looked at it on a text version so this uh these are the new guys that I have installed here next we go to the connection string and fix the connection string so connection string should be set up on an app settings.json file so this is my connection string uh you can put up yours now next we go to the DB context class add another folder named data and put the class file in it name it as application DB context this is a class file so inside the data folder add new class file and name it as application DB context okay once you have this uh class file and then you inherit that from the Ruby context this is the base class that we've been heading from application DB context is an edited from DB context in the this is the Constructor in the Constructor uh parameters use this one DB context application DB context options base options below that add your class file public virtual DB said this is going to create your table once you have this thing you have your nuggets you have the app settings with the connection string go for the package manager console and add migration but you have created that let me just show you what things you need to put in the command add migrations migration I'm sorry and then I need any text and this I put it I need you can put anything here so any name for this migration file once you run this successful you will get a migrations folder which I do not have here I'm already using a table so I don't have here if you're using an existing table you don't need to add migration you simply put in the connection string what is the uh the database and what is the server it will take care of the table and everything if you already have the table so you just have this patient stable so that will be taken care of in case you do not have that you need to put this ad migration that will create the file which has the commands and then you update the database the next command is update database that will create a table in your database once you have the database stable you should have something like a table and you have migration and now that you do not have any value you could be able to see all the columns that has been created by migrations then you come up to a controller section let's move ahead to the controllers oh I'm sorry we missed out the stored procedure so we create the stored procedure right click on the um programmability folder and then there's no procedure uh you need to address you don't need to right click on that or you can just put in this command uh I have created alter you need to put in create so you need to create procedure name proc and the procedure name and then you have these parameters these are the input parameters and then the e-files condition and then the insert update select and delete queries in it they need to run the stored procedure and the stored procedure is oh it's already there so I need to create an alter it's not allowing me because I already have it so now it's created so let's go to the controller add new controller I have patience controller so once you create the controller class add new controller name the controller class with a controller at the end select API controller Okay add this and then put in the name of the controller with an ending with a controller as the uh what do you say suffix okay we should have a suffix controller name then inherit that from the controller class initially it would be controller base but I'm using controller because I'm using return Json if you want to return anything else you can use the controller base class but I'm using Json that's why I used controller class now uh use dependency injection to have the DB DB context okay I didn't I didn't tell you about the program.cs let's have a look at that the program.cs is configured in such a way that we have the application DB context and the connection string configured in it these are the two lines that I only added with the default program.cs so nothing much more in here moving ahead to the controller class we have uh the first one HTTP post with an x i action result create and I'm passing the model class as a parameter so that I will have all the values in it and as you can see these are the command that we need to pass on I will I'll put that in the GitHub okay now index will receive my content from the database and show that uh on the screen this is the index you can see that the the command for the index is context.table name Dot from SQL raw and put that uh the name of the stored procedure with the parameter at the red type equals to select so this is the parameter that's going to understand what type of operation is this is it a select or if it's um okay so I can hard code this one and here I'm sending it from there but I can hard code here with insert you can pass that from the API result or you can pass it from here so in case of insert you're passing all the values to the parameters but you don't need that in the remaining instances if it's an index you don't need anything if it's an edit then you need to put in like what is the condition of the edit in your sort procedure according to that you will send the values so my update has it's going to update the age and the address uh following it with the type of ID or the the ID it has received so I just pass three parameters to it ID age and address and the T type is update so four parameters delete is only taking the ID and the type of the operation and it's returning me the message like it's deleted or it's updated or whatever it is okay let's run this application and see that it works So currently while it's running uh if I'm looking at my table I have how many six rows and uh let's try to add one row and try editing and deleting that one okay so here um get patients should show me all the records that I have so I have like how many five six okay so that's going to show me six records here Swagger comes by default if you want to put that in Postman you can just copy this URL from here and send this as a type of get request and this will work on uh Postman so these are the results that we see here now let's go ahead and create one and work on that way so post is going to create try it um I will pass nothing with the ID because that's an auto generated column let's put the first name is Alex last name as David page um I will put that and say 62 address everything Network we should type let's say inpatient that number let's say 25 G diagnosis uh uh I don't know anything so let's say medicines p-type let's say we're not adding anything here that will be taken care of at the back end otherwise if you want to pass that so you will just mark it as insert from here now let's execute this and this is going to add a new row to my table what I got which one and work here to integer give me a second here guys all right guys uh so what happened was like uh it was expecting a parameter with the ID so I did not pass anything for the ID um it was it was expecting every parameter in the um stored procedure so as I used an ID so it was expecting an input parameter by the ID though it's Auto generated but you still have to provide something okay so remember one thing when you're creating the stored procedure try to initialize all of them integers with zero and War cards with blank okay you need to put that thing in the parameters otherwise it won't work you need to take care of this procedure so that it works with our API as expected oh right now um if we are running this create let's see I haven't shown you okay so one row is already created and I deleted that one but still I'll show you so now we have the patient and I'm trying to create a record so I'll provide this one with uh um I think I already have a cabin so let's say Kali and uh Tunes age let's say 65 address let's say Idaho type as let's say outpatient that number let's say 36 key diagnosis I don't know just medicines type I don't need to pause anything but it's already there insert it's already hard coded there see it's added okay let's see that in the database yep we got the record in here okay so we got that now let's try to update that and delete that let's do the remaining things so get will give me all the records edit uh try it out so I'm trying to change what's the id that's 20 okay so in the 20 I will update the age and the address I guess so currently the age is 65 let's make it 25 and the address is Idaho's I will make it London so he moved to London and let's execute that updated okay let's check that in the database perfect we got this 25 London okay so that's updated now let's try to delete this clear let's go to the delete and I need I think that's the 20th tried out 20th row that I want to delete yes that's the 28th row perform the operation execute okay response is deleted let's check that in the database that's deleted okay so as expected our stored procedure worked with the type of message that we are sending it and the queries that we are working on so if you want to have a look at this stored procedure you can look at it in the video or else I'm also going to share that in the GitHub so pretty much that's all about this video um I hope you understood what we went through and what worked for us um so until we have our next set of videos stay tuned stay connected and happy coding thank you

Channel: Fired Developer

Views: 183

Keywords: how to call a stored procedure with required parameters from an API, calling a stored procedure with required parameters from an API in C#, Parameterized Stored Procedures, example of calling a stored procedure with required parameters from an API, stored procedure with required parameters

Id: F68g0x25DKE

Length: 13min 6sec (786 seconds)

Published: Mon Sep 18 2023