🪴 Shoyeb's Dev Diary

18. Dapper

Thursday Mar 13, 2025

Dapper is a micro ORM, while Entity Framework is a full ORM. Dapper was created by the team behind Stack Overflow. Documentation

Is Dapper an ORM?

  • Dapper is a micro-ORM, offering only a subset of the functionality of full ORMs like Entity Framework Core.
  • Known for speed and simple implementation.

When Should You Use Dapper?

  • Primary reason: Performance.
  • Ideal for frequent, read-only data that changes often.
  • Works well in stateless scenarios (e.g., web apps).
  • Requires SQL queries (does not auto-translate .NET code to SQL).
  • Best for non-normalized databases with minimal schema expectations.
  • Works with any database supporting an ADO.NET provider.
  • Dapper is a lightweight, performant, and easy-to-use choice for efficient data access.
  • Compatible with .NET Framework and .NET Core.

Implementation of dapper in ASP.NET

1. First create two tables:

CREATE TABLE Companies (
    Id INT PRIMARY KEY,
    Name NVARCHAR(100),
    Address NVARCHAR(255),
    Country NVARCHAR(100)
);
 
CREATE TABLE Employees (
    Id INT PRIMARY KEY,
    Name NVARCHAR(100),
    Age INT,
    Position NVARCHAR(100),
    CompanyId INT,
    FOREIGN KEY (CompanyId) REFERENCES Companies(Id)
);

2. Insert some values:

DECLARE @i INT = 1;
DECLARE @companyId INT;
DECLARE @companyCount INT = 10;  -- Number of companies
DECLARE @employeeCount INT = 100;  -- Number of employees
 
-- Insert 10 companies first
WHILE @i <= @companyCount
BEGIN
    INSERT INTO Companies (Id, Name, Address, Country)
    VALUES 
    (@i, 
     'Company ' + CAST(@i AS NVARCHAR(10)), 
     'Address ' + CAST(@i AS NVARCHAR(10)), 
     'Country ' + CAST(@i AS NVARCHAR(10)));
 
    SET @i = @i + 1;
END
 
-- Insert 100 employees with random CompanyId or NULL
SET @i = 1;  -- Reset counter for employees
 
WHILE @i <= @employeeCount
BEGIN
    -- Randomly decide if employee will have a company or not
    IF (RAND() * 2) >= 1  -- 50% chance of assigning a company
    BEGIN
        -- Assign a random CompanyId (choose from the 10 inserted companies)
        SET @companyId = (SELECT TOP 1 Id FROM Companies ORDER BY NEWID());
        
        INSERT INTO Employees (Id, Name, Age, Position, CompanyId)
        VALUES 
        (@i, 
         'Employee ' + CAST(@i AS NVARCHAR(10)), 
         FLOOR(RAND() * (60 - 20 + 1)) + 20,  -- Random age between 20 and 60
         'Position ' + CAST(@i AS NVARCHAR(10)),
         @companyId);
    END
    ELSE
    BEGIN
        -- Employee has no company (CompanyId = NULL)
        INSERT INTO Employees (Id, Name, Age, Position, CompanyId)
        VALUES 
        (@i, 
         'Employee ' + CAST(@i AS NVARCHAR(10)), 
         FLOOR(RAND() * (60 - 20 + 1)) + 20,  -- Random age between 20 and 60
         'Position ' + CAST(@i AS NVARCHAR(10)),
         NULL);  -- No company
    END
 
    SET @i = @i + 1;
END

3. Check values:

SELECT * FROM Employees;
SELECT * FROM Companies;
 
SELECT 
    c.Id AS CompanyId,
    c.Name AS CompanyName,
    c.Address AS CompanyAddress,
    c.Country AS CompanyCountry,
    e.Id AS EmployeeId,
    e.Name AS EmployeeName,
    e.Age AS EmployeeAge,
    e.Position AS EmployeePosition
FROM 
    Companies c
LEFT JOIN 
    Employees e ON c.Id = e.CompanyId
