C# – Delete records with Dapper

by

You can delete records with Dapper by using Execute() with a DELETE statement and specifying the record ID as a parameter. Here’s an example:

using Dapper;

void Delete(Movie movie)
{
    using (var con = new SqlConnection(connectionString))
    {
        con.Execute("DELETE FROM Movies WHERE Id=@id",
            param: new { id = movie.Id });
    }
}
Code language: C# (cs)

This deletes a single record from the Movies table.

Delete multiple records

When you’re deleting multiple records, you can efficiently execute a single DELETE statement with a WHERE clause including all of the record IDs to delete. The simplest way to do that is to use WHERE IN and pass in a list of all the IDs. Here’s an example:

using Dapper;
using System.Linq;

void DeleteMultiple(List<Movie> movies)
{
    using (var con = new SqlConnection(connectionString))
    {
        con.Execute("DELETE FROM Movies WHERE Id IN @ids",
            param: new { ids = movies.Select(m => m.Id) });
    }
}
Code language: C# (cs)

Your other option is to pass in a list of model objects. This simplifies the code a little bit. Here’s an example:

using Dapper;

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

When you do this, Dapper actually executes multiple DELETE statements (one for each model object), which is less efficient than executing a single DELETE statement that deletes all records. This is a tradeoff between slightly simpler code and efficiency. I’d only suggest using this option if you’re dealing with a small number of records.

Delete all records

Most of the time you’ll want to delete specific records, but sometimes you may need to delete ALL records in a table. To do that with Dapper, you can execute an unconditional DELETE statement: 

using Dapper;

void DeleteAll()
{
    using (var con = new SqlConnection(connectionString))
    {
        con.Execute("DELETE FROM Movies");
    }
}
Code language: C# (cs)

Note: There are other ways to clear a table, such as using TRUNCATE TABLE and DROP/CREATE TABLE. I’d suggest sticking with a simple DELETE in most scenarios. Use whatever option you want.

Leave a Comment