How to Delete records with Dapper in C#

Deleting a Single Record

To delete a single record, you can execute a DELETE statement that targets a specific record based on its ID.

using Dapper;
using System.Data.SqlClient;

void Delete(Customer customer)
{
    using (var con = new SqlConnection(connectionString))
    {
        con.Execute("DELETE FROM Customers WHERE Id=@id", 
                    param: new { id = customer.Id });
    }
}

his deletes a single customer record from the Customers table.

Deleting Multiple Records

To delete multiple records at once, you can use a DELETE statement with a WHERE IN clause, which includes a list of IDs of the records you want to delete.

using Dapper;
using System.Linq;

void DeleteMultiple(List<Customer> customers)
{
    using (var con = new SqlConnection(connectionString))
    {
        con.Execute("DELETE FROM Customers WHERE Id IN @ids", 
                    param: new { ids = customers.Select(c => c.Id) });
    }
}

This deletes all customers whose IDs are in the provided list. Dapper efficiently passes in the list of IDs, which allows you to delete all the selected records in a single statement.

Alternatively, you can pass the entire list of model objects to Dapper:

using Dapper;

void DeleteMultiple(List<Customer> customers)
{
    using (var con = new SqlConnection(connectionString))
    {
        con.Execute("DELETE FROM Customers WHERE Id=@Id", 
                    param: customers);
    }
}

In this example, Dapper executes individual DELETE statements for each record in the list, which is slightly less efficient than executing one statement with a WHERE IN clause.

Deleting All Records

If you need to delete all records in a table, you can execute a simple DELETE statement without a WHERE clause:

using Dapper;

void DeleteAll()
{
    using (var con = new SqlConnection(connectionString))
    {
        con.Execute("DELETE FROM Customers");
    }
}

This deletes all customer records from the Customers table. You may also consider using commands like TRUNCATE TABLE or DROP TABLE for clearing a table, but the DELETE statement is typically preferred due to its simplicity.