C# – Delete records with Dapper

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