Dapper delete operation

1. Delete operations also use db.Execute() to run DELETE statements and return the number of affected rows.
2. Deletion usually targets records by their unique primary‑key ID.
3. A WHERE clause is mandatory! Omitting WHERE will wipe the whole table.
4. Bad practice: building SQL with string concatenation, which opens SQL‑injection risks; ✅ Proper approach: parameterized queries.

Original note: Entity deletion generally relies on a unique Id; core SQL pattern: DELETE ... WHERE PrimaryKey=Value

The example below is vulnerable to SQL injection

// Risky implementation: string concatenation leads to SQL‑injection risks
using (IDbConnection db = new MySqlConnection(conn))
{
    var id = 1;
    string sqlQuery = "DELETE FROM student WHERE Id = " + id;
    int rowsAffected = db.Execute(sqlQuery);

    // Query to verify deletion result
    var info = db.Query<Student>("SELECT * FROM student WHERE Id = " + id).SingleOrDefault();
}Code language: C# (cs)

Standard Parameterized Implementation

using System.Data;
using MySql.Data.MySqlClient;
using Dapper;
using System.Configuration;

static void Main(string[] args)
{
    var conn = ConfigurationManager.ConnectionStrings["StudentConnection"].ConnectionString;
    using (IDbConnection db = new MySqlConnection(conn))
    {
        // Delete SQL with parameter placeholders
        string sqlDelete = "DELETE FROM student WHERE Id = @Id";
        int targetId = 1;

        // Execute delete, pass parameters via anonymous class
        int rowsAffected = db.Execute(sqlDelete, new { Id = targetId });

        // Verification: query should return null after deletion
        var student = db.Query<Student>(
            "SELECT * FROM student WHERE Id = @Id",
            new { Id = targetId }
        ).SingleOrDefault();
    }
    Console.ReadKey();
}

// Student entity class
public class Student
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Email { get; set; }
}Code language: C# (cs)

Pass Parameters Using Entity Object

string sqlDelete = "DELETE FROM student WHERE Id = @Id";
var stu = new Student { Id = 1 };
int rowsAffected = db.Execute(sqlDelete, stu);Code language: C# (cs)

Remarks

1. Return value explanation
   rowsAffected > 0: target rows deleted successfully; rowsAffected = 0: no matching records found.
2. Critical warning
   DELETE FROM student; without conditions clears the whole table, never write this by mistake.
3. Security rule
   All externally‑supplied IDs and filter conditions must use @xxx parameters. Never concatenate raw strings.

Dapper delete operation