Dapper tutorial with dot net 7 apis | .net core api crud

diaper is a micro orm you can use it as a replacement of Entity framework the major advantage of Tapper is performance it is much faster than Entity framework and if you prefer a database first approach and if you prefer to work with stored procedures or queries this framework is a good choice for you some of my viewers asking me to use DB first approach in dotnet core so it is the video for those suggestions if you don't know me my name is ravindra devrani and my goal is to convey my knowledge to all of you people so in this tutorial we are gonna use Dapper as an orm and we are going to make a project with dotnet core apis where we are going to perform all the crud operations and if you find this video helpful then please hit that like button and let me know in the comment if it is helpful or not and feel free to put any suggestions and I will consider those suggestions in the future videos if you haven't subscribed this Channel Please Subscribe it also and one more thing that I want to say that it is the official documentation provided by dapper in their GitHub page so feel free to check this link github.com slash Dapper and here we have the official documentation of Tapper you can find all the documentation related to Dapper and it and I recommend you that you should definitely check this documentation it is very well documented okay so let's create a new project click here in this icon create a new project now we are gonna select this one asp.net core web API and if you are not able to find it here you can search it here web API and you will filter the result and let's just select this one asp.net core web API click on next and let's rename it to Dapper demo and I'm going to change location so if you need to change location you can change it and it is our solution name and here We Are Gonna Change it to wrapper demo API and we are gonna change the solution name because we are going to add multiple project in this Solution that's why I am keeping the solution name and project name different okay so let's click on the next now here we will have multiple solution like this framework we're going to choose the latest one dot net 7. you don't have the latest one you can use dotnet 6 also that is also going to work fine we will not going to implement any authentication type not going to implement Docker just take this configure for https and select this option use controllers if you want to use minimal apis then you have to deselect this option we are going to use the old way of creating apis with controller and enable open API support if you want socket Swagger documentation and that's a good that's a good thing so we are also going to enable this one also let's create our project okay so our project has created I'm going to close this tab and let's run this project so it is some boilerplate code that is provided by Microsoft and if we click here and try out so we will simply get this result okay we are not gonna do this thing so I'm just going to close that tab since we are using Dapper in our project so we have to create database first so open your Visual Studio sorry not Visual Studio open your SQL Server management Studio and let's connect this thing okay now we can click here in New query or query so that we can create a database with the command so let's create a new database create database Dapper demo let's press F5 or you can click here in execute okay now our database has created now use this database so type here use database press F5 now we are inside that database so either we can you we can press F5 to execute or we can execute from this icon here where I am hovering the mouse okay now we are inside this database and you can see it here we are inside the Dapper demo or you can directly select it from here also but with command it is much fun so let's create a new table create table and let's just name dbo Dot person and you can write it without TBO that is totally fine dbo dot person ID int primary let's let's write it in the next line okay ID int okay first of all I'm gonna zoom in so that you can see it clearly ID int primary key identity now what it is going to do it is going to create a field ID with primary key and with auto incremented array it means we are not going to enter any value inside the ID It Is by default starting with 1 and whenever you create a new row it will increment the previous ID and store it in in it so when you create a first row it will create ID with one on the second row it will create idv2 then in the third row it will create idv3 and so on now name and work 30 30 will be enough not null this field is not going to be null email and worker and where care or watcher whatever you pronunciate it and where care 50 it is also not null so that is our table okay fine so let's execute it and successfully completed that's good so let's insert some values to it insert into person name comma email and values so first one will be John and email will be gone at the red rediff.com and another one will be second record is me ravindra ravindra let's just say ravindra at the red xyz.com okay let's execute it and here we have some error I guess there is comma okay it is fine now so I was missing this comma here that is fine now we have two records inside this person's select asterisk from person okay now we have these two records with ID one and two and we haven't entered these ID as you can see here okay so that is it with the database part now we are going to move to this Visual Studio okay now what we are going to do first we are going to open this app setting dot Json file here we are going to define a connection string now we are inside the file so press or press a comma key here now we will write the connection strings and here is our connection ID it is default inside this string we are gonna create a connection string so inside these double quotes we will Define our connection string so first thing will be data source data source equals to and open this thing here and you will see if I disconnect it here and connect it again so you will see this server name it is our data source so just copy this thing from there and paste it here so it is our data source or server name so I'm just gonna close it I don't need the SQL Server now data source equals to Ravi and d r a it is our data source now initial catalog and it is the name of database that we have created earlier and that was stepper demo so it is the name of database okay I'm gonna remind you once again it is the name of database not the project name or solution name now let's enter here Integrated Security and we write this line so that we do not need any password or any credential like username or password so Integrated Security equals to true now in Crypt equals to false and since I am getting some issues in dot Net 7 that's why I have to add this encrypt thing here okay now we have defined our connection string so you can see it here so here we have this connection strings then it is our connection ID which is named default here or data source which is the name of server it is initial catalog it is a name of database that we have created earlier and Integrated Security true encrypt false and make sure it is a semicolon not a comma okay so it is separated by semicolons okay so now let's create a folder here and sorry not a folder sorry sorry my mistake let's click on this solution and right click here and click on add new project I'm gonna show you again right click here in the solution and click on this add new project and we are going to add a new project here right now and that is going to be a library project so it will be our class Library so just type here class library and we have to select this class Library a project for creating a class library that target.net or dotnet standard okay now let's click on next and it is are Dapper demo data click on next yeah it is framework 7.0 that's fine click on next and it will create a project for us so here we have created this new project let's click this close this class one and let's minimize this and go here so we are gonna delete this file we do not need it okay so it is deleted now what we are going to do we first of all we need to add some packages so let's click here on tools package manager package manager console and we have multiple options for doing git you can also add these packages like this and where is tools and you get package manager manage new get packages for solution so you can search it from here also so click on this browse and search here dapper okay so here we have this Dapper and here we have to install this thing let's click on this not this this one and install it in Dapper demo dot data so make sure you have installed it in this project not this one so we will install it in Dapper demo data do not make any mistake otherwise if you are going to face some errors in future so after selecting this option click on install it is a one way okay and we are gonna also add it with package manager console so here we have our package manager console and here make sure click on this this project sorry this drop down and here we have to select this project which is Dapper demo data okay so I'm gonna remind you once again please select this one diaper demo data okay so do not make any mistakes here watch every step clearfully carefully so select this one Dapper demo data okay do not make any mistakes here so let's type here install package dapper so it is going to add a package for us which is Tapper okay now we need to add another package so type here install package and the next package will be Microsoft dot data dot SQL client okay so it is the second package click on Insta click on the enter button and it is saying that yeah I guess I have done some mistake so Microsoft spelling mistake install package Microsoft microsoft.data.sqlclient okay so it is fine now very good now we have installed the desired packages now we are good so we are gonna add a new folder inside this project Dapper demo data Okay so right click here and this project add new folder just name it data good and here we need another folder models good now inside this data folder okay so now inside this data folder we are going to add a class which is called data access Okay so click here I sorry not I data access so it is our data access class here we are going to Define our database logic so change this internal to public otherwise you are not able to access this class inside this project now we need a private read only and it is a config private read-only I configuration High configuration manager config that's it so it is our field so it is getting as error and I guess not guess I am sure it is I configuration and if we press here Ctrl Dot then we will get some we will get some hints here and it is saying that install package Microsoft dot extension dot configuration dot abstraction okay so we are definitely going to install this package by just clicking here okay so install this package either from clicking here or by going there in the tools and we can also install this package from here from the package manager console so why would you go there when we have option here install package microsoft.extension.configuration and Dot abstraction so click on this thing and let's click on find and install latest version okay uh if we click on this what it will do so I have clicked on the second one so it took us here okay so I pressed Ctrl dot so potential fixes and click here find and install latest version install with package manager so I'm clicking on the second one okay so watch here carefully press Ctrl dot install package and click on the second one install with package manager you can also select it it will automatically install it for you you can select either one of these options so I'm gonna going with second one second option so it is taking us here and we will install this package so the latest one is tables and point zero click on install and let's wait so it is installed successfully and we can close this tab now if we press Ctrl dot right now and let's see what it is saying press Ctrl Dot and we can click on this using microsoft.extension.configuration okay and what it will do it will add this line here using microsoft.extensions dot configuration now we are doing good press ctor it will create a Constructor for you now we can add this thing here I configuration config and we can assign this field config this parameter config okay we are good so far now what we are going to do we are going to create some methods yes so first one will be public async I think task okay and it will be I enumerable T and P so it is our generic method T and P so it will take some parameter here a string and second one is string query second one is p parameters query p and parameters another one will be a string connection ID and we are going to add a default here Okay so press it and sorry I have messed here so it is just I enumerability so it is our written type T and here we can and I haven't defined a method name so it will be get data it is our method name and here we will define t comma p so this T is a written type and it is a p which is a parameter so we are going to get these parameter dynamically press enter and I am zooming out little bit okay so here so it is our first method that is get data which will return high numerable of type T which will be decide in future and we will have the name get data it is the name of method and it have two types T and P T is the type of data you are going to return and P is the parameter which we are defining here so it will take three parameters query parameters and string connection ID and by default we are setting a value of it to default and we have used this value from here app setting dot Json and it is the connection ID default make sure do not make any mistakes here just use the same ID so you can also copy it from here and paste it here to avoid any spelling mistakes okay so that's it and what why we are doing it so that we can easily change it in the future so if we want if we want to add another connection here we can directly add that connection ID here okay this one now write some code here so first of all we are gonna write here using I DB connection DB connection press Ctrl Dot it will give you some options using system.data using system dot data dot DB connection so click on this using system dot data okay using systemdb.connection just name it connection equals to new SQL connection here it will ask us this connection string so how we will get the connection string that's why we have used this config so that we can access our configuration file just click on config dot get connection string and here that connection is string and connection ID so connection ID is connection ID okay so put a semicolon here and just press and enter here okay now we are fine so it is our connection and why we are writing this line so that we can avoid any memory leaks in the future it will create a connection here and close it after the usage Okay so it will automatically dispose the connection here now we are going to return I'll wait DB okay sorry not DB it is connection dot query and connection Dot q-u-e-r-y query async okay and query async so what it will need we are gonna type here t okay and what it is asking us command definition not okay string SQL and parameters so just type here query comma parameters or parameters whatever you pronunciate it okay so don't worry about it it will make more sense when we create our web apis and when you create a large project with it you will be thankful for me for creating these two methods with the help of only these two methods and the second method I am going to create now with the help of these two methods you will able to maintain the whole project okay so the second method will be public async task and public asking task and we will assign P here just name it save data just name it save data and it will take query and PE Okay so I have messed again here so it will not return anything but it will take an input parameter so we will Define p here P parameters or parameters connection ID a string connection ID equals to default Okay so here is our method foreign just copy this same line here and now what we are gonna do okay so it is connection ID so why it is giving me any error okay I have put the same column here now we are fine so just right here await connection dot execute async that's it query parameters and parameters so these are the two methods by which we are going to do the database connectivity so this method will not return anything so just right here this method will not return anything okay so it is only used for saving the data and this method will return a list of type T So this method is used for getting the list from the database and this method is used for will be used for saving the data into the database okay so now we are done to the data access part let's close this thing and click here in the models and a new model here so add new class inside the models folder just name it a person okay so just write it public and add three properties here which we have defined inside the database so its second one will be string name third one will be email and just put her required attribute here very good copy this thing paste it here that's it easy peasy now let's close this thing now right click here and add a new folder so let's name it Repository okay but before that I just forget one thing and we have to create interface for it I data access and right now we don't have this interface so it will give us an error so if we press a control dot here we put a mouse here occursor here press Ctrl Dot and it will say as generate interface I data access in new file yes definitely and what it will do it will create an interface for us here so we have this interface here we are good so far but what we have to do just copy this line from task to till here so paste it here okay it is one method and do it same thing here copy from the task to the last parenthesis of the method and paste it here now we have these two methods so put a semicolon here now we have this interface and this class what we have to do because we have created these interfaces or this interface so we have to open this Tapper demo API project and click on this program dot CS file and we have to resolve this dependency here so please do not forget this step otherwise you are going to get some error in the future regarding the dependencies so right here Builder Dot Services okay it was a builder dots Services dot a transient let's just name it I data access it is our interface so press Ctrl Dot and okay now now why we are not able to see it here because we haven't reference this project to here so right click on this Tapper demo API project so listen carefully right click on Dapper demo API not on this project click on Dapper demo API right click so right click here and here add added new project reference so I'm gonna show you again right click on Dapper demo API and click on ADD and click on Project reference now okay so we have to select this Tapper demo data so click on OK button and now we have referenced this project here we can check it here so when we click double click on this Tapper demo API listen carefully when we double click on this Dapper demo API it is gonna show us here this line so we have added the project reference here you can see Tapper demo data Dapper demo data.csr project that is good okay so let's close this thing now if we press Ctrl dot here we will see this line here using Dapper demo data dot data okay click here and it will add this line here using Tapper demo data dot data okay now we can add this line here data access now we have resolved this dependency here so I'm going to close this tab for now and we are done with data access okay so now what we are going to do here we are gonna add a class here so first of all we will add the interface here so new class and rather than selecting class I am selecting the interface and I'm going to name it hi person Repository okay change it to public okay so we will Define some of the methods here first one we will need task bull and let's name it add person okay and it will take an object of person type that's it and one more will be update person that's it another one will be let's just say it will be a type of person and it will be get person by ID and we will define an ID here and ID okay another one will be get people and it will be high enumerable of percent type that's it get people and we have to remove this okay one last thing that will be a task of Boolean type and it will be delete person so we need these five methods so let's create a class here and it will be a person class that will implement the person I person repository interface so it will be a person repository class that's it and we will implement this interface there so let's inherit that interface I person Repository that's it and we have to change this internal to the public so let's change it to public okay now right click here and quick refactor and click on Implement interface that's it it will Implement all the method now we have to define the functionality so we need to define a field here private read only and it will be I data access let's just name it DB okay so DB will be all right now what we need to do we have to define a Constructor for that just type ctor press double tap okay it will create a Constructor for you now let's copy this guy and paste it here just type DB and assign DB to this underscore DP that's it we are fine so let's do a thing first just open this demo project sorry demo API project and uh oh okay so go to here person.cs class and just copy this line and change it to I person Repository to person repository or repository whatever it is okay now we have resolved our dependency now go there again and Define this person Repository that's it now just I hit try double tap and here what we are going to do we are going to add a person so where result equals to oh wait T sorry it is not underscore of it just a weight DB Dot save data okay so what this save data need it just need this thing query parameters and connection ID which is an optional parameter so just Define here query q-u-e-r-y query and parameters okay so just leave it like this first we need to Define here a query so a string query equals to insert into person or we can write here dbo dot person values we can type here person name email values at the red name add the rate email and just put a semicolon here that's fine now we need to Define we need to pass an object here or we can pass it like this name equals to sorry we have to write here new name equals to person dot name email equals to person dot email I guess it should be fine but wire is giving me an error so let's just see okay okay so what it is saying the weight cannot be used as a nursing method uh okay so what it is saying cannot assign void to an implicitly typed variable okay so it does not return anything so we can now restore it inside any variable now let's see what it is saying but for now just return a true from here and we are assuming it is saved now and return false from here just type exception ex and we are not logging any exception here you can log in exception here by using Siri log or anything you like now let's just see what it is saying so I guess we are done now let's try to build it let's see is there any error or not if there is an error we will find it right now okay so here we have bunch of errors so first of all it is saying away operator can only be used in a sync method blah blah blah okay so we will provide it an async method uh I think I think now it should be happy with me so let's build it again I don't know why it is not displaying error in the compile time or before the compile time so build 2 succeeded zero failed okay we are good now now we can copy this guy and we can Implement a update method very good now it also needs an async method that's it now what we have to do we just have to change this query so query will be update DB Co dot person set let's just say name equals to name email equals to email where ID equals to ID hi so so we can provide here an ID it will be person ID or we can pass a whole object here like this that will also be okay I guess I'm not so sure but I guess okay that's it and we are good now now what should we do okay so first so let's define that delete method also just copy all of these lines here and try passing here very good and just right here delete okay so right here delete from person where ID a spelling mistake where ID equals to at the rate ID and he will provide that ID from here so ID equals to ID that is good and one thing you have to note that this parameter ID and this parameter should be same there should not be any misspelling mistake or it is a case sensitive also I guess so keep that thing in mind these two things should be matched this ID and this thing okay delete from person where ID equals to this and just save it okay we are good now so now what we can do now let's move here get people so just type here a string query equals to select asterisk from person just type here dbo dot person and copy that DBU paste it here also DB dot person where not where just DB dot person and now we will where we will write here where people equals to a weight DB dot get data and it needs a type here so we're gonna type here person and here dynamic okay so this Dynamic is a type of parameter that we are going to pass and it is a person it is a type of data which we are going to return from this method so that was it so this person so this type and this type should be same now let's just pass that query here query and parameters now we need to pass some parameters and we do not need to pass any parameter here so we will pass a blank object here okay we are good till now so just return people from here very good now just copy these lines and paste them here fine just type here where ID equals to at the ID new ID not all one Capital ID equals to small I and small d that's it but here we have a problem it is a High numerable type so if we write if we right here like this innumerable person that will also be fine but we need to return an object here I don't know why it is not giving an error but we need to return an object here's not a list so we have to write this thing people thought first or default so I don't know why it is giving me an error so I guess yeah I think okay I guess we are good now we have implemented all of these method and I don't know how they correct or not we will test them later so for now this class Library part is done now now we will move to the UI part so open this thing this project Dapper demo dot API and right click on the controller we will add a controller here that will be an click here on API and it will be a API controller and we are going to use this API controller with read write actions so that we can save some time all right that is very fine so just type here per s-o-n person control good we are good now we have all of these methods we have to change them the first method is our get which will return a list of person and press Ctrl Dot select this thing and it will add this line here using Dapper demo data dot models okay so it will be an async method so wrap it in wrap it inside the task okay so let's type here get written okay okay but not okay we have to inject our services here so type here private read-only not a person but person Repository that's it and it will add this line here using Dapper demo data dot Repository or repository whatever it is now we have to type here ctor press double tap it will create a Constructor for us and sorry it should be an i person Repository okay so it will be person very good now person equals to person now we will right here move our data or [Music] people equals to okay we are missing a semicolon here person Dot uh let's change its name let's name it person Repository it make more sense here so let's remove this underscore from here from here also person Repository and get people that is fine very good now and we will do same here just copy these lines not just this line actually we have to convert it to async task person so it will return an object of percent type and here rather than get people we will type get person by ID and we're gonna pass this ID here and what we are going to check here if people equals to null or we can write here we can lie sorry we can write it like this if we put this null then return not found okay otherwise return okay people so why it is yelling at me created okay object result of let's see so if people is null first of all let's change it name because it's an object of single so it should not be plus people now we will look into an error so what it is saying if person is null creates a not found that what is his status code Returns the gradient cannot implicitly convert the type microsystem BC not pound result to dapper okay okay okay okay it should be an IX and result and here I have done that terrible mistake also it should be an IX and result now we are good okay so I have changed it from this to this keep that thing in mind okay now we are good so if person is null then we will find date not found otherwise everything is fine so we will do same here as sync and it will be async task i x and result that is fine post and we're going to remove this from Buddy Guy from here just type here person plus one that is fine now let's see what we can do first of all we will write we will check if model first of all we will put a negation sign here model dot is valid model state DOT is valid it means if model state is not valid so we will return and we can let's just see we can pass here bad request and just pass here message invalid data okay now now let's see where person sorry so where result equals to DB Dot not DB it was a repository something person Repository percent repository dot hat person that's it so it should be wait here now let's just see if result is true and let's see let's write here not true so if we have any error inside that person depository dot add person method so we will just return this pad request from here okay this right here could not save data anything like that uh [Music] type here okay and we do not need to pass a result here so that's it I guess let's copy this thing from here to here and we are also going to copy this all of not all of this thing just this thing async task action result and it should be like this public guessing task i x and result put ID from body from body we will pass an object person that is it now if model is not valid in valid data rather than add we will update it okay now here we will check condition where person equals to let's change it to new person and copy this new person and paste it here okay so paste that new person here here okay now we will find a person where person repository Dot get percent by ID if person is null then return not found and here person new person okay so we can write it like this sorry not person new person dot ID equals to ID okay that's it so now what we can do we can copy all of these code and paste it here and we can utilize this code so first first of all we will type here async task i x and result delete so first of all we do not need these two lines now we will check this if first one is null not found now we do not need this line also and and delete person and it need just an ID Okay so I guess we have deleted a person here so if this this this if result is not true then we will written a bad request could not save data otherwise it will be okay I guess we have defined all the methods so let's run the project at the last thing we have to do here okay so now we will test these things here so let's click here and it will expand now click on this thing this button draw it out just click here and click on execute let's see do we get an error or not so we are getting an error here that's a pretty bad error system dot not support exception serialized and deserialization system.xn instances are not supported and it's a big mess so now what we can do okay we do not have to worry let's click here put a breakpoint here and let's see is this method hitting or not so this method is definitely hitting now click on this down arrow you can see where I am clicking here Okay so click on this thing step into and you can press directly f11 so now click again and we will go inside this method now here okay so if we click here again we will go there and there and again there there let's just see are we getting any result or not so here we are getting the result that is fine so click it on click on it again and so it's a different problem I guess Okay so let's click on this execute button once again and this time we will click on this step over and shortcut of this thing is F10 and here we are getting okay so we are getting this thing here and that is a terrible mistake again have to write and have it here okay and this thing will go here also await get person by ID and here is a weight also and here how it also okay so here we have to change it to a weight a weight of weight that is fine and one of eight will be needed here also okay so we are fine now I guess I'm not that sure so let's just check that thing again okay so we are here so expand this guy and try it out execute and let's see this time we are getting a list so remove that breakpoint from here and we are good to go now we are getting an array so let's clear it and cancel it and minimize this thing okay so let's do one more thing here let's expand this post method try it out and here let's type Max at Red X Y Z x y z dot com and let's name it max11 and rather than type here X11 let's click on execute let's see what we are getting from the response and we are getting two double zero okay status code I guess that is fine so if I copy it here and just type here API slash person so we are getting a new record here height equals to 3 Max double one email Max double one okay so that is fine so let's minimize this thing and let's move here so we will try this thing here so just pass your ID 3 and we are getting this error here Okay so no problem no problem we are going to put a breakpoint here and let's execute it again and click on this step into f11 okay that is fine click here once again and here we have this problem I have noticed here a spelling mistake mistake w h e r e okay I guess we are good now we are good now I'm gonna remove this breakpoint from here okay so let's test this thing right now ID equals to 3 execute okay now we are getting two double zero status code and in response body we are getting this object okay let's cancel this thing and let's test this method so click on Try It Out Three and we are gonna pass Max and Max gmail.com and before that let's type this URL here and you can see with ID3 we have this record name Max One One Max one one xyz.com is email so now we are going to execute it and we are getting a success code okay that's a good news now you can see we have changed its name to Max and email to Max at the gmail.com now we have last method to test and I'm feeling very good at this moment so let's click on this try it out and we are going to delete that third record execute and we are getting a 200 status code that means okay so let's refresh this and we just have two records now so we are done now we are done there and I hope you enjoyed this tutorial so if you find this video helpful then please hit that like button and if you haven't subscribed this Channel Please Subscribe it to support me please share this video to your friend Circle so that they can also get a help from this video so that's it for now I will see you next time

Channel: Ravindra Devrani

Views: 6,547

Keywords: dapper tutorial, code first approach in dot net core, dot net api tutorial

Id: Y6EbAPiN7gs

Length: 75min 28sec (4528 seconds)

Published: Thu Feb 02 2023