.net core web api crud with mysql & dapper

hello there everyone it's rindra here in this video we will create net core web apis along with my SQL database and I already have created this video in the past but that time I have used EF core and in this video we are not going to use EF core and we will use Dapper instead of EF core in this video If you find this video helpful then please hit the like button make sure to subscribe this channel to get more videos like this so let's get it started without any delays so first and foremost we will create a database I am inside the my SQL workbench it is a default editor that is installed with my SQL 8 and here we will create our database so just going to write create database person DB and here just execute this guy and now we use this dat database and inside it we will create a new table so I'm just going to write here so it is a table we are going to create it have just three Fields ID primary key with auto increment name uh it is a warar 50 and another one is email which is a Vare 50 also and the both of fields are not null so I'm just going to execute these things Okay so I have done some mistake here so now we are fine and yeah we have created this table and we can just check it like this select as from person so just execute this guy and you can see that we have some data here sorry we have a table without any data here now we are going to use this database and this table in our net core project so I am here inside the visual studio sorry it is not a visual studio it is a visual studio code editor and one thing you have to do is you have to install this package sorry extension C devkit extension okay so now we can create uh net core project from here so I'm just going to click on this create a net project and here we have various projects so I'm going to go with sp. net core empty so just click on this guy and here I am going to select some location so I'm going to go here inside the projects and here net and let me choose yeah this one okay so here we are going to create our project so it will take a little bit time and here something will pop out okay that thing popped out so here we will name our project so it will be net my SQL Dapper but yeah it's a weird name but it will make me easy to identify my projects in repository GitHub repository so we will go with this name and here press enter okay so when you create a project you will see here it is our project but we have installed C devkit extension so you are able to see this solution Explorer here and here we have this one project so it is just a blank. net code project so here we will create one more project so I will create I will click on this plus sign here in the solution Explorer and here I will just click on this guy and and here I am going to create a class Library project so it will be our data access so I'm just going to name it dot yeah just name it data access that's it and default directory it will create a new project inside this solution yeah it has already created so you can see that we have this project and I'm going to create this class 1. CS file and now what we will do here we will reference this project to our API project so here you will see you will see some option add project reference yeah and it will be referen to data access now we have referenced our data access to this API project now what we will do here the first thing we are going to create a connection string here so let me a little bit zoom in okay so here connection is strings and let's just name it default and here we will Define our connection string so first thing will be our server and that server is Local Host next thing is our database which is person DB and next thing is uh user ID user ID and user ID will be root and password equals to it is my password so yeah that's it so we have successfully created our connection string here now we will install some packages so go here in the view section and click on this terminal it also have the shortcut here which is control+ this ptic sign so just click here and we need to change the default directory so CD it will be data access where we are going to install our packages so first package we will install do net add package it will be Tapper and next package will be let's just finish it okay so next package will be my SQL do data okay so next package will be my SQL do data and previous one was Dapper and the last package we are going to install this one Microsoft do extensions do configurations sorry not s do configuration do abstractions okay so just click here and it will be installed shortly so this package is needed for accessing our app setting. Json file because we want to access our connection is string in this Library project so we need this package for that okay so we are done with installation and now I can come out of this repository sorry this folder okay so we are done here so what we should do now we will create a folder here add new folder and it will be models and inside this folder we will create a new file so it will be a class and let's name it person okay now it is created and here just change it to name space data access do models okay now let's see what we can do now let's create some properties here so just type here p o p double tap and ID next thing will be string it will be name next thing will be email so we are going to validate these data for that we are going to use required qord here and re ke here also okay let's make one more validation here let's just name it max length and 50 and same here okay we can validate one more thing here it should be uh email so just type here email address now we have done pretty much good validation here okay so it is not label property max length will be 50 it is also not an label property and it will have email address validation and it max length will be 50 okay now we will create one more folder here in the data Access Project so if I click here you can see that we have the Cs project file and it will Define which package we have installed so we have installed three packages Dapper my SQL do data and the last one is microsoft. extensions do configuration. abstractions okay so let's close this guy and here we will create another folder let's name is repositories inside this repositories we will create uh one class and it will be person repository that's it and here we have to change this name space to repositories okay so that's it now here we will create a Constructor CTO and let's remove this guy okay now we will do some configuration here first of all we need uh I configuration and it will add this line here I configuration config it should be private read only next thing we will need uh string type conent string so I'm just going to type here connection string okay and we have to type it here also I configuration config and here we just write config equals to to config and connection connection strings equals to config do get connection strings default okay so we are done with the connection string section now what we will do here we will create few methods so first one we be public as sync and this one will be task I person sorry not I person I enumerable I enumerable person get people I think okay it is a PID name okay now here we will create uh one private method private idb connection get connection get connection and here it will return [Music] idb sorry using idb connection and we are using this keyword using because it will automatically open the connection and it will automatically dispose the connection so we do not need to worry about disposing the connection private no using idb connection connection equals to my SQL client sorry I guess it is something like new my SQL oops it should be small SQL connection okay and here we will pass the connection string so that's how it will look and we will return this connection okay so now here we'll write our connection equals to get connection and string ql equals to select ID comma name comma email from person it will be our query here we will just write connection dot execute no SQL query s sync query as Sync here we will pass person here we will pass equal query and that's it and it should be were people equals to await now we will return this people Okay so it is get people sing and it will be get people by ID sing and here we will pass ID okay so here we will write where ID equals to ID that's it it should be the same ID and here new ID that's it so it is our get people by I think no it is not it will return object not uh numerable so here should be query first or default asnc now we are good so is our get people iing get this and now what we will do here we will create another method so I'm just going to copy in this line and and it will be create person asnc and here we will just pass person person okay so now here we will just write string query equals to insert into person name email values equals to name and email okay now connection equals sorry not equals to here should be a wait connection Dot execute scaler asnc and here we will do one more thing after the semicolon we will execute one more line so it will be select and select okay last insert ID okay so it will return the recently created identity value now here we will pass this query and here we will pass some data so it will be new and here we will pass person dot oops it should be small person person do do name person. email okay since we are using two parameters here name and email that's why we are passing it like this it is anonymous object person. name and email since we are using name here that's why we are using name here we are using email here that's why we are using email here that's how the parameter works in the dep okay now let's just see what should we do now and yeah last thing we are going to do is we are going to store it here so string No it should be in int ID equals to this this this let's just name it it created ID and here we will pass ID sorry in so now we have our created ID which we have recently created what we can do here we can just do person do ID equals to created ID and we will return this person object okay so we have passed this person object which do not have any ID and here we have inserted a record and got the ID from that record and we have stored that ID in the created ID field and here we are just updating person. ID with created ID and we are returning it here so it is our create personing next thing we will do is update so let's say name name it update person s sync so update query will be different and everything will be different here so yeah right now we will just do update and name of table person set name equals to name and email equals to email where ID equals to add theate ID and now we will just write here a wait connection. execute snc and here we will pass query and we will pass object person so this person contains three Fields ID email and name so we can pass this whole object because it just have three fields and these fields should be same with these fields name email and ID and those fields must be present in this object and those are presents you can see here we have same field here ID name and email and those fields should be matched with these fields name atate name atate email and at theate ID otherwise you will get errors now we have done our update person iing the last query we will write is for delete so just name it delete person I think and here we will just pass ID int ID just write delete from person where ID equals to at theate ID and here new ID so this field and this field should be matched ID and ID so we have deleted our person that's good the next thing we will do here is we will create a new file and it will be a interface and I it will be I person Repository that's it should be named with repositories and here we will do something so let's it shrink now let's copy this thing and paste this thing here okay now now we'll do the same so I have copied all the names 1 2 3 4 5 so all these five methods should be present here and I have copied it from here now what we will do we will inherit this interface here High person repository and we are good now what we will do we will just go here in in the program.cs file and here we will just write Builder dot Services dot add what it is ADD transient add transient and I person repository person depository that's it it should be like this okay so we have successfully added these services to di container to use the dependency injection we have to write this line so do not forget to include this line okay and here we are just us using the minimal apis but we will use the controller based apis for that we will do some configuration let's see just run this project so let's see how it is going so click on this debugger and here we will see run and debug so we are just going to select this Net 5 and. net core and it will not work so we have to click on this create a launch setting sorry create a launch. jsn file click on this Net 5 plus and net core and it will create this file so it will create this file you can see it here somewhere herea here launch. Json file okay so now we have created this file just click on this run Icon and it will run our project so right now you can see this hello world here and we are not going to use it like this okay so here we are going to remove this line and you can remove or you can leave it like this it's totally up to you so I'm just going to comment this line okay now we have to do little bit setting here first thing we are going to call builder. services. add controller add controllers and here we are going to map controllers now we will be able to use controller based API for that inside the MySQL Dapper project it is a API project and here we will create a folder it will be controllers inside the controllers we will create a new class since controller is a class so we are going to create a class and let's name it person controller that's it and here present enter okay so our class has been created let's change it to controllers okay public class person controller now it will inherit a class base controller sorry controller base controller base and that will include this package here or name space Sorry in C is name space okay so one last thing just type here route and our route will be API SL people that's it and here we will write uh controller methoding I have created this sniffit so it is not a default feature of Visual Studio code I have created this sniffit for creating a method so let's name it uh get just get okay and here oops and here type HTTP get just write here hello and let's see our apis are working fine or not so I'm going to run this project and here we will write something API SL people and you will see Hello so our apis are working pretty fine let's close this guy we do not need it okay now we'll create all all the methods here but first we will create we will inject our repository here so so it will be private read only and I person repository person repo that's it and we will copy this thing in the Constructor so just create a Constructor here c r double tap and just copy this thing here person repo that's it so add one line here person repo equals to person repo and here one more thing we're going to use the default logger of Microsoft so it will be person repository and it should be like this logger okay and here logger equals to logger oops just copy this guy here and paste it here logger now we are good okay so here we'll just type try catch okay just return okay and here we will write exception ex and logger.log ex. message it should be log error and and here we will written status code status codes c o d s it is NM dot 5 internal internal 5 server whatever it is internal server error and we will also pass Anonymous object here which will contain status code equals to 5 and message equals to ex. message okay written status code not context okay so we are fine now and here should be a semicolon and now for people equals to A wait and person repo dot get people asnc that's it so it is our get method now we will create few more methods so here just copy this guy and paste it here and we will change it to like this it will also accept ID and here we will also pass ID okay and it will be person not people get people by idsn we will pass ID and we will get a person here we pass this person here so we will check one more thing first I'm going to copy this line okay we will check whether it exists or not so if person equals to null then then we will return not found exception and here we will pass this object and here person does not exist that's it oh code should be 44 okay now we'll pass we will check if a person exist or not here if it does not exist we will return a 404 not found status along with this data otherwise we will pass this person and here we are just Lo the exception okay now what should we do just copy this line and paste this line here and it should be post HTTP post person create person sing and we will pass person as an object person person it should be created person so we will get a newly created person and we will pass it here and press control dot here and and click on using data access do models so now we have successfully created it but we will we will return not okay we will return the created at action and here we will just pass name of post that's it so it will return return 2011 status code it means created a resource one more thing here we will use HTTP put here put so it should be httv person and update person I think we will pass a person and it will not return anything so it's just going to pass it over wait and here wait yeah that's fine oops we already have a weight here so here we will just pass no content so it will return a 204 but here we will also check if person exists or not so I'm going to copy this line here so let's name it existing person existing person and here we will pass person. ID that's it first we are checking if person exist or not if it does not exist then we will WR not found otherwise we will update a person here oop sorry where it is yeah I have copied it in the wrong place sorry I have messed with it I'm just going to copy this guy not this guy let me copy this guy from here to here okay now we are fine and we will paste it here and it will be person. ID okay so okay what is is going on here first of all it is our put API and here we are checking if a person exists or not first we will find the person if it is null then we will return this not found uh not found along with this object and here otherwise we will update our person and we will return no content here it is just uh acception handling so we have one last method remained so so it should be like this HTTP delete here we will pass HTTP delete and here we will pass ID okay now we will check if a person exist or not but first it will get an ID as a parameter and here we will pass the ID first we will check if person exist or or not uh if it does not exist we will move we will return return not found and otherwise we will delete this person so person repo not delete person snc and here we will pass the ID so we have created our all the five methods so now we will test them with Postman and before running this project I have noticed something that I I haven't passed anything to this okay object sorry this okay method so we need to pass this people here I hope we are doing good so let's run this application so just click here and here so our project has been started so we will move here in the postman and here I going to test this endpoint so it is a get method and here we are calling this endpoint point so press on this send button and let's wait so here we have two status code and we have blank current that is because we do not have any record yet so we will create we will create a new record here and I'm going to open the new tab here and here I'm just going to post paste this endpoint and we will use the post method and inside this body we will change it to Json and here we will pass some data so let's pass some Json object we do not need to pass ID so we'll just pass a name and name will be John and email email would be John azma.com so let's hit it and let's see yeah and here we have the error column name cannot be null and I wonder why we are having this column null so let's see where where is our post method here so let's put a break point here and I'm going to hit it again and so we will stop here okay now I can check that what we are getting inside this okay so we are we are not getting anything here sttv post supp post method let me think what is happening here okay I have done some research behind the scenes now I'm back to the recording so what I was missing here is I was not using API controller so that thing I was missing let's run this project again and let's see so now let's go here and let's post this data let's see do we have any break point here and you can see that we have our data so let's remove this breako and let's run this guy so we have created this object ID 1 which is autogenerated name and email now let's create another record here let's put my name here so yeah so this one this one also has created let's create another record here and it's with ID3 so let's go here and let's run here so now we have three records if I try like this it is a third record and if I pass four so here you can see that we have 404 not found and here is the status code also 404 not found so let's do one thing here and let's try to update this record so I'm going to put here this put method and since we are updating the data so we need to pass the ID and that is three and here let's name it mik and let's change its email to mik gmail.com okay so 204 no content it means we are fine here so if I remove this guy you can see that we have three records third one is mik Mike gmail.com we can also check it like this so one last thing has remained we need to check this delete end point so first let's check the fifth end point and you can see have 404 not found error here so let's delete the third record and you can see that here we have 204 no content means we have successfully deleted our data now if I run it again you will see that we do not have this uh this resource anymore so let's go here to the get end point and you can see that record with ID3 does not exist any more and here also so we have successfully tested all the end points and that's how we can create our net core applications with my sqls and using Dapper so that's it for now see you next time

Channel: Ravindra Devrani

Views: 893

Keywords:

Id: iceJBFcWuM4

Length: 48min 59sec (2939 seconds)

Published: Sat Sep 30 2023