Trong bài viết này, chúng ta sẽ tìm hiểu cách sử dụng Dapper trong dự án .NET Core Web API. Chúng ta sẽ nói về Dapper một cách tổng thể nhất vầ cách sử dụng các truy vấn và thực thi khác nhau, cách thực thi các thủ tục và cách tạo nhiều truy vấn. Để tránh sử dụng các truy vấn Dapper trực tiếp bên trong controller, chúng ta sẽ tạo một lớp repository đơn giản để bao bọc các logic.

Dapper là gì?

Dapper là một ORM (Object-Relational Mapper) hay nói chính xác hơn là Micro ORM, chúng ta có thể sử dụng nó để giao tiếp với cơ sở dữ liệu trong các dự án của mình. Bằng cách sử dụng Dapper, chúng ta có thể viết các câu lệnh SQL như thể chúng ta sẽ làm điều đó trong SQL Server.

Dapper có hiệu suất tuyệt vời vì nó không biên dịch các truy vấn mà ta sẽ viết trong .NET sang SQL. Điều quan trọng là Dapper là SQL Injection an toàn vì chúng ta có thể sử dụng các truy vấn được tham số hóa và đó là điều chúng ta nên luôn làm.

Một điều quan trọng nữa là Dapper hỗ trợ nhiều provider cơ sở dữ liệu. Vì vậy, nó không chỉ dành cho SQL Server mà có thể sử dụng nó với PostgreSQL, MySQL hoặc những thứ khác.

Nó mở rộng từ IDbConnection của ADO.NET và cung cấp các phương thức mở rộng hữu ích để truy vấn cơ sở dữ liệu. Tất nhiên, ta phải viết các truy vấn tương thích với các cơ sở dữ liệu đang dùng.

Khi chúng ta nói về các phương thức mở rộng này, chúng ta phải nói rằng Dapper hỗ trợ cả thực thi phương thức đồng bộ (synchronous) và bất đồng bộ (asynchronous). Trong bài viết này, chúng ta sẽ sử dụng phiên bản không đồng bộ của các phương thức đó.

Các Methods mở rộng

Dapper extends từ interface IDbConnection bằng nhiều phương pháp sau:

Execute: thực hiện lệnh một hoặc nhiều lần và trả về số hàng tương ứng.
Query: thực hiện một truy vấn và ánh xạ kết quả.
QueryFirst: thực hiện một truy vấn và ánh xạ kết quả đầu tiên.
QueryFirstOrDefault: thực hiện một truy vấn và ánh xạ kết quả đầu tiên hoặc một giá trị mặc định nếu chuỗi không chứa phần tử.
QuerySingle: thực hiện một truy vấn và ánh xạ kết quả. Nó “throws an exception” nếu không có chính xác một phần tử nào trong chuỗi.
QuerySingleOrDefault: thực hiện một truy vấn và ánh xạ kết quả hoặc một giá trị mặc định nếu chuỗi trống. Nó trả về một ngoại lệ nếu có nhiều hơn một phần tử trong chuỗi
QueryMultiple: thực thi nhiều truy vấn trong cùng một lệnh và ánh xạ kết quả.

Như đã để cập trước đó, Dapper cung cấp phiên bản bất đồng bộ (asynchronous) cho tất cả các phương thức này (ExecuteAsync, QueryAsync, QueryFirstAsync, QueryFirstOrDefaultAsync, QuerySingleAsync, QuerySingleOrDefaultAsync, QueryMultipleAsync).

Sử dụng Dapper Queries

Hãy bắt đầu với một ví dụ trong đó chúng ta trả về tất cả các companies từ cơ sở dữ liệu.

Vì vậy, điều đầu tiên chúng tôi muốn làm là cập nhật lớp interface ICompanyRepository:

public interface ICompanyRepository
{
    public Task<IEnumerable<Company>> GetCompanies();
}

Sau đó, hãy triển khai phương thức này trong class CompanyRepository:

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();
    }
}

Đầu tiên chúng ta tạo một biến query là một chuỗi lưu trữ truy vấn SQL để lấy tất cả các companies. Sau đó, bên trong câu lệnh using, chúng ta sử dụng đối tượng DapperContext để tạo đối tượng SQLConnection (hay chính xác hơn là một đối tượng IDbConnection) bằng cách gọi phương thức CreateConnection.

Như bạn có thể thấy, ngay sau khi ngừng sử dụng kết nối, ta phải loại bỏ (dispose) nó. Khi tạo một kết nối, chúng ta có thể sử dụng nó để gọi phương thức QueryAsync và chuyển truy vấn làm đối số. Vì phương thức QueryAsync trả về IEnumerable<T>, ta chuyển đổi nó thành một danh sách ngay khi muốn trả về một kết quả.

