How To Call Stored Procedure In ASP.NET MVC Using Entity Framework

hi guys welcome to digital Tech joint in this video I'm going to show you how you can call store procedure from asp.net MVC application I'm going to use Visual Studio 2022 and I'm using dotted framework 6.0 and here is a MVC application template which is already loaded it's a new project and I'll be calling this road procedure that is available in Microsoft SQL Server management I am using 2012 version which is a Express Edition which I downloaded from the Internet it's a free community version so here I'm going to create a database first so let me call it as calling SP demo so this is my database name calling SP demo and let me go ahead and add one table let me add a simple table to demonstrate this I say ID int name wirecad and then mobile and then email so this is this is my simple table and I just click on ID and Mark this as integer and also Define identity specifications as yes so that it is auto related incremental I want to save this and name it as customers so now we have our database calling SP demo and the table I have not created a procedure yet before that let's go and set up our application with the connection string and everything so I go to app settings I just enter before the logging tag and just paste it this is my connection string so what it does the connection string app it has a server name this is my server name on eternity and the database is not SP demo but it's calling SP demo and then integrated Securities through that means I am not providing any username and password it is taken by default the windows prediction enter server secure trust server certificate through so let's this is a connection string now once the connection string is done let's go click on the models and add one class name this as customer entity so this is my customer entity class and I'm going to define the IDS which I defined in my table so this will be ID and then this will be sorry this will be name ring this will be mobile and finally this will be our email and this is my key so my model is defined now we have the model class ready before we proceed with setting up our application DB contest class let's go ahead and add the required packages right click on the project and say new that packages and we are going to add first The Entity code so here is the entity code we add this version and then we have since we are working with SQL Server database we are going to use let me add okay so I search for SQL server and then Entity Framework yeah so here this entity Microsoft Entity framework SQL Server you add this Library so these two libraries are enough and these are already installed over here now let's go right click add a folder as data layer let me add a class called application DB contest this is my application class now this class will Implement DB contest and it's not able to find because okay so now it is added automatically the required Library so this is my debate contest class I just click on the DB contest and then on the extreme left hand side click and generate Constructor using options so this is a Constructor I am going to inject the connection string from my program class file this is one thing and let me also add the DB set so go and say DB set and I use the customer entity and this is the name of the table so I already Define that numer table as Customs so this flag will represent the table that is over your customers okay this is done let me go ahead and do a dependency injection and inject the connection string in the Constructor which I'm going to do from the programs file and here I'm going to use before the application Builders dot I'm going to add the dependency injection over here dots Services dot add DP contest and I want to name the class application DB contest application every contest this is the class that I've created and options [Music] I'm going to say options dot use SQL server and inside that I'm going to say Builder Dot configuration dot get connection string and this is default connection that I've defined so this becomes my connection string here app setting if you go this is default connection which I called over here default connection so my connection string is ready my DB contest is ready so now let's go ahead and add the controller the first thing we are going to do is that add a controller and here I'm going to show you how will we call the stored procedure now it's better that we go ahead and create the procedure first so that you can refer that in the controller class so guys I have a table over here let me go and add some records so this is one user and it I've added three records to the database now let me go ahead and add the procedure you click on the database calling SP and I'm going to create new query window and here I'm going to create the procedure so first procedure which I will be fetching fetching on customer records no filters so I'm going to say create procedure get all customers so this is my procedure name and I'm going to say as begin and end what this is going to do is simply select or from customs my decision procedure and I'm going to just execute this got executed you can just check I say execute get all customers and it should fetch me all the records okay execute error so you are able to see my procedure is working now let me create a second procedure right now which will get single record so this is the second procedure which I'm creating okay so I just copy this procedure now this procedure will have a parameter so we should be after at the rate and call it as ID and it will be int and this will be a get single customer record okay and then you say select all where ID is and we pass this parameter right so this is our second procedure let me execute this it got executed let me test it so I say execute get single customer record and I pass one and select it and execute I should be able to see John record cool now let me go ahead and also do an update so let me write another query update customer record I'm going to copy the same thing and here I'm going to paste it and I I'm going to say update customer record okay so here it takes ID which is required then for updating I'm going to say mobile and I'm going to update the mobile then I will say email I'm going to update the email three and here I'm going to change it so I'm going to write the query update customers set mobile equals to at the rate mobile comma and email is to add the rate at the rate email where IDs and your password parameters so our update procedure is also ready Let's test this procedure also so now I say select all from customers and so you can see so I'll update John record so I'm going to say execute update a customer record I'm going to ask about the ID I'm going to pass the mobile number let me keep the mobile number as it is and let me change the email address to John at the rate apple.com okay let's execute this query and it's got updated one record updated now if you see select all from customers and you see okay so here there is some issue Okay so it's by default in your update frog I'm not provided the length so it has taken by default one so I'm just going to alter this and updated I'm going back over here and I will update this and you can see it's got updated fine so we have all these three procedure let's go to our visual studio now and add a controller and let's check it in empty controller and name it as customers so this is my controller now this is the default index now let's go ahead and create a view rightly first right click on the index say add view say add and let it be the same index and it is added now you can see this is folder created under views as customers and the index it is empty right now first you go ahead and write the code over here so let's go ahead and write the index code foreign application DB contest class and underscore TV then we'll add a Constructor see so in order to add a Constructor I type c t o r tap tap two times the Constructor is created and I'm going to pass application DB and here I'm going to initialize underscore DB which is passed over here my underscore DB is initialized and I'll come to index View now guys here I'm going to call the first procedure that is getting all the records so I'm saying and say get all customer details get all customers and then I'm going to refer the DB contest class dot r customers that DB set that I have defined in my data layer you can see I defined this over here this is representing the table and here now I'm going to use the method called from SQL Raw and here inside that I'm going to Simply call the procedure get all customers and paste that over here and say to list and then I'm going to pass on this value to our view so this is a code that simply fetches the cause the procedure from the database and fetches the record and pass it to The View let's go ahead and also add code that will fetch the record single record of the customers let's finish our controller class and all the code and then create the UI and see the things in action so I want to create bubbling I action result and I'll say get details and I'm going to pass the ID so this will fetch single record for that I'm going to call the same method over here it was paste it but here instead of passing get customer I'm going to call the second procedure is to get single record get single customer record and here I am going to pass the parameters as well so I say dollar refer to the double quotes and here I'm going to put a space open your own parenthesis and then I'm going to say ID okay and this won't be a to-do list since I have to fetch the model this will be representing our custom identity model so I am going to convert that I numerable and say first and default so this code will fetch the single record so I'm going to just say get single data and I want to pass this over here so this code will actually here you can see we are passing the parameters okay and this is the value that is passed over here to the procedure and it replicates so since this is expecting ID we are passing ID and it will fetch if you go inside call this SQL statement and face the single record and we should be passed to The View now let's go in and write code that will update the record so for that I am going to since it will be a post because we are going to update add some values and then post so I'm going to call public sync task I actually wizard this will be update details here I'm going to pass ID I want to update the mobile and I want to update the emails so now the second thing I'm going to do is that I need to pass all these parameters to the procedure in a similar format but since there are multiple Fields I will be using the SQL parameter objects from saving the param Mr new SQL parameters and inside this I am going to say new pattern this will be the first entry so I'm going to say parameter name is at the rate ID this will be what data type integer and finally what is the value the comma and this will be value I'm going to pass this over here so this will be the value similarly I'm going to copy this and paste it two times one and two because I need to pass mobile which will be where can value will be mobile which is pass in the parameters over here and then this will be email and this will be email and I'm going to just put a semicolon so this works so our parameter list is ready so this is the parameter list now we come down and just copy this code over here paste it and this will be customer update result so here instead of the entity customer we are going to call the database so underscore DB dot database and this will be execute SQL Raw I think execute SQL yeah this one is equal to Raw async and here I'm going to pass update customer record and with that I'm going to also append execute which I did it over here execute and then execute the procedure and then I'm going to pass the parameters I will enter it ID comma at the rate mobile comma at the rate email these are the parameters and I'm going to remove this I enumerable and then I'm going to say comma param so Paramus and passing all this parameters in one variable so all these values so this is the procedure this is the expected input parameters and this is a value so this is done now this will written either minus 1 if the record is not updated or it is written one so accordingly we will redirect to our page so if say we if customer update result equals to 1 and we are going to say return redirect to action and this will be r index else we are going to see return View this will be s let me return View so our code is ready we are getting an exception because yeah because this yeah because this is an async method I forgot to mention every so this is a code so we have all three code ready we have code ready to fetch all the record fetch only single record and update the record also so it's time that we go ahead and build our UI so we go to our view so we already created one UI called index so what I'm going to do is that I'm going to paste HTML but before that I'm going to refer that model the model will be I in numerable and here I'm going to pass the customer entity [Music] okay so now the HTML code I'll just copy and paste the HTML code okay so here's the HTML code it's a very simple code here I am creating a table and then I am creating the column which is ID name email mobile and then I'm writing a for each syntax and I'm going to I'm displaying all the records and for Simplicity I've just added the edit because in the demo I'm going to show how to call the data then edit the data and save it then remaining for delete you will have to and update update I'm already showing so delete you have to do as an homework so this is a code that is tested simple code that I'm displaying let's run this code and see things in action so I'm going to just go to Shared also and in the layout I'll just add the link so that we don't have to type in the URL this page customers so here I am going to replace home with customers and index is the same and run the application now so let's click on the home button now so when you click on the button a method will be called and we should be able to see display the records that is there in a table I did not add a breakpoint but we can do that so you here we go we can see all the records added in the database and now we also have that edit so we need get the mouse over the edit you can see on the address bar below you can see it's the ID is displayed and the get details is also there so basically our details are getting fetched using the procedure so this code is able to fetch the record I'll put a breakpoint and call again now we will going to call get details let's go to our view and make sure that this maximum method is defined over there so now when you go to R here so here it says the action is already defined get details and it is parsing the ID so that means in our controller this method will be called let's put a breakpoint over here but we have not added the view for this yet so first I'll show you the get all code in a breakpoint mode and then we'll proceed in adding The View for the get details it is a simple line of code but I still like to run through the code so click on the home the moment I click on the home a breakpoint is called and here as as I mentioned I am calling get all customer this is a proced using database to list and then and it's got it iterates through all the record and then it passes That Into The View and we are able to see the record now let's go ahead and add that Details page so right click on get details and say add View selector is empty laser page just name it and get details okay so here I'm going to refer the model as customer entity and I'm going to paste HTML code here is the HTML code this is a simple UI I'll just walk you through the system record so basically we have created a div class and then we have used form because once you click on the submit button you are supposed to call this update details which is there in our customer contact this is update details you should actually update the record and put the breakpoint over here so this is the first line form method and then we have a div of summary validation then we have one div label and input for the name mobile email and the submit button here we have also passing input type in which is an ID so this is a UI let's go and run the application and this time let's click on edit and see the things in action so this time we are going to test a second procedure that which is an edit and which is fetch single record from calling the stored procedure so we click on home button so here we are going to click on edit for John and click on edit and you can see our breakpoint is called here get the mouse over ID you can see the ID is passed and this is a code that will fetch the single record it's the same as this code but here I am also passing the ID parameters and I'm converting that into a customer entity model so this gets called and this this is passed in the view and we should be able to see John record in it in a UI so you can see John mobile and email let's go and change the number so this is a number change and we will get back drawn to Yahoo domains as an at yahoo.com let's click on Save now when you click on Save I put a breakpoint will also go through the update method so when I click on Save so our update method is called here I am passing the ID the mobile number which is changed and the email now this is a param SQL parameter which is passing all the values over here and finally it calls the execute SQL raw database is execute SQL raw pass the procedure the parameters they added there is the procedure and the values operated values let's click on let's get the breakpoint next level let's get the mouse over customer updates it we can see it is one that means successfully updated and now it will redirect to our index space and we should be able to see the changes there so you can see John email address is changed and even the mobile number so we were able to call all the functionality we were able to do all the group operation we called the get all procedure that single procedure and also show you how to do an update record using calling the procedure now in your activity you should be able to also add code to delete the record from the database table using calling the stored procedure of delete so when you do delete procedure you have to create a procedure which will take an input as ID and then you have to write a query as delete from the table where ID is this so here I was successfully able to show you how do you call a procedure maybe it will be required in any of your scenarios in any any of the project where you need to call the procedure from the database and you can use definitely use this method to do do the same guys I hope you like the videos please do subscribe my channel share it with your friends and colleagues like all my videos and help me grow my digital Tech joint Channel thank you so much for your time

Channel: Digital TechJoint

Views: 9,824

Keywords: aspnet, aspnetmvc, aspnetcore, mvc, core, storedprocedure, entityframework, entity, calling stored procedure, calling stored procedure with parameters, asp.net calling stored procedure, csharp, asp.net connection string, razorpages, dotnet, visualstudio, entityframeworkcore, mssql server, mssql, database, learn asp.net, dotnet6, course, learning, web development, web development course, coding, tutorial, beginners, developers, learn coding, dotnetconf, microsoft asp.net, bootstrap, web developer

Id: J4Qv0bDPLDw

Length: 30min 2sec (1802 seconds)

Published: Sat Dec 24 2022