ORDER BY 
    c.Id, e.Id;

4. Now Create an ASP.NET Core API project and install NuGet:

<ItemGroup>
  <PackageReference Include="Dapper" Version="2.1.66" />
  <PackageReference Include="Microsoft.Data.SqlClient" Version="6.0.1" />
  <PackageReference Include="Microsoft.AspNetCore.OpenApi" Version="9.0.1" />
  <PackageReference Include="Swashbuckle.AspNetCore" Version="7.3.1" />
</ItemGroup>

5. Add connection string into appsettings.json:

"ConnectionStrings": {
  "DefaultConnection": "Server=prod.asdbd.com;Database=SADB;User ID=asdinc;Password=ASD007;TrustServerCertificate=True;"
},

6. Add lines in Program.cs:

builder.Services.AddSingleton<DbContext>();
builder.Services.AddScoped<ICompanyRepository, CompanyRepository>();
builder.Services.AddScoped<IEmployeeRepository, EmployeeRepository>();
 
builder.Services.AddSwaggerGen();
builder.Services.AddControllers();
 
app.UseSwagger();
app.UseSwaggerUI();

7. Add a DbContext.cs:

using Microsoft.Data.SqlClient;
using System.Data;
 
namespace WebApplicationB1
{
    public class DbContext
    {
        private readonly IConfiguration _configuration;
        private readonly string? _connectionString;
 
        public DbContext(IConfiguration configuration)
        {
            _configuration = configuration;
            _connectionString = _configuration.GetConnectionString("DefaultConnection");
        }
 
        public IDbConnection CreateConnection() => new SqlConnection(_connectionString);
    }
}

8. Add two entities:

public class Employee
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
    public string Position { get; set; }
    public int CompanyId { get; set; }
}
 
public class Company
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Address { get; set; }
    public string Country { get; set; }
    public List<Employee> Employees { get; set; } = new List<Employee>();
}

9. Add repository interface:

public interface IEmployeeRepository
{
    public Task<IEnumerable<Employee>> GetEmployees();
    public Task<Employee> GetById(int id);
    public Task<bool> Add(Employee company);
    public Task<bool> Edit(Employee company);
    public Task<bool> Delete(int id);
}
 
public interface ICompanyRepository
{
    public Task<IEnumerable<Company>> GetCompanies();
    public Task<Company> GetById(int id);
    public Task<bool> Add(Company company);
    public Task<bool> Edit(Company company);
    public Task<bool> Delete(int id);
}

10. Implement repository:

EmployeeRepository:

using Dapper;
using WebApplicationB1.Entities;
 
namespace WebApplicationB1.Repositories
{
    public class EmployeeRepository : IEmployeeRepository
    {
        private readonly DbContext _context;
 
        public EmployeeRepository(DbContext context)
        {
            _context = context;
        }
 
        // Get Employees
        public async Task<IEnumerable<Employee>> GetEmployees()
        {
            var query = "SELECT * FROM Employees";
 
            using (var connection = _context.CreateConnection())
            {
                var employees = await connection.QueryAsync<Employee>(query);
                return employees.ToList();
            }
        }
 
        // Add a new Employee
        public async Task<bool> Add(Employee employee)
        {
            var query = "INSERT INTO Employees (Id, Name, Age, Position, CompanyId) VALUES (@Id, @Name, @Age, @Position, @CompanyId)";
 
            using (var connection = _context.CreateConnection())
            {
                var result = await connection.ExecuteAsync(query, new
                {
                    employee.Id,
                    employee.Name,
                    employee.Age,
                    employee.Position,
                    employee.CompanyId
                });
 
                return result > 0; // Returns true if the insert was successful
            }
        }
 
        // Delete an Employee by Id
        public async Task<bool> Delete(int id)
        {
            var query = "DELETE FROM Employees WHERE Id = @Id";
 
            using (var connection = _context.CreateConnection())
            {
                var result = await connection.ExecuteAsync(query, new { Id = id });
                return result > 0;
            }
        }
 
