.NET 7.0 Web API CRUD actions using dapper and SqlServer

today we can talk about how to use Dapper in asp.net core baby APA for the implementation we can use the framework is.net 7.0 the functionality point of view we can do the complete credactions okay so before going to the implementation we can see what is Dapper so Dapper is nothing but an worm object National mapping it is responsible for mapping between database and programming language okay we have lot of orm availables even my previous videos also I have used Entity framework core that also a kind of worm so if you comparing this Dapper with other orm the data retrieval is somewhat fast okay and the concept of dapper is it extends the IDP connection interface so in order to we can use some extension method so using that we can execute the xql queries and also the store procedures okay so now we can start the implementation so parallely we can see what are the extension methods are available okay so let me create one new application new project we can choose this template and the project name let me give Lan damper so we can have this framework is dotnet 7.0 so let me create this one okay our application is created next we can install the required packages so let me go to the nuget package manager so in this browse set we can select dapper so the latest version 2.0.138 is available we can install this one okay the installation gets completed so next we can install the SQL client okay so here we can see this Microsoft and also system dot so let me choose this microsoft.data.sql client okay the installation gets completed so next we can create one new controller so in this APA side we can choose this empty controller okay so let me provide the name is employee I'm already having one create a database so in this Lan DB we have this table is called table employee so this table only I'm going to use it is having five columns code name email phone number and a destination okay so here this code is nothing but a primary key and also this is the identity column so now let me go back to our application so here I'm going to create one model class so let me create one folder the name of model here I am creating one model class so we can provide the name is employee so next we can declare our properties so since we know our column names so let me include one by one next let me include the name so similarly we can include the other columns also okay email form added destination work in our model is ready so next we have to include the connection string in our app settings.json so connect some string you can provide that the name is connection okay so the value already I'm having it is Handy so the first one is server name then our database we are using this land DB and The Trusted connection is true and also trust the server certificate also true okay so let me save this one next we can create our database context okay so we can create one more folder I'm going to provide the name as data so instead that I am creating new CS file for our DB context dapper DB context so here I am going to create one Constructor so there we can inject our high configuration okay so in order to access our connection string and the next let me declare our connection string so here we can assign the value for our connection string so this dot underscore configuration dot get connection string off so here we have to provide our connection string name okay so that is available in our app settings.json next in our Dapper basically it is extend the idb connection interface so we can use this one idb connection new SQL connection of here we have to pass our connection string okay so now our database context is ready so our DB context is ready so the next thing is we have to register this database context in our program.cs file so in our service side so Builder dot services dot add transient we can use our Dapper DB contest okay this is fine next I am going to create our post class and also the interface so let me create one more folder repo and here we can have the classes employee repo and also one interface okay this is fine so in this interface I am going to declare our first function so list of employee so this getter okay and in our Repose class we can implement the same interface I imply report we can implement this interface here and the next thing is I'm going to have one Constructor here we can inject our database context okay and before implementing this Catal function we have to register our reports and also interface in our program.cs file after this Dapper DB contains we can use our interface imply report and the implementation class okay so this is also done so now let me focus on the implementation of this guitar method okay so first what I'm going to do let me create one query select start from table and blade so this is our table name yeah next we can initiate our connection so this dot context dot create connection so here I am going to declare one variable where employee list and we are doing this as synchronous programming so let me Mark this functional so a synchronous and we have to use this update so now connection Dot Korea synchronous and our final response is I mean the response model is employee so here we have to pass this query also okay so once it is done we have to return this symbol list also we can convert into list okay next let me move on the controller side here also first we can have one Constructor okay so next we can inject our interface here I imply wrapper and let me inject this one okay this is also done so next we can create our first action method so the method type is get method so the name is guitar and we are doing the synchronous programming so let me Mark this into a synchronous so inside the task we can use the I action result interface okay and here I'm going to declare one more variable so this Dot repo dot getter and here we can check underscore list not equal to null if this is the scenario we can return as the our list maybe we can use Servite from here and let me remove it okay so in this CLC scenario we can return smartphone so we have done our first method so let me run this application and we can verify so if it is working we can focus on the other functionalities okay okay it is working fine without any error okay so now let me focus on the other from sure it is so let me go to our interface side and the second from sneeze get by code so it is returning only the object is okay and it is having one input that is code and our next function is create so it is having the input is server employee object and the return type is string and the fourth functionality is update so it is having this object and also the key value and the response is same only and the final one is delete or we can use a sir remove so it is having this code as our input and the response is string only okay so now we can Implement all the interfaces in our request class so the potential fix Implement all the interfaces okay so first we can complete the easiest one get by code so already we have this get all method so we can copy the same item so let me Mark this into asynchronous and in this query we can include where condition code equal to H code okay and now it is not a list this is just object only and since it is returning the single object so we can change our extension method query faster default asynchronous okay and also we need to pass the parameter for that we can use as the object at the next easiest method is this remove function so let me copy the same code and here let me declare one more string variable that is response so the default value should be string dot empty so our query is delivered from and table name we are passing this code add the extension method we can use as the execute okay so since this is the synchronous method we can use the xcu dancing runners and let me Mark this also asynchronous okay and this response is nothing actually so once the execution is completed become set the values for this response as pass at the end we can return as the response okay so we have completed three functionalities so next let me move on the create function okay so we can copy the same code so let me include it here and the first we have to form our query insert into our table name so here we have to mention what are the fields we are going to insert the data okay so the first field is name because the code is auto generated field the next one is email then phone at the final on this destination so in this values we have to pass our parameters let's name email headphone destination so next we can create the parameters okay new Dynamic parameters parameters dot add name and our value implied.name then DB type so the type is stringed okay so this is our first parameter so similarly let me include next three more parameters email then form and finally designation for the execution we can use the same execute asynchronous only first we pass this query so after that we can use the parameters and we can mark this function into a synchronous okay so this is all about the create function so we can copy the same code so let me complete the update function also it is in the end so here the parameters are same so additionally we have to include the code also so let me include this code or else we can use this Direct Value and let me change this query update table employee sit name equal to at name and the next email equal to email then phone equal to platform where code equal to that code okay we have done our query similarly we can mark this function also a synchronous that's it because other things are same only so we can label this application if there is any error we can resolve first so so far we are good so let me focus on the controller set okay so after this get all method I'm going to include this getby code function so our parameters code and the input is integer code and this function we can use this getby code and also I'm passing this parameter okay and other things are same only so next hour a create method so in this career let me change this into HTTP post and the input is we can use the from body and our employee objects okay and create just passing like this and in this response we can directly use it okay no need to check anything so the same way we can apply for our update method also so first we have to change the name and second thing we have to change the method type instead of post we can use the put and here we have to use one more parameter so in this update function we have to pass this code also okay and the final one is to remove so let me provide the name is streamer and it is having only one parameter that is our code only and instead of this foot we can move this into delete so now we are good to go so let me run this application we can verify one by one so here first I am going to execute this guitar method so here let me take this code 1043 and also we have this one zero four seven also okay next in our Gateway code method we can verify so I am getting the cemental object in case if I'm providing one zero double four I am getting this not found response so if I'm providing for 7 it will work okay so next let me create one new record we can remove this code so name is so phone number I am providing some values so this destination let me provide a certain okay so if I'm executed I am getting the responses fast so that means I believe the record is created so anyway we can confirm I am going to execute the kettle method once again so at the end you can see the new record okay we have provided the same data only so a new record created with the name of one zero seven zero so let me copy the same object so using that we can verify our update function so we can replace the same object so instead of here let me provide us here are the keys and then we can replace our official email here are the key set gmail.com and the phone number at the end I am provider is one okay our object is fine and also in this code we can use this one zero seven zero so let me exceed this one so we are getting this pass response okay so let me go back to our gate by code function see our new values are updated okay so that means our get function get by code create and updates are working fine so finally we can test this remove also I got the past response only so it might get backward I'm going to use the same one zero seven zero see now I am getting this a not found because the record got removed okay so now we successfully completed our credactions so next I am going to explain how to use the store procedures as of now we have used the direct SQL queries so instead of that we can use the procedure okay for doing this implementation I am already created one store procedure so this is my procedure SP underscore get employee payroll so let me execute this one it is returning some data okay now in our application side basically two ways we can execute the procedure one is the command type we can use as the procedure and another type is the default one texture okay so anyway let me cover the both ways first let me create one function in our interface side after this get all function we can provide get all by role now go back to our implementation class so there we will get the suggestion and whatever code we have used in our guitar function so let me copy and reuse it here so here we are executing like that our procedures so let me copy the same thing we can replace this query so instead of this thread meme let me provide a troll okay and here we will get one input swing rule so the same thing we have to apply in our interface also otherwise we will get an error and we can mark this function into a synchronous so after this query so let me pass this parameter and finally we can create one new method in our controller side and get all b-roll and it is having the input string roll here the function is get albed roll so let me pass this parameter also so we can run this one so we can provide the same value as admin see we are getting the response okay and in this table we can verify what are the other roles we have okay we have this dou and sub admin developer so let me provide say I have only one record and a sub so for that also I have one record and developer yeah for the developer we have the three records okay and this is the normal implementation the comment type is text only so now let me change this into our common type is store procedure okay so first let me copy everything and also comment the existing item so once I uploaded the source in the GitHub you can use this for the reference okay so in this query point of view we can use the procedure name only and we are passing this query query is nothing but our procedure name and we are passing our parameters role so finally we have to provide our Command type our Command type is store procedure okay this is the change we have done so let me rerun the application and verify it admin see we are getting the response okay so if you implementing just I am getting the CMT array okay and this dough I am getting the single data so that means our functionality is working fine so now we are in the end of the video so I hope you got some better idea about this Dapper and the executing queries and store procedures so in my next video we can see one of the interesting topics so now we are in the end of the video still if you have any doubts or clarification please post in the comment box and also please don't forget to subscribe my channel thank you thanks for watching

Channel: Nihira Techiees

Views: 13,204

Keywords: .net 7 web api crud actions, .net 7 web api crud action using dapper, dapper in .net core, how to use dapper in .net core, .net core, dapper extensions methods in .net core, nihira techies

Id: wErQH7foJIY

Length: 29min 42sec (1782 seconds)

Published: Fri Jun 16 2023