Working with Dapper

Dapper is a micro ORM created by StackOverflow. It is pretty simple to use and really fast. For this reasons, I have been using Dapper to retrieve the data from the database instead of use Entity Framework. By the way, EF has some powerfull features and I have just used it for database input. If you need performance to read... use Dapper not EF!!!

- Hey Alex, I could use ADO.NET to read!!! - Yes you can, and there is nothing faster than it!

In this post, I would like to encourage you to check incredible Dapper's features and why Dapper has helped me to write less and clean code.

Installing Dapper
To install Dapper dot net, run the following command in the Package Manager Console:
PM> Install-Package Dapper
Add namespace: Using Dapper;

A key feature of Dapper is performance.
Dapper Performance
Reference: Dapper - a simple object mapper for .Net

Lets get started with examples! See below my relational database:
Relational Database

My first example is pretty simple. This method returns a collection of Products.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
public class ProductRepository
{
    protected DapperDemoContext Db;
    public ProductRepository(DapperDemoContext context)
    {
        Db = context;
    }
    public IEnumerable<Product> GetAllProducts()
    {
        var cn = Db.Database.Connection;
	var sql = @"SELECT * FROM Product";
	return cn.Query<Product>(sql);
    }
}

For my second example, I will use Multi Mapping feature and Dapper allows me to map a single row to multiple objects. See below my repository class that contain a method to return an IEnumerable of QuoteRequest object.
In this case, I want to take the QuoteRequest object and put it inside Product, PurchaseOrder and Supplier objects.

QuoteRequestRepository.cs

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
public class QuoteRequestRepository
{
    protected DapperDemoContext Db;
 
    public QuoteRequestRepository(DapperDemoContext context)
    {
        Db = context;
    }
    public IEnumerable<QuoteRequest> GetQuotationsByPurchaseOrderId(int purchaseId)
    {
        var cn = Db.Database.Connection;
        var sql = @"SELECT t1.QuoteRequestId, t1.PurchaseOrderId, t1.PriceQuoted, t1.QuantityQuoted " +
            ",t2.ProductId, t2.Name, t3.PurchaseOrderItemId, t3.Quantity " +
            ",t4.SupplierId,t4.Name " +
            "FROM QuoteRequest as t1 " +
            "INNER JOIN Product as t2 " +
            "ON t1.ProductId = t2.ProductId " +
            "INNER JOIN PurchaseOrderItem as t3 " +
            "ON t1.PurchaseOrderItemId = t3.PurchaseOrderItemId " +
            "INNER JOIN Supplier as t4 " +
            "ON t1.SupplierId = t4.SupplierId " +
            "WHERE t1.PurchaseOrderId = @sid ORDER BY t2.Name";
 
        var quotesList = cn.Query<QuoteRequest, Product, PurchaseOrderItem, Supplier, QuoteRequest>(sql,
            (quo, prod, item, supplier) =>
            {
                quo.Product = prod;
                quo.PurchaseOrderItem = item;
                quo.Supplier = supplier;
                return quo;
            }, new { sid = purchaseId }, splitOn: "QuoteRequestId, ProductId, PurchaseOrderItemId, SupplierId");
 
        return quotesList;
    }
}

Line 24: Query method encapsulates a Func delegate and returns a value of the type specified by the last parameter-> In this case "QuoteRequest" is returned.

Line 31: Params "sid" and "splitOn"
sid -> Anonymous object that receive purchaseId as a parameter.
splitOn -> The splitOn param is specified with object Id because Dapper needs to know how to split the columns into objects.

Looking in Visual Studio, I can see the content of quotesList variable populated with all entities.

My next example returns a "Purchase Order" object and put it inside a collection of "Purchase Order Items".

PurchaseOrderRepository.cs

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
public class PurchaseOrderRepository
{
    protected DapperDemoContext Db;
 
    public PurchaseOrderRepository(DapperDemoContext context)
    {
        Db = context;
    }
 
    public IEnumerable<PurchaseOrder> GetItemsByPurchaseOrderId(int purchaseOrderId)
    {
        var cn = Db.Database.Connection;
        var sql = @"SELECT t1.PurchaseOrderId, t1.CreateDate, t1.Status " +
	    ",t2.PurchaseOrderItemId, t2.Quantity " +
            ",t3.ProductId, t3.Name " +
            "FROM PurchaseOrder as t1 " +
            "INNER JOIN PurchaseOrderItem as t2 " +
            "ON t1.PurchaseOrderId = t2.PurchaseOrderID " +
            "INNER JOIN Product as t3 " +
            "ON t2.ProductId = t3.ProductId " +
            "WHERE t1.PurchaseOrderId = @sid";
 
        var orderItemsList = cn.Query<PurchaseOrder, PurchaseOrderItem, Product, PurchaseOrder>(sql,
            (order, item, prod) =>
            {
                order.PurchaseOrderItems.Add(item);
                item.Product = prod;
                return order;
            }, new { sid = purchaseOrderId }, splitOn: "PurchaseOrderId, PurchaseOrderItemId, ProductId");
 
        return orderItemsList;
    }
}

Line 26: Adding a collection of items

See below my result set.

Stored Procedure
Instead of writing hard coded sql query, look at the following class that calls a stored procedure and returns the same result as previous example.

1
2
3
4
5
6
7
8
9
10
11
12
13
public IEnumerable<PurchaseOrder> spGetItemsByPurchaseOrderId(int purchaseOrderId)
{
    var cn = Db.Database.Connection;
    var orderItemsList = cn.Query<PurchaseOrder, PurchaseOrderItem, Product, PurchaseOrder>("spGetItemsByOrder",
        (order, item, prod) =>
        {
            order.PurchaseOrderItems.Add(item);
            item.Product = prod;
            return order;
        }, new { Id = purchaseOrderId }, splitOn: "PurchaseOrderId, PurchaseOrderItemId, ProductId", commandType: CommandType.StoredProcedure);
 
	return orderItemsList;
}

You can check more examples on Dapper's Github page and chek out my project on my Github - Dapper Demo sample on Github

Thanks for reading!

Leave a Reply

Your email address will not be published. Required fields are marked *

This blog is kept spam free by WP-SpamFree.