ASP.NET Web API CRUD Operation Using SQL Server Stored Procedures

hello everyone this is manoj welcome back to my youtube channel in today's video i'll discuss and talk about how we can use sql stored procedures in web api and also we will perform all crud operation like insert delete update select using stored procedures of sql server so first of all let's open sql server so here we will create a database and a table and we will perform all crud operations using sql procedure on that table meanwhile it is opening let me also open visual studio so for this i'm using visual studio 2019 so you can use whatever version you are having so as you can see this is the sql server window so for me i'm going to connect it locally so this is my server name for your machine it should be a different name so click on connect so it will connect your local database and in the object explorer it will display all the features whatever sql server is having so we can see this is the databases list once i expand it it shows all the list whatever i have created so far databases so click on new query it will open a query window where we will write all your commands so for this i'm going to use the master database so i'm not going to change it so let's create a table create table test employee f5 you can see command completed successfully let's try to fetch the table records so as of now it is a newly created table so there is no row so we have created our table now go back to visual studio so click on create a new project and from here we will select our template so in the search bar type web api so asp.net so this one we are going to use hp.net application this web api click on this click on next here we need to give a meaningful name so let's say stored processors with web api i'm going to change the location go to your sql server now we need to create stored procedures all right so let's say first of all we'll create i'm writing a list so select all select by id insert new record update existing record finally delete existing record so these five operations we are going to have using sql server store procedures in web api so our wheel studio project has been created as you can see the solution explorer so this is the default file and folder structure of a web api project and this is the default page let's close this so if you expand the controllers so we will find two controllers one is mvc controller and this values is api controller so we'll use this controller let's close this one so here so when you create a default project so by default you will get some pre-created methods so if you can see this api value this is the route for this particular method and this is a type of get so if you execute this so it will return these two values same way if you want to get a particular record then you have to pass a unique value here they have given an example with five post means if you want to insert any data into database then you need to post those values from the front end so it will land to this method and further it will go to database and insert it put means let's say there is an existing record and you need to update it so you need to pass that unique number and the values so it will go to database match this number with the existing records and update those values delete again if you need to delete an existing record then you need to pass a unique number and based on that number it will delete your data so that's it so now go to your models folder add a class give it a name employee here we need to create some properties so let's say prop and id same thing which we have created here the fields so there's a shortcut prop and then hit tab so it will create your property second one is name again prop and h final one is also integer type that is active you can see so we have created our employee class now go to values folder here let's create this employee object we need to include that model reference so click on this intellisense and use stored procedure dot model so now we have included our class over here now go to web.config so inside this config we need to create our connection string so here in system dot configuration so here we need to create our connection string and name let's say web api connection and then connection string first of all we need to give server what is our server go to your sql server click on connect so this is your server name copy paste it over here and now database so in our case we are using master so type master and as you can see i'm connecting my sql database using windows authentication so there is no need to provide username and password but instead of that we need to give integrated security true that's it so we are done with our connection string now go to your controller now we need to add some name spaces so first one is using system dot data using system dot data dot sql client save so now sql connection is equal to new connection configuration let's add another namespace using system dot configuration so now configuration manager dot connection string dot connection string here we need to pass the same name which you have declared inside your connection string so copy this paste it over here so we are done with our sql connection so now first of all let's try to create this post method as of now there is no record because this is a newly created table so let's create a record so instead of this default parameter give employee class employee and object employee here if employee is not equal null so create a check over here then we need to define our stored procedures data so go to a sql now let's create a new procedure so create proc usp add employee so we need name age and active so at the rate name where care 100 at the rate age end at the rate active and as begin and end so this is the default structure of any stored procedure so here we will write insert into test employee name age active values at the rate name and the rate a age make sure the sequence otherwise it will insert the wrong values so we are done with our create procedure select this click on execute you can see command completed successfully so now come to your visual studio here we will use sql command cmd is equal to new sql command comma connection object and copy your procedure name pass it here this connection is coming from here okay now cmd dot command cmd dot command type is equal to command type dot stored procedure cmd dot parameters dot add with value so here we need to give the name of sql parameter so copy this at the right name comma now we need to provide the value so from here we are getting value from the employees employee dot name so let's copy this and paste two more times one for edge and one for active and here change the properties all right now connection dot open we need to open that connection and end i is equal to cmd dot execute non-query so it will execute our command and finally con dot we need to close the connection save so now change that return type because we need to give us message to end user so if i greater than 0 it means if the record has been inserted then return data has been inserted else return error save okay let's try to give away with let's say message return message so save this so we are done with our add web api method so let's save this now try to put a breakpoint over here and try to run this project and meanwhile it is running let's open postman because we will hit this api method through postman so this is the postman interface if you don't have postman go to google and install it let's try to this one okay so you can see localhost colon 44364 so this is the default url of your project so we need to copy this go to postman let's try to change that request type so that would be post and after that we need to give api slash values now go to body raw change this json and we need to create a json so first property is name so let's say i'll provide my name age that is integer active that is also integer one so this is my request yes the api has been loaded now minimize this and go to your postman try to hit it so now you can see the control has been landed to this post method and if you hover over this employee so we are getting our data so active1 is 19 and name is manos deshwal we don't need to pass id to insert a new record because that is auto incremented so let's try to debug this perfect if i continue this so you can see data has been inserted now go to your sql table and try to fetch all the records so you can see the data has been added through our stored procedure go back to your visual studio so you can see this is a stored procedure which we have written like this and we have posted our data from postman so instead of postman you can use your any front end like react angular or jquery an index or html form all right but just for reference i'm showing this one so let's stop this solution so we have done this new record now let's try to create the select all go back to wheel studio here so instead of this let's try to create a list of employee and here we will use again stock procedure so go back to sql server now so create a stored procedure so create proc esp get all employees so we don't need any parameter over here because that only fetch all the employees and return back to the api so inside this just try to write select star from test employee so execute this procedure precision has been executed copy this name come back to see visual studio now sql data adapter da is equal to a new data adapter so for this we'll use state adapter comma connection now we will also use data [Music] table data equal new data table now d a dot fill dt so data adapter will fill all the records into that data table so let's try to make any check so dt dot rows dot count greater than 0 if there is any single record then we need to fill a list create object equal new list so here employee emp equal new employee emp before that we need to create a for loops for n tie equal 0 i less than dt dot rows dot count and i plus plus so inside this we need to create a new employee object every time so emp dot name equal dt dot rows i name dot tostring so same way let's copy paste four times so employee dot id so id is integer so we need to convert that to end 6 32 id same way let's copy this one because we the other two fields are also integer age active oops edge so copy paste so once we have assigned all the data then list employees dot add new employee and pass this object employee so we are ready with our employee list so let's make a condition over here if list employees dot count greater than zero it means there is any single record which is coming from database then return json [Music] we need to convert that into json so json convert we need to add a namespace newtonsoft.json serialize object list employee return list employee else return null save so we are done with our get all methods similarly let me copy this entire part and paste it over here and we need to change our return type from string to employee so here we need to pass one id we also need to pass da dot select command dot command type equal to command type dot stored procedure let's copy this one also and here we need to create a parameter d a dot select command dot parameters dot add with value so here at the rate id comma id so now save this and here we will only get a particular employee not more than that so we don't need to create a list over here delete this one we also don't need this no need to add employee is not equal to null then return employee and instead of high we need to pass zeroth index because we will only get a single record from the database so we are also done with our get id and get all forget all we have created our sp so let's copy the same and paste it over here let's take it or get employee by id all right so here we we need id column and in the command we need to type where id equal at the rate id so save this and execute your command now copy your procedure name go back to visual studio so here not here pass it over here save so let's try to run the visual studio project and go back to your postman so click on a new tab so here we will use our get request so let's copy this so the web api project has been loaded now go to postman and try to hit this so you can see there is only one record so it is displaying one record let's go to this post and change we're not calling send data has been inserted go to your get method click on send so you can see we are getting two records perfect so now we have tested our this method get now try to test this get by id so if i pass let's say one so it will display only first record which is manoj's wall and if i change the id to 2 then it will display only the record number 2. let's also try with the sql server if you fetch all the records so id 1 is manoj id 2 is virat kohli so we are also tested our get by id so now we need to create delete and update method all right which is put so stop the solution so here let's try to copy this copy and paste it over here and instead of add we will use sorry update employee and here we need to pass employee object and here we also need to pass id which is coming from here so id name h data has been updated written message so change the return type to string perfect same way copy this and to this delete from update to delete employee we don't need that this check over here and also remove all three parameters employee has been deleted this save and control d it will align your code so control k d is the command so we are done with our web api part now we need to create these two stored procedures so go to your sql server and create proc update so for update we need all four parameters so let me copy all parameters and so id name h active as begin and end so update test employee set name is equal to add the rate name age equal at the rate edge and active at the rate active where id equal and the rate id so execute this one and let's copy this sp paste it over and now we need to create the delete sp so this will only take id as a parameter so change the command so delete from employee where id is equal to id so execute this one also let me give the comments so that is for select all and this is for select by id this is to insert a new record this is to update and this is to delete all right now let's execute this oh we already executed that now go to your visual studio now print the project now we will perform all five operations through postman so api has been loaded go to postman and in your post let's say we need to update go to sql server and try to fetch all the records so let's say for manoj we need to change the age all right so go to postman and let's say make it is 31 and we need to pass the id so put it 1 send so data has been inserted sorry that is my mistake we need to change the type put so if i execute this so we'll add three rows no problem so now if i execute this as of now the age of one which is 29 so if i execute this so data has been updated and if you execute this so the age has been changed from 29 to 31 the name is also has been changed someone was visual age let's say 32 and let's make it active zero send it has been updated if you execute the command so manageable320 so this is how you can your update your data now the final one is delete so select this one or go to a new tab copy this paste it over here and delete let's say if i want to delete this record the third one so i only need to pass the id 3 click on send data has been deleted if you try to access the data let's say without any id so it will give you all the records so we are having only two records let's also check in the sql server execute so two records are there only so this is how you can use your web api with stored procedures all right so get get by id a post update delete all right and this is how you will create your store procedures so this is about today's video i have already created uh many videos on web api sql server stored procedure react.js c sharp jquery in on my channel so if you want you can go to my channel and view all of them also if you are having any kind of problem with your project if you are a student or if you are doing an internship so i am doing paid services for the project so you can connect with me on my instagram or you can go to my channel there you will find my all contact details so by that way we can connect one to one so this is about today's video i hope you like it if you did so hit the like button share comment and if you haven't subscribe my channel so i request you to please subscribe my channel i need your support and help so i'll see you in the next one thanks for watching take care bye

Channel: Manoj Deshwal

Views: 39,805

Keywords: CRUD Operations Usin Web API and Asp.Net MVC, Asp.Net Web API CRUD Operations, Asp.Net Web API Beginners Tutorial, web api - insert update delete, web api crud using entity framework, web api in asp.net mvc, consume web api from asp.net mvc project, web api - get post put and delete example, crud operation using web api and mvc5, integrate asp.net mvc and web api, using SQL Server Database, web api crud mvc, web api entity framework crud, web api tutorials, asp .net web api

Id: DboyInxNgXc

Length: 33min 37sec (2017 seconds)

Published: Mon May 02 2022