Using Dapper with ASP.NET Core Web API

in this video we are going to learn how to  use dapper in the asp.net core web api project   we are going to talk about dapper overall  how to use different queries and executions   how to execute stored procedures and how to  create multiple queries inside the transaction   if you prefer reading about this topic and  also want to download the source code feel   free to visit the article on the code based  blog site the link is in the description below   dapper is a micro orm object relational mapper  which we can use to communicate with the database   in our projects by using dapper we can write sql  statements as we like to do in the sql server   dapper has great performance because it doesn't  translate queries that we write in.net to sql   it is important to know that  dapper is sql injection safe   because we can use parameterized queries  and that's something we should always do   one more important thing is that dapper  supports multiple database providers   it extends ado.net's idb connection and provides  useful extension methods to query our database   of course we have to write queries  compatible with our database provider   when we talk about these extension methods we have  to say the dapper supports both synchronous and   asynchronous method executions in this video we  are going to use the asynchronous version of those   methods so let's learn more about these extension  methods to do that let's visit our article   we can see the dapper extends the idb connection  interface with several methods execute   query query first query first or default query  single query single or default and query multiple   also we can see that all of these methods come  with the async functionality execute async query   async query first async and so on of course you  can see from the description what each method does   and by looking at that we can see  that some of them are pretty similar   to the linq methods like first first  the default single single default   now after we are familiar with dapper and  its methods we can start with our project   we have created an empty asp.net core web  api project and named it dapper asp.net   core after the project creation the first thing  we're going to do is to create a new database   to do that let's open the sql server object  explorer and right click on the database folder   let's select the add new database option and  let's name it dapper now we have to create   our tables and populate them to do that we'll use  the script we already prepared in our source code   which you can download of  course by visiting our article   so let's right click on the  database and open a new query window here we are going to paste our prepared  script let's rename this to just dapper   and inspect the rest of the script we can see that  we create a companies table with a few columns   and that we do the same for the employees  table also we have several insert commands   to add the records in our tables  now we can execute this query   and check that our database has two  tables there we go we can now close this   with this out of the way we can install  the required packages to work with apple   let's open the package manager  console and install package dapper also, we need to install another package Microsoft the data dot SQL client after a  few seconds the package will be installed   to continue let's create a simple repository in  our project so we could use it to communicate   with our database we will make it simple  without any additional complications   that said let's navigate to the solution  explorer add a new folder and name it entities   in this folder we're going to create two classes  let's create the first one and name it employee this class will have the same property names  as the columns from the employees table   so let's add an int id property string name int age string position and int company id after this, we have to add another class  in the same folder and name it company then   let's add the first int id property  then string name also string address   string country and lastly the  list of employee named employees and instantiated with the new list employee  this is our navigational property between   the two classes now let's navigate to the app  settings file to add the connection string let's   add the connection strings key here and assign  it an object with the SQL connection key   and for the value we'll provide the  string with the server equals dot   database set to dapper and  integrated security set to true   of course feel free to modify this  connection string to fit your needs   also one more important note here since  the Microsoft data SQL client package   version 4 if your database is not using  encryption any connection will fail by default   so you might get an error with your first request  that states that the connection was successfully   established with the server but then an error  cured during the logging process if you face   such an error just modify the connection  string to include encryption set to false   now let's open the solution  explorer window one more time   and create a new folder named context and  inside a new class we'll name it dapper context here let's create a new private read-only  IConfiguration configuration variable   and let's create a constructor  that initializes this field   we are going to use this field  to access our configuration file   next let's add one more private read-only  string field named connection string then in the constructor, we're  going to use the configuration field   and the get connection string method to get our  connection string with the SQL connection name lastly, we're going to create a public db  connection method and name it create connection this method will return a new SQL connection  where we have to provide our connection string   we will soon see the usage  of this method in our queries now with this prepared, we have to  register this class as a service   to do that let's navigate to the program  class and call builder dot services   dot add a single method to register  this class as a singleton service to continue let's create two new folders  let's start with the first one which we   are going to name contracts and then  the second one and name it repository in the contacts folder we are going to create  a new interface and name it ICompanyRepository and in the repository folder we're going to  create a new class and name it CompanyRepository let's inherit from the  ICompanyRepository interface   and then let's add a new private read-only  dapper context field named context of course   let's use the constructor to initialize this  field we can make it one liner if you want   with this in place we're going to navigate  to the program class again and register a new   service by calling builder dot services that add  scoped method and provide the ICompanyRepository   interface as the first parameter and the  company repository class as the second   and we are done with our preparation this means  we can continue towards our first diaper query let's start with an example where we return all  the companies from our database the first thing   we're gonna do is to modify our interface let's  add a new public task IEnumerable Company member and name it GetCompanies then let's navigate to the repository  class and implement the missing member the first thing we're going to do  here is to make this method async   then we are going to create a new query variable   and assign an SQL select star from companies  statement to it next let's add a using directive   inside we'll create a new connection variable and  call context.createconnection method to create our   connection as you can see as soon as we stop  using our connection we have to dispose of it   inside the using directive we will  create a new company's variable and await connection dot query async method   provide the type for our result which is  company and pass a query as an argument   the query async method will execute the sql  statement that we store inside the query variable   after this we are going to return our companies  converted to a list since the queryasync method   returns i innumerable of t we want to convert it  to a list as soon as we need to return a result   it is important to notice that we use a strongly  typed result from the query async method   but dapper supports anonymous results as well   that said in this video we are going  to use the strongly typed methods only   after this implementation we can use this method  in our controller of course we have to create it   so let's navigate to the controllers folder   and add a new api empty controller  and name it companies controller so before we continue let's modify this  generic route parameter to just companies   now let's add a new private read-only  icompanyrepository companyrepo variable   and let's initialize it over the constructor next let's add the http get attribute   and create a public async task i action  result action and name it get companies inside it we will create a company's variable  and await the companyrepo dot getcompany's method   and finally just return the okay result with  our fetched companies this logic is pretty   straightforward without any additional complexity  but that said let's add a few notes here   since we don't have any kind of business logic  we didn't create a service layer to wrap our   repository layer for this type of application  the service layer would just call the repository   methods and nothing more which would just add an  unnecessary level of complexity to the project   of course we always recommend using the  service layer in larger scale applications   and if you want to learn how to do that properly  you can read our onion architecture article also   to avoid additional complexity we are not handling  exceptions with dry catch blocks in our action   again we strongly suggest handling those  exceptions but to avoid cold reputation we suggest   watching our global error handling video to see  how you can create a centralized handler all the   links will be in the description below that said  let's test our logic let's start our application and let's use the already prepared postman request   after we hit that send button we can see  our two companies in the response body   great let's now return to the project right now  all the properties from the company class have   the same names as the columns inside the companies  table but what would happen if those don't match   let's check it out first let's navigate to the  company class and modify the name property to   the company name now let's start the  app again and send the same request   we can see that the company name property is  now here this is because dapper can't map in   the way that we wrote our query it means  we have to change something in our project   so let's navigate to the repository class and  modify the query inside the get companies method   we're going to add the real column names here  instead of the star so let's add id and name   but this time we're going to use as company  name and also let's add address and country now let's start the app again and let's  send the same request from postman   this time we can see the company name property  populated as we said at the beginning of   this video dapper supports parameterized  queries making it 100 sql injection safe   it supports anonymous dynamic list  string and table valued parameters   for this example we are mostly going  to use dynamic and anonymous parameters   that said let's start with the i company  repository interface modification   here we'll add a new public task company get  company member with the int id as a parameter next let's navigate to the company  repository class and implement the interface   the first thing to do here is to make this  method async then let's create a query variable and add the select star from  companies where id equals at id   this add id parameter will receive a  value once we execute our query next   let's add a using directive and create a  connection by calling context.createconnection inside we're going to create a company variable   and await connection dot query single or  default async with the company as a generic type   and provide the query as the first parameter and  a new anonymous object with the id property that   we accept in this method as a parameter for our  sql statement finally we can return the company to continue let's navigate to the controller  where we are going to add another action   first let's add a new http get attribute and  this time it accepts two parameters the id   from the uri and the name company by id then let's  create a public async task i action result action   and name it get company with a single int  id parameter inside the action we'll create   a company variable and await repository dot  get company method with the id as an argument   if a company is null we will return just not found if it's not let's return ok with our  company that's it we can start our app now   and use another request in postman with the  id provided in the uri let's send this request   and we can find our result so this works great now we're going to handle a post request in our  api and use the execute async method to create   a new company entity in the database the first  thing we are going to do is to add a new folder   name it dto and inside it let's add a new class  we're going to name it company for creation dto   we're going to use this class  for the post request parameter here let's add a new string  name property string address and string country we'll not explain what  are the benefits of using dtos in api   but if you want to learn more about it  and how to use it with different requests   you can check out our two videos for handling  get and for handling post put and delete requests   you can find the links in the description below now to continue let's navigate to the interface  and add another member we'll have a public   task create company with the company  for creation dto parameter named company then let's navigate to the repository  class and implement our interface   ok here is the method so let's make it async  then as usual let's create a query variable   and add an sql statement this time we're going  to use one to insert the record in the table   so let's write insert into companies and then  provide the columns we want to use name address   and country and then provide the values as  parameters add name add address and add country after this we're going to  create a parameters variable   and assign it a new dynamic parameters instance then let's use the parameters variable and call  the add method to add a new parameter we have   to provide the name then value as company.name  and the type of parameter which is dbtype.string let's remove this and import the using statement now let's copy this line and paste it two  more times modify name to address here as well   and in the last parameter modify to country and here again once we have our parameters ready  we can add a new using directive   and create a connection by calling  the context.createconnection method   finally inside the directory will await  connection dot execute async method   and provide our query and our parameters  that's it we want to explain one thing here   if you call this method in our controller  and pass a company for creation to it it   will create a new entity for us but  while creating api's post action it is   a good practice to return a link which the api's  users can use to navigate to the created entity   with this methods implementation as is we are not  able to do that so now we know how to create a new   record in the table using dapper but let's improve  the solution first let's revisit our interface   and modify our create company member to return a  company then let's get back to repository class   and add a couple of modifications to  our method first let's modify the query   let's add the plus sign to move the  query to a new line and add select cast   scope identity as int so now we are executing two  commands the first one to create a new record and   the second one to return the last identity value  inserted into an identity column in the same scope now let's move to the using directive  body and replace this with our new code   we are going to create a new id variable and await  the connection dot query single async int method   where we provide the query and parameters this  means that we will execute the insert command and   the select command from our query parameter and  also return the id result from the select command next let's create a new created company  object and assign a new company instance and set the id property to the value  of the id variable name to company.name address to company.address and country to company dot country lastly we're going to return this created  company of course we have an error here   because we change the name property  to company name in our company class   so let's just navigate to the company  class and modify this to just name now let's return to the repository class  and just add a company return type here with this out of the way we  can navigate to our controller here let's add a new http post attribute and then  add a public async task i action result action and   name it create company we will provide the from  body company for creation dto company parameter inside the action let's create  a new created company variable   and await companyrepo dot create company  method with our company as an argument   after this all we have to do is to  return created a drought add a name   of the route which is company by id add a new  anonymous object with a single id property   assigned with createdcompany.id property and add  our created company object as the last argument now let's test this let's start our  app and navigate to postman here   we'll use the already prepared post request  with the request body and the headers   so let's send it and as a result we  can see our newly created company   also if we inspect the headers tab we  can see the location for our new entity   let's copy it paste it in the new request and  send a request and we see this works great working with update and delete is  now pretty simple because we already   have all the knowledge we require so  let's just jump straight to the code   first let's open solution explorer and  in the dto folder create a new class   and name it company for update dto this class will  have the same properties as our previous detail   so let's open it copy all the  properties and paste them in a new class next let's navigate to the  interface and add two more members   the first one public task update company  with the int id as the first parameter   and the company for update dto company as  the second parameter the second member will   be also public task but named delete  company with a single int id parameter as usual let's open the company repository  class now and first minimize all the methods   with the control m o shortcut now  let's implement the missing members first let's modify the update  method let's make it async and then create a new query variable and  add an update sql statement so let's write   update companies set name equals add name address  equals at address and country equals add country where id equals at id then we need our  parameters so let's add a new parameters variable   and instantiate it with the  new dynamic parameters class   we can use the add method  to add our first parameter   provide the name the value which is our id  method parameter and the type of db type int 32   then let's copy this line and paste it  three more times here we will change the   name here to company.name and modify type  to string let's do the same for the address which is of the string type as  well and also for the country with the string type again then we need the  using directive and create a new connection   using the same create connection method and  all we have to do inside is to call weight   connection dot execute async and  provide the query and the parameters after this implementation we can move on to  the delete method and make it async first   then we need the query and let's write delete from  companies where id equals at id next let's add a   using directive and inside create a connection  by calling the context.createconnection method finally we will await connection execute   async method with the query and the new  anonymous object with a single id property now the next stop is our controller here we add a new http put attribute  with a single id uri parameter   then let's create a public async task our  action result action and name it update company   it will have two parameters the first one int id  and from body company for update dto company as a   second parameter now let's add a new db company  variable and call await companyrepo.getcompany with id as an argument to fetch our  company if it's a null we return not found and if it's not it means we found company  and we can update it by calling await   companyrepo.updatecompany and pass id and the company as  arguments and finally return no content   after the update action let's implement the  delete action so we need the http delete attribute   with a single id ui parameter then let's  add public async task action result action   and name it delete company here we  provide a single ind id parameter   now since the implementation is  almost identical to the previous one   let's copy this update  implementation and paste it here and then modify the repository method call from  update to delete and remove the company argument   and that's all it takes this action   also returns tool for no content  after the successful delete action now let's start the app and use  our already created posting request first let's send the put  request and we get 204 results   also let's open the delete request and  send it and here as well we get 204   so everything works great we've seen how  to execute different queries with dapper   but we can also execute stored procedures with  it pretty easily let's see how to do that first   we have to create a stored procedure so  let's open the sql object explorer window   expand this folder and then right click on this  one and choose add store procedure now we can   modify this template let's modify the name of  the procedure to show company by employee id then let's remove this first parameter and  modify the name of the second one to id next let's modify the select statement we're going  to remove this and implement a from part first so   let's add from companies c join employees e on c  dot id equals e dot company id we also need where   e dot id equals at id parameter  that we accept in this procedure   now let's add what we want to select c dot  id c dot name c dot address and c dot country   and remove this last return statement  that's it a straightforward implementation   all we have to do is to execute this procedure click this button and we can see that the action  was completed successfully we can now close this   down without saving with the stored procedure in  place we can move on to the interface modification   let's add a new public task company get company  by employee id member with int id parameter next let's navigate to the repository  class and implement our missing method then let's make it async inside it we're going to create a procedure name  variable and add the name of our created procedure   show company by employee id next  let's add a new parameters variable   and instantiate it with the  new dynamic parameters class to add our parameter we will use the add  method and provide the name the value   the type which is db type int 32 and the parameter  direction dot input as a direction parameter   for our stored procedure we are choosing  input here because we are sending this   parameter to our procedure or in other words  procedure accepts this parameter as input now with the using directive we  have to create a connection variable   and use dot create context.createconnect  to create our connection inside let's add a new company variable and a  weight connection that query first or default   async method with the company as a generic  parameter now we have to provide several arguments   the name of the procedure the parameters and the  command type as command type dot stored procedure   just one note here because our stored  procedure returns a result because we   execute the select statement inside it we use the  query first or defaultasync method to execute it   on the other hand if your stored  procedure doesn't return a value   you can use the execute async  method for the execution said let's just return the  company from this method lastly let's navigate to our  controller and implement a new action so let's add a new http get attribute with the  by employee id addition and a single id parameter then we need a public async task asynctask   actionresult action and name it get company  for employee and provide the int id parameter as for the implementation we would use already  familiar logic so let's create a company   and await company repo dot get company by employee  id method that will execute our store procedure   and let's send id as an argument if our  method returns null we will return not found on the other hand we return ok company with this implemented we can start the app and  let's send a post request to our newly created   endpoint as you can see we want to get the company  with id2 so let's hit that send button and we   get our company which means that we successfully  executed our stored procedure inside the database   with dapper we can easily execute multiple  sql statements and return multiple results   in a single query let's see how to do that with an  example as always we'll modify our interface first   let's add public task company get multiple  results with int id as a parameter ok let's go to the repository class  now and implement the missing member as usual let's make the method async first then  we're going to create a query variable with   multiple select statements let's add a first one  select star from companies where id equals at id and also another one in a new line select star from employees where company  id equals add id so here we want to return   a single company by id in the first query and all  the employees for the company in the second query   now we need a using directive and add a  connection with context.createconnectionmethod we can remove these parentheses because we  are going to add another using directive below   here we'll create a multivariable and  weight connection dot query multiple async   where we pass our query variable and an  anonymous object with a single id property   the query multiple async method will  extract multiple results from the database   and store it inside the multi variable  now let's add a company variable   and call await and extract the first result from  the multivariable by calling the read single   or default async method and provide a company as  a generic parameter if our company is not null   we can populate the company.employees property  with the second result so let's await multi   dot read async and provide the  employee as a generic parameter   we also want to cast result to list so let's  add parentheses here and call the to list method finally let's return our company  now let's navigate to the controller   and add a new http get attribute with the id  parameter and additional multiple result part then we're going to add a new public  async task i action result action   named get multiple results  with int id as a parameter next all we have to do is to create a new  company variable and call the await companyrepo   dot get multiple results method where we have  to provide our id parameter as an argument   let's check if the company is now   and if it is return not found on the other  hand let's return ok with our company now let's start the app to test this  logic and send already prepared requests   and there we go we can see the result containing  both the company and all the employees for that   company in a previous example we've used  two sql statements to return two results   and then join them together in a single object  but usually for such queries we don't want   to write two sql statements we want to use a  join clause and create a single sql statement   of course if you write it like that we can't  use the query multiple async method anymore   we have to use a multiple mapping technique  with the well-known query async method   so let's see how we can do it as usual we are  going to start with the interface modification   let's add public task list  company multiple mapping then let's navigate to the repository  class and let's implement our interface let's start by making the method async then let's create a query and add select star from companies c join employees e on c dot  id equals e dot company id after the select statement we're  going to create a using directive   and a new connection variable where we call  context dot create connection method to help   us with the process of extracting our results  we are going to use a dictionary so let's add   a companydict variable and instantiate a new  dictionary with the int key and company as value   next let's create a companies variable and  weight connection dot query async method   with three generic parameters company  employee and company again the first two types   are the input types we are going to work  with and the last type is the return type   now we have to pass parameters  to the query async method   so let's add a query as the first one and a func  delegate with company and employee as parameters inside the delegate we want to use our dictionary  and call the try getvalue method to find the entry   with the specified key company.id and extract  that value inside the current company variable and we will negate this so basically we want  to do something in the body of the if statement   only if we can't find the company for  the required key in our dictionary   if we can't find it let's add to current  company the value of our company parameter and let's call the companydict add method to  add new entry with the currentcompany.idsk and current company as value otherwise  if we find a company in our dictionary   we have to add an employee to this company  by calling currentcompany dot employees dot   add and pass an employee parameter also  we have to return our current company finally after the query async implementation  we will return companies distinct to list now let's just navigate to our controller  and paste the previous section here   the implementation of this new action  will be very similar to the previous one   so we can use an existing functionality of the  previous action let's first modify the http   get attribute by removing the id parameter  and adding here mapping instead of a result then let's modify the name to get multiple  mapping and remove the id parameter   in the body let's call a different method  remove this check and we can also modify   the name of the variable because we will return  a collection from the multiple mapping result now let's start our app navigate to postman  and send the already prepared request and   we can see our result so everything works great the last thing we want to do in this video is  to show you how you can implement transactions   with dapper transactions are pretty simple to  use with dapper we can execute it by using the   dapper library the one we already use or  by using the dapper.transaction library   which is basically the same thing as dapper  just with the extended idb connection interface   in our example we are going  to use the dapper library   we are going to show you just the repository  method where we will implement a transaction   all the rest is pretty simple as we repeated the  steps several times in this video of course in our   source code you can find a complete implementation  and as you mentioned you can find it by visiting   our article which is linked in the description  below now let's navigate to the repository class   and add a new method let's add public  async task create multiple companies method with the list of company for creation  dto parameter named companies   the first thing we're going to  do is to create a query variable   and add an insert statement so  let's write insert into companies provide name address and country and values add name at address and add country then let's create a using directive inside new  connection variable and create connection with   context dot create connection method this time  we have to open the connection with connection   dot open method then let's create another using  directive and inside a new transaction variable   and add connection dot begin transaction  with this method we'll start our transaction   then inside the using let's create for each loop  add a company variable in all the companies and   then create parameters variable and instantiate  it with the new dynamic parameters class let's use parameters dot add method  with the parameter name company.name   as a value and dbtype.string as a type then  let's copy this line and paste it two more times   let's modify this to address and here  as well and let's modify this to country and here as well then inside for each loop still  let's call weight connection dot   execute async and provide query parameters  and create a transaction as a last argument finally let's call transaction.commit to commit  our transaction as you can see it is pretty easy   to implement transactions with dapper of course  to simulate an error and test that no rows will   be created in the database you can simply throw  an exception right below the await code line the   app will call the execute async method once to try  to execute the query but then the exception will   be thrown and the transaction should revert any  changes you should find no new rows in the table   so that's it please don't forget to hit those like  and subscribe buttons down there if you like the   video and want to support us you can also use that  bell button to get notifications from our channel   also you can visit the code mess  blog to download the source code   and you can subscribe to our mailing list  to get notified about our new content and   videos thank you for watching and we'll see you  again in another video until then all the best

Channel: Code Maze

Views: 30,664

Keywords: dapper, dapper in asp.net core web api, entity framework core, EF Core, ORM, Micro ORM, Dapper ORM, C# Dapper, .NET Dapper, object mapper for .Net, a simple object mapper for .Net, Object Relational mapper, AutoMapper, Mapster, Automapper vs Dapper

Id: C763K-VGkfc

Length: 55min 45sec (3345 seconds)

Published: Fri Jul 08 2022