        // Edit an existing Employee
        public async Task<bool> Edit(Employee employee)
        {
            var query = "UPDATE Employees SET Name = @Name, Age = @Age, Position = @Position, CompanyId = @CompanyId WHERE Id = @Id";
 
            using (var connection = _context.CreateConnection())
            {
                var result = await connection.ExecuteAsync(query, new
                {
                    employee.Name,
                    employee.Age,
                    employee.Position,
                    employee.CompanyId,
                    employee.Id
                });
 
                return result > 0;
            }
        }
 
        // Get an Employee by Id
        public async Task<Employee> GetById(int id)
        {
            var query = "SELECT * FROM Employees WHERE Id = @Id";
 
            using (var connection = _context.CreateConnection())
            {
                var employee = await connection.QuerySingleOrDefaultAsync<Employee>(query, new { Id = id });
                return employee; // Returns null if not found
            }
        }
    }
}

CompanyRepository:

using Dapper;
using WebApplicationB1.Entities;
 
namespace WebApplicationB1.Repositories
{
    public class CompanyRepository : ICompanyRepository
    {
        private readonly DbContext _context;
 
        public CompanyRepository(DbContext context)
        {
            _context = context;
        }
 
        // Get all Companies
        public async Task<IEnumerable<Company>> GetCompanies()
        {
            var query = "SELECT * FROM Companies";
 
            using (var connection = _context.CreateConnection())
            {
                var companies = await connection.QueryAsync<Company>(query);
                return companies.ToList();
            }
        }
 
        // Get Company by Id (including its Employees)
        public async Task<Company> GetById(int id)
        {
            var query = @"SELECT * FROM Companies WHERE Id = @Id;
                          SELECT * FROM Employees WHERE CompanyId = @Id;";
 
            using (var connection = _context.CreateConnection())
            {
                using (var multi = await connection.QueryMultipleAsync(query, new { Id = id }))
                {
                    var company = await multi.ReadSingleOrDefaultAsync<Company>();
                    if (company != null)
                    {
                        company.Employees = (await multi.ReadAsync<Employee>()).ToList();
                    }
                    return company;
                }
            }
        }
 
        // Add a new Company
        public async Task<bool> Add(Company company)
        {
            var query = "INSERT INTO Companies (Id, Name, Address, Country) VALUES (@Id, @Name, @Address, @Country)";
 
            using (var connection = _context.CreateConnection())
            {
                var result = await connection.ExecuteAsync(query, new
                {
                    company.Id,
                    company.Name,
                    company.Address,
                    company.Country
                });
 
                // Assuming employees are added separately after the company is inserted
                if (result > 0 && company.Employees.Any())
                {
                    // Insert employees if any exist
                    foreach (var employee in company.Employees)
                    {
                        var employeeQuery = "INSERT INTO Employees (Id, Name, Age, Position, CompanyId) VALUES (@Id, @Name, @Age, @Position, @CompanyId)";
                        await connection.ExecuteAsync(employeeQuery, new
                        {
                            employee.Name,
                            employee.Age,
                            employee.Position,
                            CompanyId = company.Id
                        });
                    }
                }
 
                return result > 0;
            }
        }
 
        // Delete a Company by Id
        public async Task<bool> Delete(int id)
        {
            // Deleting employees associated with the company
            var deleteEmployeesQuery = "DELETE FROM Employees WHERE CompanyId = @Id";
            var deleteCompanyQuery = "DELETE FROM Companies WHERE Id = @Id";
 
            using (var connection = _context.CreateConnection())
            {
                // Start by deleting employees
                var employeesDeleted = await connection.ExecuteAsync(deleteEmployeesQuery, new { Id = id });
                var companyDeleted = await connection.ExecuteAsync(deleteCompanyQuery, new { Id = id });
 
                return companyDeleted > 0;
            }
        }
 
