Dapper.NET Guide – Delete Record

This tutorial will demonstrate on how to delete a record using Dapper in .NET.

Note that we will continue using the table “Employee” which we used in the previous tutorial. The table “Employee” contains the columns Id, Name and Designation.

Problem Statement

We need to delete a record with the specified employee id.

How to delete a record using Dapper?

Follow the below steps to delete the record from the employee table with the specified id

Use the Employee class that was created earlier (in the previous tutorial).

namespace DapperDemo
{
    public class Employee
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Designation { get; set; }
    }
}

2. Include the Delete method in the IRepository interface which takes the id as input and returns the Employee object.

public interface IEmployeeRepository
{
    List<Employee> GetAll();
    bool Add(Employee employee);
    Employee GetById(int id);
    bool Update(Employee employee);
    bool Delete(int id);
}

3. Implement the Delete method of the IEmployeeRepository in the EmployeeRepository concrete class. In the Delete method, specify the SQL Query for delete the data as shown below.

public bool Delete(int id)
{
    var affectedrows = this.db.Execute("DELETE FROM EMPLOYEE WHERE ID = @Id", new { Id = id });
    return affectedrows > 0;
}

4. The Execute method of the IDbConnection is used which returns the number of affected rows.

5. The last step would be to invoke the Delete method of the employee repository from the page as shown below.

IEmployeeRepository employeeRepository = new EmployeeRepository();
employeeRepository.Delete(2);
Employee emp = employeeRepository.GetById(2);

if (emp == null)
    Console.WriteLine("Employee record is deleted already");

The Complete code of the EmployeeRepository , IEmployeeRepository and Main class is shown below.

IEmployeeRepository.cs

using System.Collections.Generic;
namespace DapperDemo
{
    public interface IEmployeeRepository
    {
        Employee GetById(int id);
        List<Employee> GetAll();
        bool Add(Employee employee);
        bool Update(Employee employee);
        bool Delete(int id);
    }
}

EmployeeRepository.cs

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
namespace DapperDemo
{
    public class EmployeeRepository : IEmployeeRepository
    {
        private IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["conn"].ConnectionString);
        // Get Employee record by Id
        public Employee GetById(int id)
        {
            return this.db.Query<Employee>("SELECT * FROM EMPLOYEE WHERE Id=@Id", new { Id = id }).FirstOrDefault();
        }
        // Retreives the data from the table.
        public List<Employee> GetAll()
        {
            return this.db.Query<Employee>("SELECT * FROM EMPLOYEE").ToList();
        }
        // Add Employee Data
        public bool Add(Employee employee)
        {
            try
            {
                string sql = "INSERT INTO Employee(Name,Designation) values(@Name,@Designation); SELECT CAST(SCOPE_IDENTITY() as int)";
                var returnId = this.db.Query<int>(sql, employee).SingleOrDefault();
                employee.Id = returnId;
            }
            catch(Exception ex)
            {
                return false;
            }
            return true;
        }
        // Update the employee record
        public bool Update(Employee employee)
        {
            string query = "UPDATE EMPLOYEE SET NAME = @Name WHERE Id = @Id";
            var count = this.db.Execute(query, employee);
            return count > 0;
        }
        // Deleting the record
        public bool Delete(int id)
        {
            var affectedrows = this.db.Execute("DELETE FROM EMPLOYEE WHERE ID = @Id", new { Id = id });
            return affectedrows > 0;
        }
    }
}

Program.cs

using System;
using System.Collections.Generic;
namespace DapperDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            IEmployeeRepository employeeRepository = new EmployeeRepository();
            employeeRepository.Delete(2);
            Employee emp = employeeRepository.GetById(2);
          
            if (emp == null)
                Console.WriteLine("Employee record is deleted already");
            
            Console.ReadLine();
        }
        
    }
}