How to Implement the Repository Pattern in C# Using Dapper and SQLite

if you're a net developer working with databases you've probably worked with Entity framework core but what about Dapper hi my name is Nick centino and I'm a principal software engineering manager at Microsoft in this video we're going to be looking at the repository pattern using Dapper this video is part of a larger playlist that focuses on the repository pattern and caching as well so I'll have a link to that right up here and I do recommend that you check this out from the beginning if you're not familiar with the repository pattern Entity framework core or Dapper and this way you can see these pieces come together as we explore Dapper in this video with the repository pattern this will build upon the other videos so I'm going to be glossing over the interface that we have for the repository pattern I'm not going to be going through all the nitty-gritty details about why I pick those interface methods and if you want to understand that better you can start from the beginning of this playlist with Entity framework or now if all of that sounds interesting just a reminder to subscribe to the channel and check out that pined comment for my courses on dome train let's jump over to visual studio check out dapper and the repository pattern so in this video we're going to be using the sample application that we have from the prior videos it is going to be using these minimal apis on this asp.net core web app I do have a comment here that these are not really what you should be doing this is just so that I can show you it in the browser very quickly when we go to walk through and debug some of this the idea is that we have what's called a crud application so we can create we can read we can delete and we can update as well and these are all going to call methods on our repository pattern and then from there we'll see how we use Dapper in order to hook up to a sqlite database starting from the top if we want to go wire all these things up if you've watched the previous videos where we were doing this with Entity framework core we have a generic repository interface I'll quickly jump over to that just so you can see it and when I say generic I mean the entity that we're using with our repository is going to form the typ parameter for this generic repository so if I jump back to program.cs and how we had this all wired up in the previous videos with Entity framework core was done online for so what we used to have was set up and then we had we had Entity framework core uh crud generic was what we left off with but now we don't need to use entity frame core we can literally swap everything out to use Dapper and I'll show you the implementation of this extension method in just a moment but the entire idea is that we can swap out the entire implementation of the Repository none of the other code here has to go change and we should be able to go run our application with an entirely different repository this repository still will point at a sqlite database so the backing database is the exact same but the technology the omm or object relational mapper that we're using is Dapper now instead of Entity framework core and I'll walk through in just a moment what Dapper looks like and some of the reasons why I enjoy using it to understand how the dependency injection Works we're going to jump into this little extension method I wrote we'll see that we're adding a Singleton repository it is going to be the generic repository that takes in the entity type and this entity type is from the previous videos if you were building a real application say like a Blog you might have a Blog entity or if you had a website with products you could have a product entity I just have a generic entity here for the sake of demonstration and then the implementation is going to be this Dapper repository now for our Dapper Repository it needs to take in a factory method that allows us to create and open up connections so right here we can see that I've defined a function that returns a DB connection and this is just going to be a call back that we can provide in all that it's going to do is open up a SQL light connection so it creates it here with the connection string this just points to test DB if you look in my solution explore on the left you can see test DB right there I also have it opened up in another program and then the next and we open up the connection and return it so that's this call back function and we pass it into the repository this code rate here just wires up onto the dependency injection framework that we're going to be using a specific implementation of I repository and it's our Dapper implementation if I jump back to program.cs you can see that we're calling it right here on line 4 and that's all that I need to change to go from The Entity framework core implementation to the Dapper one but of course we have to go look at the Dapper implementation to understand what it's doing so we'll jump over to that next so if you've never worked with Dapper before one of the major differences between Dapper and Entity framework core at least from a usability perspective is that it's a lot more like using SQL directly for many people that are using NTI framework core I think one of the primary reasons that people love to use it is that you don't really have to think about SQL nty framework core is super powerful and if you really need to dive into raw queries or actually working with SQL it has all of the tools and capabilities to do that so when I'm explaining this stuff to you and I've mentioned that I like Dapper personally better I'm not saying that Dapper is the better tool overall I'm not saying that Entity framework core is bad I get a lot of flack for this online but it's just a personal preference and the personal preference comes from the fact that I do like having SQL queries some people hate it for me personally I like having them in front of me I like being able to tune them and tailor them directly and yes I know you can do this with ntity framework core now but Dapper just happens to be the thing that I go with and I figured one of the reasons that making this series would be a lot of fun is that you can compare and contrast the two and I'm not trying to tell you to pick dapp or over Entity framework core I just want to show you what the two implementations look like and then you have some more information to go with this is just a brief Interruption to remind you that I do have courses available on dome train focused on C so whether you're interested in getting started in C looking for a little bit more of an intermediate course focus on object-oriented programming and some async programming or are you just looking to update your refactoring skills and see some examples that we can walk through together you can go ahead and check them out by visiting the links in the description and the comment below thanks and back to the video what we're going to see in this crud repository that we have again create read update and delete we're going to start with a read which is going to be a get what we need to do is open up a new connection so that's going to use our Factory method that we passed in we'll just invoke it to get a newly opened connection and then from there we're going to use a Dapper method and Dapper has a lot of extension methods that are added right onto the connection interface so we can say query first or default and it's an asynchronous call and then we provide in the SQL query that we want to use the next set of parameters is going to be the parameters to the SQL query that we want to go run and then we can provide a cancellation token as well if we look at the SQL query it's going to be asking for two Fields coming back the ID and the value the table that we have is called entities and then we're just going to match on the ID and you'll notice on line 25 that's where we provided in the parameter which is the ID of the entity that we want to look for so Dapper because it's an OM knows how to go map these two properties into if I jump to this entity class into here so you see ID and value as well so it does that sort of magic for you you'll notice that I don't have to go take the result out of here use a database reader read out the columns and things like that and then try to go map that to the object directly it just does that magic for you so in my opinion this is sort of The Best of Both Worlds that I like because I get my SQL query CU like I said personally I like to use that and then it does all of the mapping for me without me having to do some of that tedious work now Dapper has a bunch of other extension methods that will look very Sim similar to some of the link things that you might be used to so I was using query first here you could do query single and it's again just very much like the link extension methods right so query first query single we have the default part you can do it synchronously or asynchronously so lots of familiar options that you're probably used to if you're used to link and Entity framework core for that matter next up we're going to look at get all async it's going to be very similar like all of these will start with opening up a new connection we're just going to use a dip Dapper method so we can see that we're calling query a sync and we're passing in the entity type this is going to give us a collection back of all of the things that match so because our SQL query doesn't even have a wear Clause it's just going to get the ID and value from the entity's table that's then going to get pulled into an i inumerable and we're going to two list it before going back you could use two array if you wanted to as well but the idea here is that we're going to materialize this collection when I say materialize I mean taking something that we can enumerate over and pull it all into memory I want to call out that this might not be something you want to do depending on your database and the entities that you're working with so a common theme as you're watching through all of the videos in this playlist as I start putting more and more together is that some of these things will make sense in some context in other contexts it will make absolutely no sense to do if I had a product database with 10 million products in it and someone called get all and then pull all of that into memory probably a big no no however if I had some repository and that was going to be a small set of things that I knew was going to be capped at some amount and I was comfortable pulling that into memory then I might be totally cool with calling get all async and in future videos we'll look at caching as well the caching concept is going to come up here again I just wanted to call out that if you're looking at this code and saying okay here's a repository pattern I'm just going to copy and paste all the code I see on the screen please consider what get all async would do if you're materializing it into a collection if you just wanted to iterate over the entire thing with an i inumerable there are ways to do that but I just wanted to call it out so you don't go blindly copy and paste if you notice so far the common thing between both of these is that we're using a napper extension method and we're using a SQL query right inside the code here next up we're going to look at create a sync so connection get gets open then we're using a SQL command here to insert into entities right so it's not querying we're going to insert we have the SQL syntax here but now we're passing in two parameters we have the ID and the value of the entity that we're working with so slight difference here and you'll notice that we're not returning back a new entity we're just putting one into the database next up we'll move over to update and update is always an interesting one because in some cases people want an update method that's like an up exert or insert or update right so you might say only update if the entity is already there with the same ID or sometimes you'll say hey it's not there okay add it like I just wanted to make sure this data was put into the database so updating or adding in this current implementation if you look closely it's just an update right so this wear Clause that we have right here will guarantee that if we don't find something we're not going to be matching anything and therefore not updating anything so that's why this returns a Boolean and the return value of execute async is essentially going to be the count of rows that were affected if we've updated any rows that means we're greater than zero that's going to return a true if you want to go Implement your repository pattern in a different way this isn't like written in stone that it has to be done this way you may have different use cases so this is all just an example of what you could be doing with your repository pattern but finally let's move on to delete a SN think we can see that it takes in an ID so we don't need the entire entity to go delete it we just need an ID of the entity to work with again we're going to use this execute a sync method and we're going to say delete from entities where that ID is the ID all of these are very similar in nature we have some that are pulling data back we have some that are executing something but one of the big things you'll notice especially if you're familiar with Entity framework core is that we have to look at SQL here right when you're using Entity framework core all of the sequel is kind of hidden from your eyes if you want to go see it you can find it or you can work with it but I think like I was saying earlier one of the reasons that people really gravitate towards Entity framework core is that they don't have to be having their brain thinking about squel so it's pros and cons and personal preferences involved and for me a lot of the time the personal preference here is just the usability of it because I enjoy doing it this way but from here we're going to go ahead and run this and we're going to go see these methods get executed from the web API but I did want to call out that I tried this earlier before recording the video and I know that it's not going to work already and it's a really interesting problem but I'm going to press play and show you what's up okay so at this point in time everything is technically running if we have a quick look at the console output here in Visual Studio you can see that it is in fact listening so the https version is on 7230 for the port but let's go ahead and run this because it's a a crud API I'm going to go ahead and go to create and we get this ID back what I'd like to do is just demonstrate to you in fair warning it's going to get very bright very soon boom if we go refresh this so we have the ID right above and you can see if I refresh then you can see that we have this 8520 37424 and this guid that's put to a string ends in 9 AC we can see that's the right value so this did get inserted here I'm going to go ahead and and copy this out and I already have it on my clipboard technically but what we can do is go read this back so if I go use ID equals and paste this in here this is going to call our get method passing in the ID which is then going to call into our repository but this is where things are going to break here we see that we're getting an exception and I wanted to have a quick look at this because it's a very interesting problem and I think that it's something important to think about if you're trying to switch between implementations of things so you can see that the error says a parameterless default Constructor or one matching the signature int 64 which is a long and system string value is required so we need a long and a string as the two parameters to our entity okay sounds interesting I know we have an ID and a value so it wants a long and a string but if we go jump into entity you'll notice that it's just just int it's not a long it's not a 64-bit integer it's a 32bit integer the string is the right second parameter that we have to the Constructor here but something's up with the ID why are these different so the reality is when we were using Entity framework core we're using a different om it's mapping things differently in fact the driver that's underneath that's calling the SQL could be a different implementation than the one that I'm using with Dapper so we have two or and they could be using different SQL drivers underneath so we have completely different call paths even though they're meeting the same interface yes they're both SQL light and yes they're both OMS but they can still have different behaviors even though technically the queries are going to look the exact same what I would like to do here just to keep this demonstration going is that we're actually going to change this to long and on our base entity we're going to change this to long and I've already tried this so the rest of the application already compiles just using Longs so not really a big deal to worry about here but I just wanted to mention this because if you are working in a real code base in a real big application and something like this happens where someone's like hey we can just go ahead and Swap this out because the interface is the same and it's technically the same database not so fast right this is a good reason to have functional tests over things as much as I love unit testing some type of functional or integration test that actually exercise the SQL calls will make a huge difference catching things like this let's go ahead and run this now okay now that we're using long values if I press enter on this we should get something back and we sure do right it ends in 9 a c just like we were saying before so now we can go ahead and we can update this an update is going to take an ID and a value so if I go value equals devader and we say enter we should hit our breakpoint that I forgot that I left on here and it says true because it did update it if we try to pull it back now right it says Dev leader inside here so all of this is working as expected and we should be able to do a delete and we get a true and that means that if I go ask for this thing coming back here so back to this we should probably get a white screen and we get a very white screen so this is just a very introductory look at using a repository pattern with Dapper a reminder that Dapper is really getting us focused on looking at SQL queries and being a little bit closer to the data that way it's my personal preference but it might not be yours Entity framework core is an absolutely awesome option it's probably the most widely used one so there's a lot of good reason for that as well and if you haven't checked out the previous videos just to remind Remer they're in the playlist for this and I recommend that you go check those out before continuing on because at this point we're going to start looking at some other things like caching if that sounds interesting stay tuned