Getting Started with Dapper in .NET

in today's video we'll see how simple it is to integrate Dapper into our application Dapper will allow us to easily integrate with our database and we'll see how we can replace our inmemory list of objects with working against a real postgress database if you're new to the channel then welcome my name is amihai and in this channel I talk about software architecture design patterns best practices things that you really want to be familiar with if you're a software engineer this video is part 11 in a series in which we're building a rest API using asp.net 8 following best practice Etc so if that sounds interesting then make sure to smash the Subscribe button in any case this video can be watched Standalone from the overall series okay so currently what we have is just a web application that is working in memory now when we say in memory I mean we simply have over here a static list in which we store our objects but we already have everything set up to work against a real database so if we look at our Docker compos then we have over here a database which is listening on Port 5432 so now all we have left to do is basically create the connection to the database and start running queries against our database to do this we're going to need two nougat packages number one is npg SQL which is the net data provider for postgress and the next thing that we want to add is the Dapper package okay now that we have that let's go to our persistence folder and alongside the the database let's create a new folder and let's call this folder repositories and in here let's create the products repository and this will replace the inmemory static list of products okay and the first thing that we're going to have over here let me split the screen and show you what we'll be replacing so in the product service when we go ahead and we create a product then we now want to update this to actually update the database so let's go ahead and create over here our first method which will be create async and this method will receive the product and store it in the database which means that we also need to go ahead and do the following let's go to the program Cs and alongside the ad Services let's add here another extension method called add persistence and this method will be similar to this but it will be responsible for all the persistence concerns once we have this then we can go to the product service and get rid of this in memory list and say over here products Repository and instead of this simply being create let's update this to create async and let's go ahead and call over here the product repository. create async method that we're now going to Define similarly the get won't be from the inmemory static list but instead this will be get async and this will be get by ID async and we'll pass the product ID so let's go ahead and generate this method as well and now let's go ahead and Implement these two methods for those who aren't familiar with Dapper then depper gives us an easy way to work above the DB connection so the first thing we need to do is get hold of a DB connection and then we want to define the SQL query and finally we want to execute the query now before we do step number one let's actually look at these two steps and see how simple it is so we still need to somehow get hold of the idb connection but let's imagine that we already have this in our hand once we have it all we need to do is the following let's imagine that over here we have our query definition and then we can go ahead and say connection and we can call execute async which comes from Dapper and all we need to do is pass it the query and it will run our query for us so in our case we're inserting into the products table the and I'm reminding you what the table looks like so we have the ID the name the category and the subcategory so we want to add over here the ID the name the category and the subcategory into this we want to add the values from the product that we got now what we can do with Dapper is the following so the product has the various properties and we could have Dapper extract the values from the product for us and insert them over here we simply need to specify the properties that we want so we have the ID we have the name we have the category and we have the subcategory and what we can do now is simply alongside the query also give it the product and Dapper will go ahead and pull the values from the properties that we put over here and create the query and execute it for us now this entire thing returns a result and we can now take this result and based on the number of rows that were changed know if this updated the database correctly where in our case we want to throw an exception if this didn't update any rows so we can throw an exception if this is smaller than one to do this let's go ahead and say result and let's throw an exception and this is coming from a new get package called Throw so so doet add the package throw and once we have that then we can go ahead and say if negative or zero and same story for the get by ID so let's go ahead and copy this entire thing paste it over here we have the connection and over here what we want to do is we want to select this entire thing from the products table where the ID is equal to the ID that comes from the product ID parameter we can say connection. query first or default async where the object that we want to materialize is the product and we pass it the query and over here we need some object that has the ID property so let's create here an anonymous object like let's pass it the product ID that we got over here let's await this entire thing and over here we should have the product if it exists and I see I'm missing over here that this needs to be a product or nothing and over here we can simply go ahead and return whatever comes back from executing the query so now that we have all of this the only thing that we're still missing is to get the DB connection to do this what we're going to do is we're going to create a DB connection Factory which will know how to give us a connection on demand so the way this is going to look is that we're also going to have over here a idb connection factory and we're going to use the DB connection to go ahead and say create connection async and this will go ahead and using the connection string connect to the database and return us an idb connection instance finally this entire thing is disposable so let's go ahead and add using over here so it's disposed once we're done using it so under the database folder let's go ahead and create over here also the DB connection Factory which is going to have the single method that we talked about so the create connection async and this entire thing is going to return in idb connection let's actually pull this to its own file and alongside this let's create our concrete implementation in a file called npg SQL connection Factory which is going to implement the idb connection Factory interface that we just created and over here it's going to create the connection now creating the connection is pretty simple so all we need to do is say new npg SQL connection and pass it over here the connection string once we have the actual connection let's add what's missing say using npg SQL once we have this then we can say connection. open async and finally return the connection the only thing that we're still missing is to pass the connection string so we can use it over here so let's add over here the connection string and let's assign it to our private read only field and use it below so overall what we have is the npg SQL connection Factory that knows how to create a new connection based on our connection string so all we still need to do is to go ahead and register this in the dependency injection IC container and pass it the connection string so let's go to the program Cs and we want this to sit also under the persistence but let's also pass here the configuration so we can access the underlying connection string so let's say over here builder. configuration and add this as a parameter now all we need to do is say services. add scoped and when someone asks for the ITB connection Factory then we want to give them an instance of our npg SQL connection Factory and we want to pass it the connection string to get this we can go ahead and say configuration and access the connection string via the constant that we created in the last videoos I'm reminding you that over here we have the DB constants with the path to the connection string so we can copy this thing and say DB constants do this thing again we're passing the connection string to the npg connection Factory which goes ahead and creates a new connection and in the end what we want for Dapper is simply the DB connection because we have all these nice extension methods on the DB connection okay so now that we have all of this let's go ahead and say Docker compose up Das D build this should start our application start the postgress database and now when we make a request then we should be creating the object in the database and not in memory so seems like everything is working as expected let's go ahead and make the create product request we get 2011 created but this doesn't mean anything yet before we let's do two things first of all let's go ahead and try to get it and yes it comes back successfully but the subcategory is missing so we'll see what I missed in a second but what I want us to do is to go ahead and look in the database and see that this actually exists as expected so let's say select top 1000 and indeed we see that it's in the database we can also see that the subcategory was populated correctly so it's something in the fetch so let's go to the product repository and we can see that what we're missing is over here in the subcategory we want to take this as subcategory and that's the mismatch that we had so now when we go ahead and make a request then we created the product and now when we're fetching it then we can see that the subcategory was fetched successfully so again what I missed in the first time around was that I left this subcategory and then Dapper didn't know to populate the subcategory property until we added this small fix so that's it for this video for my patrons then I'll go ahead and create the reviews repository off camera and you'll have that as part of the source code as well if you want to keep following along on the series then make sure to smash the Subscribe button also smash the like button and I'll see you in the next one

Channel: Amichai Mantinband

Views: 5,682

Keywords: c#, clean code, best practices, software engineering, .net, c# tutorial, asp.net, aspnetcore, aspnet, rest api, asp.net 8, aspnet 8, asp.net core 8, .net 8, dotnet 8, asp dotnet 8, dotnet tutorial, .net tutorial, dapper, database, postgres, sql, sql query, nuget package, dapper nuget, database nuget package, .net database, .net dapper, ef core, dapper tutorial, dapper nuget tutorial, dapper .net tutorial, dapper aspnet, orm, dapper orm, .net orm, running sql queries

Id: KSBOSL61OYU

Length: 11min 28sec (688 seconds)

Published: Mon Jul 15 2024