Dapper In .NET Core – Part 3 – Updates/Inserts/Deletes

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

Updating A Record

So.. Here’s where things with Dapper get kinda rough. You see, to update a record we use the “Execute” method from Dapper like so :

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Execute("UPDATE Event SET EventName = 'NewEventName' WHERE Id = 1");
}

And of course you can use parameters with the execute statement also. So we could change that to look like :

using (SqlConnection connection = new SqlConnection(connectionString))
{
    var eventName = "NewEventName";
    var eventId = 1;
    connection.Execute("UPDATE Event SET EventName = @EventName WHERE Id = @EventId", new { EventName = eventName, EventId = eventId } );
}

I mean yeah, it works but… It’s kinda rough right? Imagine if we wanted to update multiple fields at once how unweildy this statement might get. But then again, maybe that’s good. We are only updating exactly what we want to update and nothing more.

It’s sort of a “Great power comes great responsibility” type issue.

Inserting A Record

If you thought updating a record was rough, wait till you see inserting.

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Execute("INSERT INTO Event (EventLocationId, EventName, EventDate, DateCreated) VALUES(1, 'InsertedEvent', '2019-01-01', GETUTCDATE())");
}

Again… It’s pretty basic. Infact all dapper is really doing is executing our insert statement, but we are kinda doing all the leg work here. Similar to updating, we can use parameters here to clean the values up a bit, but it’s still a lot of manual work. Obviously each time we create a new column, we need to come back to this statement and make sure it works as we intend it to.

Delete A Record

No big surprises here.

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Execute("DELETE FROM Event WHERE Id = 4");
}

The execute method kinda becomes our catch all for “Execute this statement and don’t worry about the result”.

How About CQRS?

It’s pretty common for CQRS to be a hot topic when talking about Dapper. After all, we’ve kind of seen that while reading data in Dapper is super powerful, slick, and easy. Writing feels cumbersome. For this reason I’ve found myself using doing writes using a different ORM (EF or NHibernate), but sticking with Dapper for querying. It works, but it’s not required (As we will soon see!).

What’s Next?

So updating and inserting records is a bit oof. But there is an extension library for Dapper called Dapper.Contrib that actually makes this super simple. Let’s take a look at that!