C# – Update records with Dapper

by

You can update records with Dapper by using Execute() with an UPDATE statement and passing in the parameters. Here’s an example:

using Dapper;

void UpdateYear(Movie movie)
{
    using (var con = new SqlConnection(connectionString))
    {
        con.Execute("UPDATE Movies SET YearOfRelease=@year WHERE Id=@id",
            param: new { id = movie.Id, year = movie.YearOfRelease});
    }
}
Code language: C# (cs)

Dapper maps the properties from the param object to the query parameters (ex: it maps year to @year). You can pass in the parameters with an anonymous type (as shown above) or by passing in a model object, like this:

con.Execute("UPDATE Movies SET BoxOffice=@BoxOffice, Views=@Views WHERE Id=@Id",
            param: movie); 
Code language: C# (cs)

Keep the UPDATE statement as concise as possible by only including the bare minimum columns necessary. If a column doesn’t need to be in the update list or in the WHERE clause, leave it out.

Updating multiple records

When updating multiple records, you either want to 1) update records with different values or 2) update records with the same values. I’ll explain how to handle both scenarios.

Updating multiple records with different values

If you’re updating multiple records with different values, then you’ll need to execute multiple UPDATE statements. For example, let’s say you want to set:

  • Movie A YearOfRelease=2021
  • Movie B YearOfRelease=2022

To do this with Dapper, pass in a list of record IDs. It’ll execute the UPDATE statement for each item in the list:

public void UpdateMultipleToDifferentYears(List<Movie> movies)
{
	using (var con = new SqlConnection(connectionString))
	{
		con.Execute("UPDATE Movies SET YearOfRelease=@YearOfRelease WHERE Id=@Id",
			param: movies);
	}
}
Code language: C# (cs)

If you passed in 10 movies, then Dapper will execute this UPDATE statement 10 times.

Updating multiple records with the same value

If you’re updating multiple records with the same value, you can execute a single UPDATE statement with a WHERE IN clause. For example, let’s say you want to update several movies to have YearOfRelease=2022.

Here’s how to do this with Dapper:

using System.Linq;

public void UpdateMultipleWithSameYear(int year, List<Movie> movies)
{
	using (var con = new SqlConnection(connectionString))
	{
		con.Execute("UPDATE Movies SET YearOfRelease=@year WHERE Id IN @ids",
			param: new { year, ids = movies.Select(m => m.Id) });
	}
}
Code language: C# (cs)

This is more efficient than executing multiple UPDATE statements.

Related Articles