Building a Dapper CRUD API From Scratch In .NET 7 - For Beginners

Dapper has been a highly requested topic on my channel so I'm finally delivering a video today we're going to create a simple crud API using the Dapper microorm and SQL Server as our database so let's dive in I'm starting from a blank solution because we're going to be building our API from scratch so the first step is going to be to add a web API project so I'm going to say add new project and I'm going to pick asp.net core web API let's give it a simple name of web dot API and let's click next I'm going to uncheck Docker because I'm not going to be using it and I'll also uncheck use controllers because I want to be using minimal apis for my endpoints I'm going to leave enable open API support because I want to be using swagger for my user interface where I will easily be able to test my endpoint so let's create our new web API project and let's see what we're going to do next out of the box we're going to get this program CS file I'm going to start by removing these comments because I don't need them and I'm also going to get rid of the weather forecast endpoint and the respective weather forecast class so this is what we are left with the boilerplate minimal API in.net 7. the next thing we need to do is to install Dapper and our database client so let me go through our nougat packages and I'm going to first look for Dapper there it is I'm going to install this library and this is going to be our microorm that we're going to use to write queries to our database next I'm going to look for SQL client I'm going to install the Microsoft data SQL client nougat package because this is the newer version for SQL server and it's going to be the recommended one going forward so this is the one I'll be installing and with our nougat packages in place we can move on to the next step I mentioned that I'm going to be using SQL server for my database and I already have a database in place I'm going to be using the customers table which has just a few columns and this will be enough for our crud example SQL Server is running locally on my machine and I'm going to connect to this database through my web API and we're going to query this table and implement the crowd operations so let's start by adding the connection string to the web API project I'm going to add the connection string in the app settings development Json file I'm going to create a new section which I will call connection strings and let's add the default connection connection string and this is the value that it's going to have it's going to point to my localhost SQL Server instance and the database that we are connecting to is going to be called eShop with the connection string in place we're going to need a class to represent our table in the database so let me create a new folder here which is going to contain my database models and let's call it models I'm going to add a new class inside which is going to represent my customer and let's add a few properties to our customer entity so first I'm going to need an ID then I'm going to need a few string properties for the first name and the last name then I'm going to need a string property to represent the email so let me adjust the name of this property and I'll also need a date time property for the date of birth of this customer so date time and I'm going to give it the name of date of birth and now we are ready to write our first query using Dapper so let's create an endpoint to query all of the customers from the database and return them from our API I'll head over to the program file and I'm going to start simple by calling map get this is how you can define a get endpoint with minimal apis and let's give it a route of customers you want to give your routes a name based on the resource that they are managing in this case we are working with the customer entity so the appropriate resource is also the customer we're going to give our route a name of customers because we want to always make our API routes in plural the next step is going to be to create our request delegate which is just a Lambda function implementing the body of our endpoint we can use method injection here to get any services that we're going to need to implement our endpoint and I'm going to inject the instance of eye configuration so that I can access my connection string which I added in the application settings Json file the connection string is going to be in this variable and we're going to get the value by saying configuration get connection string and we're going to get the default connection value the connection string returned by this method could be null so I'm going to solve that a little later on in the video right now I'm just going to add a null forgiving operator so I'm going to use VAR because it's obvious that this is returning a string and then the next thing that we need to do with this connection string is to create the connection to our database how you create a new connection is by saying new SQL connection this class is available in the Microsoft data SQL client namespace which is why we added the appropriate nougat package now if I pass the connection string to this Constructor it's going to establish a connection to my database I also want to save this in a variable and it's really important that I add a using statement in front of my connection so that it gets this post when we go out of scope I'm using an inline using statement here you can also use the approach with a block I prefer using the inline statement because I find it simpler so the next step is going to be to write our SQL query so what I like to do and I find it more readable is to Define my SQL queries as contents and let's write a simple query that's going to select everything from the customers table so I'm going to write select star from customers and this will be enough to return the data from the customers table select star in general is considered bad because you're going to query all of the columns from the table where you might only be using a few of them but in our case we really want all of the columns so we're going to select all of them an alternative approach would be to manually specify all of the columns like saying first name last name email and date of birth this approach would be considered slightly better but you can use whichever one you like so now that we have our connection to the database and the query we are ready to query the customers so how do we do that with Dapper we can do an asynchronous query and we can say connection query async this is the method that is available through the Dapper Library it's also generic and we can specify what is the return type of this query I'm going to specify our customer model and we're just going to provide our SQL query and Dapper will be able to execute this now of course you need to make your whole method asynchronous to be able to await this and now we can just return our customers from this endpoint I can say return results.ok and pass in the customers with this in place we should be able to run our API and see the endpoint that we created in the Swagger user interface and then we're going to run it and see if we get back any customers from the database here's the Swagger UI for our API and of course we have our one get customers endpoint and let's try it out and see if we get back our customers from the database so if I execute this query we're going to get back two customers that I created in the database and now let's make a few small improvements to our design before we move on with the other endpoints before we add any more endpoints here's what I'm going to do I'll add a new folder which I will call endpoints and I'm going to add a new class inside which I will call customer endpoints now this is going to be a static class and I'll add a static method inside which I will use to map my endpoint so I will call it map customer endpoints and it's going to be an extension method on the I endpoint route Builder type so let me find it let's give it the name of Builder and now I want to move my endpoint registration from the program CS file into my static method that I just created so the map get is going to live here I'll add them missing using statements and I'm going to replace app with Builder so now everything should function the same and we just need to map our endpoints from program.cs so let's call map customer endpoints and now everything is the same as we had it before only our endpoints are scoped inside of the customer endpoints class the next small Improvement I want to make is about creating the SQL connection every time I'm going to create a helper service which I will place inside of the services folder and I'm going to call it SQL connection Factory so let's create the SQL connection Factory class and what we're going to do here is just expose one method which is going to return a SQL connection we'll call it create and all we're going to do is return a new SQL connection now to be able to do this we need to pass it our connection string so I'm going to add a private read-only so string which is going to represent my connection string now I can pass this connection string to the SQL connection and I also need to inject this connection string from The Constructor so with this in place the SQL connection Factory is ready we need to register it with dependency injection because it has the connection string which is unlikely to change I'm going to add it as a Singleton service if we were saving any state which should be disposed then using a Singleton isn't a good idea for example if we were storing a SQL connection inside of this class this is something that you wouldn't want to do because you would have an open long left connection to the database so let's call at Singleton and here's the approach I'm going to take I'm going to resolve a service provider and we're going to use this service provider to get a service which service do I need well I need the eye configuration service so I'm going to say service provider yet required service of I configuration then I'm going to use this eye configuration instance to get a connection string and we're going to take the default connection like we had earlier and now I need to store this in a variable so let's call it connection stream and I can return a new SQL connection Factory instance and pass it the connection string and I said we're going to take care of the case when this is now so let's throw a new application exception as soon as we try to resolve the SQL connection factory and we're going to say the connection string is not all right so this takes care of our SQL connection Factory and now I can go ahead and update my endpoint and simplify all of this so instead of using eye configuration I can take the SQL connection Factory let's give it a proper name and we're going to get the connection using SQL connection Factory create this slightly simplifies our endpoints and we can only focus on writing our queries so the next endpoint I'm going to create is going to be to fetch a single customer from the database it's also going to be a get endpoint and the route is going to be customers slash ID now to specify a parameter which is coming from your route you need to use these curly braces here and then provide the name of your parameter inside and now in the request delegate I can grab the ID which is coming from the route along with the SQL connection Factory and we can write our query so let's see what we're going to have inside I'm going to actually copy what I have here because it's going to be really similar I'm also going to make this asynchronous and what we need to update in the body of this endpoint is to add a where statement to take care of selecting a customer with the given ID so I'm going to use this approach for writing my query to make it a little bit more readable so I'm going to place select here and all of the columns that we are selecting then we're going to say from customers and I'm going to add a where statement and the end which is going to say where the ID of the customer is equal to the customer ID that I'm going to pass as a parameter let's also update the query method that I'm using I'm going to use the query single or default async method and this one can return null so we'll have to handle that case as well we're going to still be passing the SQL statement as before but now I also need to pass my parameter which is the customer ID and I'll give it the value coming from the route I mentioned that the customer could be null and of course this is now plural so let's update this so let's say if the customer is not now then we're going to return an OK result otherwise we're going to return results.not found and let's check out if this endpoint is working let's get all of the customers to see which ones we have in the database and let's try to get the customer with the ID of six so I'll open up this endpoint let's pass the number six which is the ID of this customer and the route and if I send this request we're going to get back a single customer that we fetched using Dapper if I pass in a non-existing customer ID such as 11 we're going to get a 404 not found response so everything is working correctly and let's see what's going to be the next endpoint that we're going to add let's add an endpoint for creating a new customer so we're going to say map post post is the HTTP verb that you should be using for creating resources in your API we're going to use the route of customers then we need to Define our request body this is where our customer is going to come before we store it in the database so let's also use the customer model directly and I'll give it the name of customer and I'm going to need the SQL connection Factory so let's create the request body and see what we're going to do inside so I need to start by opening a connection to my database so we're going to say SQL connection Factory create then let's write a SQL statement for inserting a new customer to the database so this one is going to be slightly different than what we had before going to say insert into customers and then I'm going to specify the individual column names the First Column is going to be the first name then we're going to have the last name then I'll have the email and the date of birth and then we need to provide the values for these columns and I'm just going to copy all of these paste them here and we're going to make them parameters which we are going to provide through Dapper and then this is what our query will look like I'll make this map to asynchronous before writing the query and then I can say await connection execute async because I'm not expecting any data back and we're going to pass the SQL query and I'm going to pass the entire customer object as our parameters now because the names of the properties are identical to the parameters that I'm expecting in this query everything is going to function the same otherwise you're going to need to create an anonymous object and pass in the proper values to the Dapper parameters then we're going to say return results okay and let's see if this is working correctly here's our post endpoint for creating a new customer I'm going to omit the ID because this is part of my model I really shouldn't be exposing it as part of my API a much better approach would be to create a separate model without the ID and it's only going to contain the required fields for creating a new customer so let's give it the name of new customer we're going to give it an email of new at test.com and let's say that they were born on this day which is July 27th in the year 2000. so let's send this request to our API and we're going to get back at 200 okay response so now if I check out my list of all customers you're going to see that our new customer is there if I try to query this customer by the ID so I'm going to say give me the customer with the ID of 7. we're going to get back this customer from the database so our post endpoint is working as expected let's now Implement an endpoint for updating a customer so I'm going to say Builder map put and I'm going to give it a route of customers slash ID I'm going to provide the ID for the customer that I will be updating in the route it's going to be asynchronous and I'll need the customer from my API and I'll also need the SQL connection Factory so that I can write my queries using dapper let's create the body for my endpoint and inside of this body I'm going to start by opening a connection to the database I'll say SQL connection Factory create then I'm going to write my SQL query it's also going to be similar to the one that we have for inserting to the database I'm going to say update customers and then I'll say set and we're going to say which columns we are setting so we're going to set the first name to be respective parameter and the same is going to apply to the other columns that we have in the database so let's also update the date of birth and I'm actually going to align these vertically so that you can see everything so we're only left with date of birth I also need to make sure to not update all of the rows in my database so it's really important to specify a where statement here and we're going to say where the ID is equal to the ID which will come as a parameter now I can say await connection execute async pass in the customer which already contains the same properties that are matching my parameters so this is going to work okay and I'm going to return results dot no content as the response before we can try this I omitted one small part and that is that the customer ID will come from the route so I'll add it here so ID is going to come from the route and then I'm going to set the ID property on my customer to the one coming from the route in a proper solution you would have a separate class for the request for updating a customer which would not contain the ID and you would pass it through the route in this case I'm resorting to a small hack but let's see if this is working correctly I'm going to update the customer with the ID of 7 and let me grab the existing data from the database so this is what we have right now and let's use it in our put endpoint so let's pass the ID of seven I'll also omit the ID from the request body and I'm going to say that this is a new name for this customer let's send this request and we get back at 204 no content now let's try to fetch the same customer from the database and you can see that the first name of the customer was updated through our put endpoint one more thing that's left to do is to create an endpoint for deleting a customer we're going to call map delete the route will be customers and we're going to grab the ID from the route it's going to be an async method and I'll take the ID from the route and the SQL connection Factory so let's define our endpoint body and this one is going to be slightly simpler than what we had so far so let's first create our SQL database Connection by calling SQL Factory create then our SQL query is going to be a delete statement so I will say delete from customers where the customer ID is equal to the customer ID that will pass as a parameter to this query now let's say connection execute async I'll pass in the SQL query and an anonymous object containing my customer ID and now I can just return results dot no content let's check out if our delete endpoint is working correctly let's first try to get the customer with the ID of 7 from the database and you'll see that we get a response back now I'm going to try to delete it through our delete endpoint and we get back a tool for no content so we assume that it was deleted so let's try to fetch the customer again through the get endpoint and you'll see that we get a 404 not file response which means that the customer was successfully deleted from the database one more Improvement that you can make here when you are mapping your endpoints is is to create a route group you can do this by calling the endpoint route Builder and call the map group endpoint where you specify the route prefix for the entire group then you can say group Builder here or just group to make it simple and you have to map your endpoints using the route group Builder so I will just replace Builder with my route group Builder on all of my endpoints and I also need to remove the customer's prefix to get rid of the common part and all of the routes and now my routes are slightly simpler and I can apply any cross-cutting concerns on the group itself such as adding authorization rate limiting I can also add an endpoint filter which is a way to apply middleware with minimal apis so route groups are a nice feature that you can use on top of minimal apis to make them just slightly less complicated so this is how to create a simple crowd API using Dapper and you can see how little code we need need to implement a complete crud functionality if you enjoyed this video then make sure to subscribe to my channel and until next time stay awesome

Channel: Milan Jovanović

Views: 10,876

Keywords: dapper, dapper c#, dapper crud, dapper crud api, dapper rest api, dapper .net, dapper orm, dapper micro-orm, dapper vs ef, dapper vs ef core, dapper performance, dapper query, dapper update, dapper executequery, dapper executescalar, dapper api, dapper .net api, dapper restful api, dapper rest, dapper tutorial, dapper beginner tutorial, dapper sql, dapper sql server, dapper postgresql, dapper example, dapper best practices, dapper 101, dapper .net 8

Id: Idi_X0cTDjk

Length: 22min 49sec (1369 seconds)

Published: Tue Aug 08 2023