Sau khi có interface và class repository, và bây giờ ta tạo CompaniesController với code sau đây:

[Route("api/companies")]
[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)
        {
            //log error
            return StatusCode(500, ex.Message);
        }
    }
}

Ở đây chúng ta đưa vào repository thông qua DI và sử dụng nó để gọi phương thức GetCompanies.

Sử dụng Parameters với Dapper Queries

Như chúng ta đã nói ở phần đầu của bài viết này, Dapper hỗ trợ các truy vấn được tham số hóa, giúp nó an toàn 100% SQL Injection. Nó hỗ trợ các tham số anonymous, dynamic, list, string, và table-valued. Ta chủ yếu sẽ sử dụng các tham số động (dynamic) và ẩn danh (anonymous) trong bài viết này.

Bây giờ, ta cập nhật interface:

public interface ICompanyRepository
{
    public Task<IEnumerable<Company>> GetCompanies();
    public Task<Company> GetCompany(int id);
}

Phương thức này gần giống như phương thức trước, nhưng có một ngoại lệ vì chúng ta đang sử dụng phương thức QuerySingleOrDefaultAsync ở đây và cung cấp một đối tượng ẩn danh làm đối số thứ hai. Mình sẽ chỉ cho bạn cách sử dụng các tham số động trong ví dụ tiếp theo, nơi mà ta sẽ tạo một thực thể company mới trong cơ sở dữ liệu.

Tiếp theo, ta cập nhật code trong controller:

[HttpGet("{id}"]
public async Task<IActionResult> GetCompany(int id)
{
    try
    {
        var company = await _companyRepo.GetCompany(id);
        if (company == null)
            return NotFound();
        return Ok(company);
    }
    catch (Exception ex)
    {
        //log error
        return StatusCode(500, ex.Message);
    }
}

Thêm thực thể mới (Create) sử dụng Execute(Async) Method

Bây giờ, chúng tôi sẽ xử lý một request POST trong API và sử dụng phương thức ExecuteAsync này để tạo một thực thể company mới trong cơ sở dữ liệu.

Điều đầu tiên chúng ta sẽ làm là tạo một thư mục Dto mới và bên trong nó là một class CompanyForCreationDto mới mà chúng ta sẽ sử dụng cho yêu cầu POST:

public class CompanyForCreationDto
{
    public string Name { get; set; }
    public string Address { get; set; }
    public string Country { get; set; }
}

Sau khi tạo class ta cập nhật code trong interface repository:

public interface ICompanyRepository
{
    public Task<IEnumerable<Company>> GetCompanies();
    public Task<Company> GetCompany(int id);
    public Task CreateCompany(CompanyForCreationDto company);
}

Tiếp theo, ta triển khai code với phương thức sau trong repository:

public async Task CreateCompany(CompanyForCreationDto company)
{
    var query = "INSERT INTO Companies (Name, Address, Country) VALUES (@Name, @Address, @Country)";
    var parameters = new DynamicParameters();
    parameters.Add("Name", company.Name, DbType.String);
    parameters.Add("Address", company.Address, DbType.String);
    parameters.Add("Country", company.Country, DbType.String);
    using (var connection = _context.CreateConnection())
    {
        await connection.ExecuteAsync(query, parameters);
    }
}

Update và Delete thực thể

Làm việc với cập nhật và xóa hiện khá đơn giản vì chúng cũng tương tự và ta đã có tất cả kiến thức để thực hiện.

Đầu tiên ta tạo class Dto cho update:

public class CompanyForUpdateDto
{
    public string Name { get; set; }
    public string Address { get; set; }
    public string Country { get; set; }
}

Sau đó cập nhật interface:

public interface ICompanyRepository
{
    public Task<IEnumerable<Company>> GetCompanies();
    public Task<Company> GetCompany(int id);
    public Task<Company> CreateCompany(CompanyForCreationDto company);
    public Task UpdateCompany(int id, CompanyForUpdateDto company);
    public Task DeleteCompany(int id);
}

Và triển khai trong class repository:

public async Task UpdateCompany(int id, CompanyForUpdateDto company)
{
    var query = "UPDATE Companies SET Name = @Name, Address = @Address, Country = @Country WHERE Id = @Id";
    var parameters = new DynamicParameters();
    parameters.Add("Id", id, DbType.Int32);
    parameters.Add("Name", company.Name, DbType.String);
    parameters.Add("Address", company.Address, DbType.String);
    parameters.Add("Country", company.Country, DbType.String);
    using (var connection = _context.CreateConnection())
    {
        await connection.ExecuteAsync(query, parameters);
    }
}
public async Task DeleteCompany(int id)
{
    var query = "DELETE FROM Companies WHERE Id = @Id";
    using (var connection = _context.CreateConnection())
    {
        await connection.ExecuteAsync(query, new { id });
    }
}

Như bạn có thể thấy, không có gì mới với hai phương thức này. Ta có một truy vấn, các tham số và thực thi các câu lệnh với phương thức ExecuteAsync.

Cuối cùng, chúng ta phải thêm hai actions này vào controller:

[HttpPut("{id}")]
public async Task<IActionResult> UpdateCompany(int id, CompanyForUpdateDto company)
{
    try
    {
        var dbCompany = await _companyRepo.GetCompany(id);
        if (dbCompany == null)
            return NotFound();
        await _companyRepo.UpdateCompany(id, company);
        return NoContent();
    }
    catch (Exception ex)
    {
        //log error
        return StatusCode(500, ex.Message);
    }
}


[HttpDelete("{id}")]
public async Task<IActionResult> DeleteCompany(int id)
{
    try
    {
        var dbCompany = await _companyRepo.GetCompany(id);
        if (dbCompany == null)
            return NotFound();
        await _companyRepo.DeleteCompany(id);
        return NoContent();
    }
    catch (Exception ex)
    {
        //log error
        return StatusCode(500, ex.Message);
    }
}

Thực thi Stored Procedures trong Dapper

Trước khi mình chỉ cho bạn cách sử dụng Dapper để gọi một thủ tục được lưu trữ, ta phải tạo một thủ tục trong cơ sở dữ liệu của mình:

USE [DapperASPNetCore]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ShowCompanyForProvidedEmployeeId] @Id int
AS
SELECT c.Id, c.Name, c.Address, c.Country
FROM Companies c JOIN Employees e ON c.Id = e.CompanyId
Where e.Id = @Id
GO

Thủ tục này trả về Name, Address, và Country từ một company có employee với giá trị Id được cung cấp.

Bây giờ ta cập nhật code trong interface repository:

public interface ICompanyRepository
{
    public Task<IEnumerable<Company>> GetCompanies();
    public Task<Company> GetCompany(int id);
    public Task<Company> CreateCompany(CompanyForCreationDto company);
    public Task UpdateCompany(int id, CompanyForUpdateDto company);
    public Task DeleteCompany(int id);
    public Task<Company> GetCompanyByEmployeeId(int id);
}

Tiếp theo, ta cập nhật code trong class repository:

public async Task<Company> GetCompanyByEmployeeId(int id)
{
    var procedureName = "ShowCompanyForProvidedEmployeeId";
    var parameters = new DynamicParameters();
    parameters.Add("Id", id, DbType.Int32, ParameterDirection.Input);
    using (var connection = _context.CreateConnection())
    {
        var company = await connection.QueryFirstOrDefaultAsync<Company>
            (procedureName, parameters, commandType: CommandType.StoredProcedure);
        return company;
    }
}

Ở đây, chúng ta tạo một biến chứa tên procedure và tham số với một tham số duy nhất bên trong. Bởi vì thủ tục được lưu trữ trả về một giá trị, ta sử dụng phương thức QueryFirstOrDefaultAsync để thực thi nó. Hãy chú ý rằng nếu thủ tục của bạn không trả về giá trị, bạn có thể sử dụng phương thức ExecuteAsync này để thực thi.

Như thường lệ, ta phải thêm một action khác trong controller của mình:

[HttpGet("ByEmployeeId/{id}")]
public async Task<IActionResult> GetCompanyForEmployee(int id)
{
    try
    {
        var company = await _companyRepo.GetCompanyByEmployeeId(id);
        if (company == null)
            return NotFound();
        return Ok(company);
    }
    catch (Exception ex)
    {
        //log error
        return StatusCode(500, ex.Message);
    }
}

Thực thi nhiều câu lệnh SQL với Single Query

Trong một số tình huống, bạn muốn data response là bao gồm các sub document, nhưng khi thực hiện trong Linq thật sự rất phức tạp và hiệu năng rất chậm. Và chúng ta có thể dễ dàng thực thi nhiều câu lệnh SQL và trả về nhiều kết quả trong một truy vấn bằng cách sử dụng phướng thức QueryMultipleAsync này.

Trước tiên, ta cập nhật code trong interface repository:

public interface ICompanyRepository
{
    ...
    public Task<Company> GetCompanyEmployeesMultipleResults(int id);
}

Và class repository:

public async Task<Company> GetCompanyEmployeesMultipleResults(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 }))
    {
        var company = await multi.ReadSingleOrDefaultAsync<Company>();
        if (company != null)
            company.Employees = (await multi.ReadAsync<Employee>()).ToList();
        return company;
    }
}

Như bạn có thể thấy, biến truy vấn chứa hai câu lệnh SELECT. Đầu tiên sẽ trả lại một company duy nhất và công ty thứ hai sẽ trả lại tất cả nhân viên cho công ty đó.

Sau đó, chúng ta tạo một kết nối và sau đó sử dụng kết nối đó để gọi phương thức QueryMultipleAsync. Khi chúng ta nhận được nhiều kết quả bên trong, chúng tôi có thể trích xuất cả hai kết quả (công ty và nhân viên của mỗi công ty đó) bằng cách sử dụng các phương thức ReadSignleOrDefaultAsync và ReadAsync. Phương thức đầu tiên trả về một kết quả duy nhất, trong khi phương thức thứ hai trả về một tập hợp.

Multiple Mapping

Trong ví dụ trước, chúng ta đã sử dụng hai câu lệnh SQL để trả về hai kết quả và sau đó nối chúng lại với nhau trong một đối tượng.

Nhưng thông thường, đối với những truy vấn như vậy, chúng ta không muốn viết hai câu lệnh SQL, chúng tôi muốn sử dụng một mệnh đề JOIN và tạo một câu lệnh SQL duy nhất. Tất nhiên, nếu chúng ta viết nó như vậy, chúng ta không thể sử dụng phương thức QueryMultipleAsync nữa. Chúng ta phải sử dụng một kỹ thuật ánh xạ nhiều lần với một phương thức QueryAsync.

Cụ thể đầu tiên bạn cần cập nhật interface repository:

public interface ICompanyRepository
{
    ...
    public Task<List<Company>> GetCompaniesEmployeesMultipleMapping();
}

Sau đó là class repository:

public async Task<List<Company>> GetCompaniesEmployeesMultipleMapping()
{
    var query = "SELECT * FROM Companies c JOIN Employees e ON c.Id = e.CompanyId";
    using (var connection = _context.CreateConnection())
    {
        var companyDict = new Dictionary<int, Company>();
        var companies = await connection.QueryAsync<Company, Employee, Company>(
            query, (company, employee) =>
            {
                if (!companyDict.TryGetValue(company.Id, out var currentCompany))
                {
                    currentCompany = company;
                    companyDict.Add(currentCompany.Id, currentCompany);
                }
                currentCompany.Employees.Add(employee);
                return currentCompany;
            }
        );
        return companies.Distinct().ToList();
    }
}

Trước tiên, ta tạo một truy vấn và bên trong câu lệnh using một kết nối mới. Sau đó, chúng tôi tạo một dictionary mới để giữ cho các companies của chúng ta tiếp tục hoạt động. Để trích xuất dữ liệu từ cơ sở dữ liệu, ta sử dụng phương thức QueryAsync, nhưng lần này nó có một cú pháp mới mà chúng tôi chưa thấy cho đến nay.

Hai kiểu đầu tiên là kiểu đầu vào mà chúng ta sẽ làm việc và kiểu thứ ba là kiểu trả về. Phương thức này chấp nhận truy vấn như một tham số và cũng là một Func chấp nhận hai tham số. Bên trong delegate, chúng tôi cố gắng trích xuất một công ty theo giá trị Id của nó. Nếu nó không tồn tại, chúng tôi lưu trữ nó bên trong biến currentCompany và thêm nó vào dictionary. Ngoài ra, chúng ta cần chỉ định tất cả nhân viên cho công ty hiện tại đó và kết quả là trả lại từ một Func.

Sau khi ánh xạ của chúng tôi hoàn tất, chúng tôi chỉ trả về một kết quả khác biệt được chuyển đổi thành một danh sách.

Tất cả những gì chúng ta phải làm là thêm một action vào controller:

[HttpGet("MultipleMapping")]
public async Task<IActionResult> GetCompaniesEmployeesMultipleMapping()
{
    try
    {
        var company = await _companyRepo.GetCompaniesEmployeesMultipleMapping();
        return Ok(company);
    }
    catch (Exception ex)
    {
        //log error
        return StatusCode(500, ex.Message);
    }
}

Lời kết

Sau cùng, ta có thể thấy rằng Dapper chỉ là một công cụ. Những gì nó làm là:

Làm cho việc tham số hóa các truy vấn một cách chính xác trở nên dễ dàng
Làm cho việc thực thi các truy vấn trở nên dễ dàng (vô hướng, nhiều hàng,..)
Biến kết quả thành đối tượng dễ dàng
Rất hiệu quả và nhanh chóng

Những gì nó không làm là:

Tạo ra các class model cho bạn
Tạo các truy vấn cho bạn
Theo dõi các đối tượng và những thay đổi của chúng, để bạn có thể gọi SubmitChanges()

Mong bài viết hữu ích, chúc các bạn thành công.

Hieu Ho.

.Net Core

Sử dụng Dapper trong .Net Core Web API