        // Edit an existing Company
        public async Task<bool> Edit(Company company)
        {
            var query = "UPDATE Companies SET Name = @Name, Address = @Address, Country = @Country WHERE Id = @Id";
 
            using (var connection = _context.CreateConnection())
            {
                var result = await connection.ExecuteAsync(query, new
                {
                    company.Name,
                    company.Address,
                    company.Country,
                    company.Id
                });
 
                return result > 0;
            }
        }
    }
}

11. Add Controller:

EmployeesController:

using Microsoft.AspNetCore.Mvc;
using WebApplicationB1.Entities;
using WebApplicationB1.Repositories;
 
namespace WebApplicationB1.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class EmployeesController : ControllerBase
    {
        private readonly IEmployeeRepository _employeeRepo;
        public EmployeesController(IEmployeeRepository employeeRepo)
        {
            _employeeRepo = employeeRepo;
        }
        [HttpGet]
        public async Task<IActionResult> GetEmployees()
        {
            try
            {
                var employees = await _employeeRepo.GetEmployees();
                return Ok(employees);
            }
            catch (Exception ex)
            {
                return StatusCode(500, ex.Message);
            }
        }
 
        [HttpGet("{id}")]
        public async Task<IActionResult> GetById(int id)
        {
            try
            {
                return Ok(await _employeeRepo.GetById(id));
            }
            catch (Exception ex)
            {
                return StatusCode(500, ex.Message);
            }
        }
 
        [HttpPost]
        public async Task<IActionResult> Add([FromBody] Employee employee)
        {
            try
            {
                return Ok(await _employeeRepo.Add(employee));
            }
            catch (Exception ex)
            {
                return StatusCode(500, ex.Message);
            }
        }
 
        [HttpPut]
        public async Task<IActionResult> Edit([FromBody] Employee employee)
        {
            try
            {
                return Ok(await _employeeRepo.Edit(employee));
            }
            catch (Exception ex)
            {
                return StatusCode(500, ex.Message);
            }
        }
 
        [HttpDelete]
        public async Task<IActionResult> Delete(int id)
        {
            try
            {
                return Ok(await _employeeRepo.Delete(id));
            }
            catch (Exception ex)
            {
                return StatusCode(500, ex.Message);
            }
        }
    }
}

CompaniesController:

using Microsoft.AspNetCore.Mvc;
using WebApplicationB1.Entities;
using WebApplicationB1.Repositories;
 
namespace WebApplicationB1.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class CompaniesController : ControllerBase
    {
        private readonly ICompanyRepository _companyRepo;
        public CompaniesController(ICompanyRepository companyRepo)
        {
            _companyRepo = companyRepo;
        }
        [HttpGet]
        public async Task<IActionResult> GetCompanies()
        {
            try
            {
                var companies = await _companyRepo.GetCompanies();
                return Ok(companies);
            }
            catch (Exception ex)
            {
                return StatusCode(500, ex.Message);
            }
        }
 
        [HttpGet("{id}")]
        public async Task<IActionResult> GetById(int id)
        {
            try
            {
                return Ok(await _companyRepo.GetById(id));
            }
            catch (Exception ex)
            {
                return StatusCode(500, ex.Message);
            }
        }
 
        [HttpPost]
        public async Task<IActionResult> Add([FromBody] Company company)
        {
            try
            {
                return Ok(await _companyRepo.Add(company));
            }
            catch (Exception ex)
            {
                return StatusCode(500, ex.Message);
            }
        }
 
        [HttpPut]
        public async Task<IActionResult> Edit([FromBody] Company company)
        {
            try
            {
                return Ok(await _companyRepo.Edit(company));
            }
            catch (Exception ex)
            {
                return StatusCode(500, ex.Message);
            }
        }
 
        [HttpDelete]
        public async Task<IActionResult> Delete(int id)
        {
            try
            {
                return Ok(await _companyRepo.Delete(id));
            }
            catch (Exception ex)
            {
                return StatusCode(500, ex.Message);
            }
        }
    }
}

Finally, when you run the project, it will display like this: