Using Dapper in ASP.NET Core applications

Times ago I blogged about micro ORM-s. I have been busy through all Covid-19 times learning technical side of DDD and during that I met again my old friend Dapper. There are applications where Dapper is used to query read-only data without overhead coming with ORM-s. Also there are simple applications where for one or another reason developers decided to keep as close to raw SQL as possible. This blog post is brief introduction to Dapper anbd how to use it in ASP.NET Core applications.

What is Micro-ORM?

Micro-ORMs are lightweight object-relational mappers (ORM). They have thin communication interface that makes it easy to query objects from database and – in some cases – to get them easily back to database too. Micro-ORMs doesn’t generate SQL based on object queries like full-blown ORM-s do. Also they usually don’t have change tracking and any other advanced features. As SQL is not generated for us we have to write it manually.

Manually written SQL and lack of powerful features of full-blown ORM-s means one thing – querying of database is easy task to do but updating of object graphs can be challenging.

Introducing Dapper

Dapper is perhaps most popular micro-ORM. It is worked out by Stack Exchange and one of the most popular sites it is running is Stack Overflow. Yeah, the same Stack Overflow we all know and from where tons code come to our systems thaks to Copy-Paste Driven Development.

I like Dapper because it is simple and damn easy to use and understand. It is implemented as set of extension methods to ADO.NET database connection and it sets itself on our way minimally. There are no interfaces to implement, no instances to create and take care of – only simple extension methods to use on objects we have to create anyway.

Here’s the example of list method for invoices.

public async Task<IEnumerable<Invoice>> ListInvoices(int page, int pageSize)
{
    using (var connection = _provider.GetDbConnection())
    {
        var parameters = new { Skip = (page - 1)*pageSize, Take = pageSize };
 
        var query = "select * from Invoices order by CURRENT_TIMESTAMP ";
        query += "OFFSET @Skip ROWS ";
        query += "FETCH NEXT @Take ROWS ONLY";
 
        return await connection.QueryAsync<Invoice>(query, parameters);
    }
}

QueryAsync() method is extension method by Dapper. It takes query and parameters object to build command, execute it and return objects of given type. There are also other methods for returning single object or results from multiple queries that were sent to server with one batch.

Querying database using Dapper

I wrote simple CRUD application on ASP.NET Core to demonstrate how to use Dapper. To keep code clean and SQL in one place I went with query classes similar to ones I demonstrated in my blog post Implementing repository querying interface in EF Core DbContextImplementing repository querying interface in EF Core DbContext.

Let’s take a look at class for querying invoices. Notice GetInvoiceById() method that loads invoice rows from database only if user wants it to do so.

public class SqlServerInvoiceQueries : IInvoiceQueries
{
    private readonly SqlServerConnectionProvider _provider;
 
    public SqlServerInvoiceQueries(SqlServerConnectionProvider provider)
    {
        _provider = provider;
    }
 
    public async Task<Invoice> GetInvoiceById(int id, bool includeRows)
    {
        using(var connection = _provider.GetDbConnection())
        {
            var invoice = await connection.QueryFirstAsync<Invoice>("select * from invoices where id=" + id);
            if(invoice == null)
            {
                return null;
            }
 
            if(includeRows)
            {
                var query = "select * from InvoiceLines where InvoiceId=" + id;
                invoice.InvoiceLines = (await connection.QueryAsync<InvoiceLine>(query)).ToList();
            }
 
            return invoice;
        }
    }
 
    public async Task<IEnumerable<Invoice>> ListInvoices(int page, int pageSize)
    {
        using (var connection = _provider.GetDbConnection())
        {
            var parameters = new { Skip = (page - 1)*pageSize, Take = pageSize };
 
            var query = "select * from Invoices order by CURRENT_TIMESTAMP ";
            query += "OFFSET @Skip ROWS ";
            query += "FETCH NEXT @Take ROWS ONLY";
 
            return await connection.QueryAsync<Invoice>(query, parameters);
        }
    }
}

Queries classed need database connection provider – custom class to provide correct connection to query classes. For ASP.NET Core applications I register connection provider and query classes with framework-level dependency injection. Here is my MSSQL connection provider.

public class SqlServerConnectionProvider
{
    private readonly string _connectionString;
 
    public SqlServerConnectionProvider(string connectionString)
    {
        _connectionString = connectionString;
    }
 
    public IDbConnection GetDbConnection()
    {
        return new SqlConnection(_connectionString);
    }
}

Queries are plain SQL with parameters. Dapper takes away some pain on building parameters and adding these to commands. If our queries grow long, ugly and complex then we can move them to separate files or resource strings to keep query classes clean.

We can inject these query classes to ASP.NET Core controllers and call their methods to get data.

public class HomeController : Controller
{
    private readonly IInvoiceQueries _invoiceQueries;
 
    public HomeController(IInvoiceQueries invoiceQueries)
    {
        _invoiceQueries = invoiceQueries;
    }
 
    public async Task<IActionResult> Index(int page = 1)
    {
        page = Math.Max(1, page);
 
        var invoices = await _invoiceQueries.ListInvoices(page, 10);
 
        return View(invoices);
    }
 
    // More actions follow
}

For very primitive applications I don’t usually even bother to go with query classes. I need to get some simple objects from simple database tables and save them back. Be careful, of course, and don’t use this approach if there’s chance that application will grow. Example of this minimalistic approach can be found from my GitHub repository gpeipman/DapperDemo.

Modifying data using Dapper

When we want to modify data then things get complex pretty fast. Things are easy until we work with primitive entities. But dealing with object graphs is different story.

