This article is a series on using Dapper in .NET Core. Feel free to jump around, but we highly recommend you start in Part 1 and work your way through!
Part 1 – The What/Why/Who
Part 2 – Dapper Query Basics
Part 3 – Updates/Inserts/Deletes
Part 4 – Dapper Contrib
What Is Dapper Contrib?
Dapper.Contrib is a “helper” library built on top of Dapper, that adds a couple of “quality of life” enhancements to otherwise annoying Dapper hurdles. Most notably, updates and inserts become much simpler with Dapper.Contrib. So let’s jump right in!
Installing Dapper.Contrib
From your Package Manager console window you can install the Dapper.Contrib package.
Install-Package Dapper.Contrib
If you haven’t already installed Dapper, this will drag Dapper in as a dependency too! Note that at times you do have to do a juggle with versions of Dapper if you install it first, then Contrib second. The easiest way if you really want to use Contrib is remove the direct reference to the Dapper nuget, and simply install Contrib and let it drag in the version it needs.
Model Our C# Class For Writes In Dapper.Contrib
The important thing when using Dapper.Contrib is that you need a C# model to match that of your database model. Again I can’t stress enough that this should really only be used in the case of writes. When reading data, the entire point of using Dapper is that you don’t over-select and so you should attempt to use applicable DTO’s whenever possible.
From earlier, we have our C# model ready to go :
class Event { public int Id { get; set; } public int EventLocationId { get; set; } public string EventName { get; set; } public DateTime EventDate { get; set; } public DateTime DateCreated { get; set; } }
This works as is, but there are a couple of things to note.
Firstly, Dapper.Contrib requires an “Id” to be able to update a record. In our case, we have a property called “Id” and Dapper.Contrib uses this convention and knows it’s our primary key on our table. But what if we use something like “EventId” as our primary key? That’s easy too.
class Event { [Key] public int Id { get; set; } public int EventLocationId { get; set; } public string EventName { get; set; } public DateTime EventDate { get; set; } public DateTime DateCreated { get; set; } }
We just add the “Key” attribute from our Dapper.Contrib library to tell it that’s our primary key.
Next, we are also required that the class name be the same as the SQL Table name, but as a plural. So in our case, our class name is Event and it expects that our database table be called “Events”. So how can we say nope, we want to stick with the singular?
[Table ("Event")] class Event { public int Id { get; set; } public int EventLocationId { get; set; } public string EventName { get; set; } public DateTime EventDate { get; set; } public DateTime DateCreated { get; set; } }
Again, another attribute. There is actually a couple more attributes that do come in handy, but these are the main ones you should know right off the bat.
Inserting Records Using Dapper.Contrib
This should hopefully make things easier.
using (SqlConnection connection = new SqlConnection(connectionString)) { var newEvent = new Event { EventLocationId = 1, EventName = "Contrib Inserted Event", EventDate = DateTime.Now.AddDays(1), DateCreated = DateTime.UtcNow }; connection.Insert(newEvent); }
… Pretty awesome right! So we are back to having this nice “Insert” method that takes our object and creates an insert statement. It takes some of that perfect control away from us, but when it comes to inserts there isn’t much that we would want to be careful of when it comes to generating the insert statement so I’m happy with it.
Get A Record By Id Using Dapper.Contrib
Contrib also has this nifty feature where it can get a full record by Id. I feel like this sort of strays a little from the intention of Dapper because you should be trying to avoid doing a “SELECT *” and instead only get what you need. But we’ll use it later so I wanted to show it off.
using (SqlConnection connection = new SqlConnection(connectionString)) { var eventId = 1; var myEvent = connection.Get<Event>(eventId); }
Because it knows we are requesting a type of “Event”, it knows what table that’s from and grabs us the entire object based on it’s primary key Id.
Updating Records Using Dapper.Contrib
Contrib can also do updates. Although they are a little less slick.
First off, we can obviously get our object by ID, update it, then write it like so :
using (SqlConnection connection = new SqlConnection(connectionString)) { var eventId = 1; var myEvent = connection.Get<Event>(eventId); myEvent.EventName = "New Name"; connection.Update(myEvent); }
So Contrib has given us that handy “Update” method. But the problem is when we check the actual SQL that got run :
exec sp_executesql N'update Event set [EventLocationId] = @EventLocationId, [EventName] = @EventName, [EventDate] = @EventDate, [DateCreated] = @DateCreated where [Id] = @Id', N'@DateCreated datetime,@EventDate datetime,@EventLocationId int,@EventName nvarchar(4000),@Id int',@DateCreated='2019-01-20 02:11:46.453',@EventDate='2019-01-11 00:00:00',@EventLocationId=1,@EventName=N'New Name',@Id=1
So it basically updated all fields even if they weren’t touched. Because of this we have to get the record by Id before updating because we need to make sure all other fields (Not just the one we want to update) are set correctly.
Now Dapper.Contrib does have dirty tracking, but IMO that’s just as pointless as you still need to get the full object by ID in the first place to do entity tracking, and the entire point is that we aren’t overselecting.
In my opinion, Updates are still a good candidate for doing custom SQL instead of using Contrib, but that’s just my personal feeling!
Delete Records Using Dapper.Contrib
Deleting is actually fairly simple. As long as Dapper.Contrib knows what your primary key is, it can delete records based on that.
using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Delete(new Event { Id = 5 }); }
To be honest, this syntax annoys me somewhat as we are creating an object for no reason! Wouldn’t it just be easier to pass in a simple Int parameter?
What’s Next?
That’s actually it! We could get into the nitty gritty of Dapper, or even explore some of the other Dapper extension libraries such as Dapper.Rainbow or DapperExtensions, but they are largerly the same as Dapper.Contrib with minor differences. They are definitely worth investigating however!
Feel free to drop a comment below on your thoughts on Dapper and what project’s you’ve used it on in the past, I’d love to hear them!
Thanks for a very good article series on using Dapper (and .Contrib). Well explained.
I have used it before but never really thought about the pros and cons of various CRUD operations.
You said that “Updates are still a good candidate for doing custom SQL”, but this is also true for inserts !
For ex, if you are not inserting all fields (i.e using default values), there is no need to use Dapper.contrib
So basically, Dapper.Contrib is not really needed (we can agree on that) 🙂
Correct. Isn’t that equally applicable for SELECTs as well ? With Contrib, we select all fields even if need just 1. I am confused as author is mentioning it as an issue only for UPDATE.
Either way, thanks for the really helpful article.
Pretty good article. Liked it. Thanks.
Nice tutorial.
Anyway, what would be a story with auto increment based primary key in combination with dapper when adding new record to database?
When using regular Dapper (Without Contrib), you would just write the insert without an ID in the insert statement. With Contrib, you would want to add the attribute [Key] which when you use the Contrib insert, would simply skip adding it to the insert statement.
I didn’t use Dapper but like it thanks to your blog article.
Anyway after adding record to DBMS do you get back PK [key] value after data is inserted because e.g. PK is autonumber value? I’m asking this that for example after adding some record which is header data you would add some additional data in diffrent table where FK is this PK from beginining of story – master/details story.
Good Question! Yes! If you use Dapper Contrib after it does the insert it returns the @@Identity from the DB, and auto adds that to your object.
Thanks Wade.
I have one more tricky question which is also may be a common sens and use.
For example if you have one record (e.g. Invoice record) and in that record you have more then one FK fields which has it’s own object set or list of objects is it possible to handle it via dapper?
So question would be, is it possible to handle it in both direction and to add and get back from database in this way?
For use:
– If you want to place/add new invoice
– If you want to view invoice
– If you want to edit invoice
Any example would be interesting to see.
Good question again! So reading is easy, Dapper already allows for Joins so this isn’t an issue.
For Adding/Editing. Generally speaking I think Contrib does handle change tracking for navigation properties in one way or another, but I generally simply open a transaction and do the inserts manually back to front. So for example adding the Invoice, then adding the details one by one with the FK etc. And if there is an exception, roll back the transaction. That’s basically what Entity Framework would be doing for you anyway.
Generally speaking, when using large amounts of navigation properties, if you really really need this, then EF Core is a better way to go. I’ve also seen good implementations of CQRS where EF is always used to write as it’s easier to let the framework work out the order of inserts. But Dapper is used for Selects to give you more control over the queries.
Thanks Wade. Sorry for late reply.
I will try by my self soon and will back to you.
The part I like the most is not mentioned here which is how easy it is to insert a list of objects. Here is an example of a generic method I made to do that:
Given that one now can pass an “id” when using “Update”, it becomes as nice as the other operations 🙂