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:
- Update invoice in database
- Update existing invoice lines in database
- Insert new invoice lines
- 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.
View Comments (22)
More of Dapper please as there is a lack of good articles like this on the internet.
Awesome article!
Your query examples under "Modifying data" are vulnerable to SQL injection. Please make use of parameterized queries which Dapper has great support for: https://dapper-tutorial.net/parameter-anonymous
It would be more polite to use parameters, yes. But here I'm using just integers and they are not prone to SQL injection attacks.
Thanks for the write-up.
You injected SqlServerConnectionProvider in the SqlServerInvoiceQueries class. How would this be declared in Startup.cs? Is it Scoped, Transient or Singleton and why?
Cheers!
4gzlxr
kkwoc7
si88ct
hgh9xo
ogsy3b
"Returnable instanter" is a legal term used to indicate that
a court order or document must be returned immediately or without delay.
This often appli
Read more
Scrabble
Taxact didn't get my rufend 2008 Jessa R Boyd?
Asked by Anonymous
If you haven't received your refund for the 2008 tax year,
it's important to check the status of your refund with
the IRS. You can do this online using the &quo
Read more
Scrabble
What does D R O S mean in active military terms?
Asked by Anonymous
In active military terms, DROS stands for "Date of Rank and Service."
It is often used to refer to the official date when a
service member was promote
Read more
Scrabble
What is 2.8675 using words?
Asked by Anonymous
The number 2.8675 can be expressed in words as "two point eight six seven five." It represents a decimal value where the whole number part is two,
and
Read more
Scrabble
What are the Letter notes for O come o come Emmanuel on keyboard?
Asked by Anonymous
The letter notes for "O Come, O Come, Emmanuel" on keyboard are as follows:
For the melody in the key of C major, you can play:
C D E C E G A G F E D
Read more
Scrabble
How did p.t. Barnum impact the world?
Asked by Anonymous
P.T. Barnum significantly impacted the world by popularizing the
concept of entertainment through spectacle and promotion, helping to shape
modern circus cultur
Read more
Scrabble
What is denizens?
Asked by Anonymous
"Denizens" refers to individuals or creatures that inhabit or reside in a particular place, often implying a sense of
belonging or familiarity with th
Read more
Scrabble
What is the buseness letter?
Asked by Anonymous
A business letter is a formal written communication used for professional purposes, typically between organizations,
companies, or individuals. It follows a spe
Read more
Scrabble
What is r t f a?
Asked by Anonymous
RTFA stands for "Read The Fine Article" (or sometimes "Read The Fine A**").
It is an internet acronym used to encourage someone to read a sp
Read more
Scrabble
What is grumpily?
Asked by Anonymous
"Grumpily" is an adverb that describes doing something in a grumpy
or irritable manner. It often conveys a sense of
annoyance or displeasure, suggesti
Read more
Scrabble
Who is a no-rec?
Asked by Anonymous
A "no-rec" typically refers to an individual who is not recommended for
a position or opportunity, often due to poor performance or fit during a selec
Read more
Scrabble
What is after quinary?
Asked by Anonymous
After quinary, the next level in the hierarchical classification of industries is "senary." The term "senary" refers to the sixth level and
Read more
Scrabble
What does D O C T O R stand for?
Asked by Anonymous
D O C T O R is an acronym that can stand for various phrases depending on the context.
In a medical context, it often refers to "Diagnosis, Observation, Ca
Read more
Scrabble
What is the difference between beta max n t s c and pal?
Asked by Anonymous
Betamax, NTSC, and PAL are different video formats and standards. Betamax is a tape format developed by Sony, while NTSC (National Television System Committee)
Read more
Scrabble
What letter L means forte?
Asked by Anonymous
In music notation, the letter "L" is not typically associated directly with the term "forte," which means "loud" in Italian and is
Read more
Scrabble
What does l-b-dub-e-n-t mean?
Asked by Anonymous
The term "l-b-dub-e-n-t" is a playful way of spelling out "LBD" or "little black dress," using phonetic sounds. It suggests a fun
Read more
Scrabble
+1
What words can you make with d you p e t y?
Asked by Anonymous
You can make several words with the letters "d," "y," "o," "u," "p," "e," and "t," includi
Read more
Scrabble
What is a golfer's favorite letter?
Asked by Anonymous
A golfer's favorite letter is often said to be "T," as it stands for "tee,
" the starting point for each hole. The tee is where golfers place
Read more
Scrabble
What is the age for latchkey kids in N.Y.?
Asked by Anonymous
In New York, there is no specific legal age that designates when a child can be considered a latchkey kid, as it often depends on individual circumstances and m
Read more
Scrabble
Which one is different A Z F E?
Asked by Anonymous
The letter "Z" is different from the others because it is the only consonant that is positioned at the end of the alphabet, while "A," "
Read more
Scrabble
How do you write letter y?
Asked by Anonymous
To write the lowercase letter "y," start by drawing a short vertical line downwards, then curve it to the left and bring it down to create a tail. For
Read more
Scrabble
What is the physical address for the p.c.h. payment processing center?
Asked by Anonymous
I'm sorry, but I can't provide specific addresses or personal information about businesses. You may want to visit the official website of the payment processing
Read more
Scrabble
What is 1215 s of m c?
Asked by Anonymous
1215 s of m c refers to "1215 seconds of movie content." This likely indicates a duration of a film or video that is 1215 seconds long, which is equiv
Read more
Scrabble
How many f words were there in whiplash movie?
Asked by Anonymous
The movie "Whiplash" contains a significant amount of strong language, including the f-word. It has been reported that there are around 40 instances o
Read more
Scrabble
What characteristics for the letter x?
Asked by Anonymous
The letter "X" is a consonant in the English alphabet, often representing a sound produced by the combination of the letters "ks." It is com
Read more
PreviousNext
Trending Questions
What is a paloverde? What words can be made wiyh these letters preolnas? What maining simsolid? What are all the 5 letter words starting with i? What does w g b t y mean? What does 22 t l d mean? List of words that have double letters? What scrabble words end in the letters eg? What word can you make out of the letters f t n n d o m b? What words can you spell using the letters p r o p e r t i e s? What Seven letter word has a z in it and a d in it? What a 5 letter word with second letter a and 4th letter e? How much does it cost to install porcealin tile? What words can you spell with these letters H o I z s a f? What is a 6 letter word for puncture? What is a six letter word for an anchor cable? What are some nine letter words with 1st letter R and 3rd letter S and 5th letter L and 8th letter U? Each of these letters is? What Scrabble words use r? Words start with t and end with n?
Resources
Leaderboard All Tags Unanswered
Top Categories
Algebra Chemistry Biology World History English Language Arts Psychology Computer Science Economics
Product
Community Guidelines Honor Code Flashcard Maker Study Guides Math Solver FAQ
Company
About Us Contact Us Terms of Use Privacy Policy Disclaimer Cookie Policy IP Issues
Copyright ©2025 Infospace Holdings LLC, A System1 Company. All Rights Reserved. The material on this site can not be reproduced, distributed, transmitted, cached or otherwise used, except with prior written permission of Answers.
79east
3pi24a
555
uhjoap
7khx0o
tag194
555
555
1'"
1
ipn0ym