Let’s take sample entities from one of my demos.

public class Invoice
{
    public int Id { get; set; }
    public DateTime Date { get; set; }
    public DateTime DueDate { get; set; }
    public string Customer { get; set; }
    public string InvoiceNo { get; set; }
 
    public IList<InvoiceLine> InvoiceLines { get; set; }
 
    public Invoice()
    {
        InvoiceLines = new List<InvoiceLine>();
    }
}
 
public class InvoiceLine
{
    public int Id { get; set; }
    public string LineItem { get; set; }
    public decimal Amount { get; set; }
    public string Unit { get; set; }
    public decimal UnitPrice { get; set; }
    public int VatPercent { get; set; }
    public decimal Total { get { return Amount * UnitPrice * (1 + 20 / 100); } set { } }
}

Suppose we have a form where we can create or modify invoice and lines it has. It’s all done dynamically in browser and when user clicks save button then invoice with rows is sent back to server.

Here are ASP.NET Core controller actions to update invoice.

public class HomeController : Controller
{
    private readonly IInvoiceQueries _invoiceQueries;
    private readonly IInvoiceRepository _invoiceRepository;
 
    public HomeController(IInvoiceQueries invoiceQueries,
                          IInvoiceRepository invoiceRepository)
    {
        _invoiceQueries = invoiceQueries;
        _invoiceRepository = invoiceRepository;
    }
 
    public void Index() { }
 
    // Some actions here
 
    public async Task<IActionResult> Edit(int id)
    {
        var invoice = await _invoiceQueries.GetInvoiceById(id, true);
        if (invoice == null)
        {
            return NotFound();
        }
 
        return View(invoice);
    }
 
    [HttpPost]
    public async Task<IActionResult> Edit(Invoice invoice)
    {
        if(!ModelState.IsValid)
        {
            return View(nameof(this.Edit), invoice);
        }
 
        await _invoiceRepository.Save(invoice);
 
        return RedirectToAction(nameof(this.Index));
    }
 
    // More actions follow
}

As insert and delete parts are actually simple ones I leave them out. But update is not so easy when written manually without using full-blown ORM. It consists of four steps that are run in database transaction:

1. Update invoice in database
2. Update existing invoice lines in database
3. Insert new invoice lines
4. Delete removed invoice lines

Just to show how it looks in code here is the example of Update() method of my sample invoice repository.

private async Task Update(Invoice invoice)
{
    using (var connection = await GetOpenConnection())
    using (var transaction = connection.BeginTransaction())
    {
        try
        {
            var query = "UPDATE Invoices SET Date=@Date, DueDate=@DueDate ";
            query += "WHERE Id=@Id";
            await connection.ExecuteAsync(query, invoice, transaction);
            var lineIds = new List<int>();
 
            lineIds.AddRange(invoice.InvoiceLines.Where(l => l.Id != 0).Select(l => l.Id));
 
            foreach (var line in invoice.InvoiceLines)
            {
                if (line.Id == 0)
                {
                    query = "INSERT INTO InvoiceLines (LineItem, Amount, Unit, UnitPrice, VatPercent, InvoiceId)";
                    query += "VALUES (@LineItem, @Amount, @Unit, @UnitPrice, @VatPercent," + invoice.Id + "); ";
                    query += "SELECT CAST(SCOPE_IDENTITY() AS INT)";
 
                    var id = await connection.QueryFirstAsync<int>(query, line, transaction);
                    lineIds.Add(id);
                }
                else
                {
                    query = "UPDATE InvoiceLines SET LineItem=@LineItem,Amount=@Amount,Unit=@Unit,";
                    query += "UnitPrice=@UnitPrice,VatPercent=@VatPercent ";
                    query += "WHERE Id=@Id";
 
                    await connection.ExecuteAsync(query, line, transaction);
                }                        
            }
 
            if (lineIds.Count > 0)
            {
                query = "DELETE FROM InvoiceLines WHERE InvoiceId=" + invoice.Id + " AND ";
                query += "Id NOT IN(" + string.Join(',', lineIds) + ")";
 
                await connection.ExecuteAsync(query, transaction: transaction);
            }
 
            transaction.Commit();
        }
        catch
        {
            transaction.Rollback();
 
            throw;
        }
    }
}

I’m sure we don’t want to write code like this and this is why I don’t consider Dapper or any other micro-ORM as a best choice for modifying data.

Taking best from the both worlds – CQRS

Command Query Responsibility Segregation (CQRS) is pattern first introduced by Greg Young. It’s about separating operations that query data from those that modify system state. On queries side we have data querying – it’s read-only and we are using Dapper. On commands side we modify system state and there we can use EF Core, NHibernate and other full-blown ORM-s.

CQRS illustrated. Image is taken from CQRS page by Martin Fowler.

CQRS is not must-be. It’s like any other pattern – think before you use it. It doesn’t solve all your problems and it’s not a silver bullet. In his CQRS article Martin Fowler warns: “For some situations, this separation can be valuable, but beware that for most systems CQRS adds risky complexity.”

Wrapping up

Using Dapper we can write SQL to execute directly against the database server we are using. It is easy to use and there are just some methods we need to call to get objects out from database. As we saw from examples then querying for data is easy and straightforward but modifying data can be very challenging when it comes to object graphs. Because of this we may want to go with CQRS pattern to have separate interfaces and models for querying and modifying data. CQRS is not a silver bullet and we have to think if we want to apply it in our system or not. For applications with simple object graphs we can go with Dapper based repositories without making things too complex.