Dapper.Net by example

Auteur : Jeroen Kuiper Geplaatst op : 6-1-2012 00:00
When the team behind StackOverflow released the mini-ORM Dapper, we were enthousiastic. An ORM with performance in mind! 

Microsoft's Entity Framework is still lagging behind and feels (is) like a beast. NHibernate is a beast as well, but is usally fast with second-level caching. For simple applications or scheduled tasks, it just too much. And for poorly designed legacy databases, it works against you. When you just need to write SQL queries and want to map the results to objects, a mini ORM suffices. 

StackOverflow is one of the most responsive sites I know, so its ORM performance's is proven. Dapper's documentation however is somewhat sparse.

Basic usage

Download the single file SqlMapper.cs and dump it in your project.
Put using Dapper in your file with queries, because Dapper extends the normal IDbConnection interface (which is somewhat of a bad practice imho).
Use your favorite way (Dependency Injection of course) of providing a IDbConnection connection named conn,just as you normally would when using a ADO.NET. Basically like so:
                using (var conn = new SqlConnection(myConnectionString)) {
                    conn.Open();
                    ....
               }

A list of objects

Select a list of accounts from a certain webshop.
IEnumerable<Account> resultList = conn.Query<Account>(@"
                    SELECT * 
                    FROM Account
                    WHERE shopId = @ShopId", 
new {  ShopId = shopId });
The Account object is for example.
public class Account {
  public int? Id {get;set;}
  public string Name {get;set;}
  public string Address {get;set;}
  public string Country {get;set;}
  public int ShopId {get; set;}
}
Note that eventhough we use SELECT *, not all fields have to be present as class properties.

A single object

Account result = conn.Query<Account>(@"
                    SELECT * 
                    FROM Account
                    WHERE Id = @Id", 
   new {  Id = Id }).FirstOrDefault();

A dynamic object

If you're too lazy to type out a class, you can use a dynamic object. 
dynamic account = conn.Query<dynamic>(@"
                    SELECT Name, Address, Country
                    FROM Account
		    WHERE Id = @Id", new { Id = Id }).FirstOrDefault();
Console.WriteLine(account.Name);
Console.WriteLine(account.Address);
Console.WriteLine(account.Country);
Nice! Probably somewhat slower than if you type out the class.

A list of objects with single child object (multimap)

Imagine we want the Shop data with the Accounts as well. It is a legacy database, so the Shop's Id is named ShopId instead of Id. This can be overcome with the  'splitOn' option.
public class Account {
  public int? Id {get;set;}
  public string Name {get;set;}
  public string Address {get;set;}
  public string Country {get;set;}
  public int ShopId {get; set;}
  public Shop Shop {get;set;}
}
public class Shop {
  public int? ShopId {get;set;}
  public string Name {get;set;}
  public string Url {get;set;}
}

var resultList = conn.Query<Account, Shop, Account>(@"
                    SELECT a.Name, a.Address, a.Country, a.ShopId
                            s.ShopId, s.Name, s.Url
                    FROM Account a
                    INNER JOIN Shop s ON s.ShopId = a.ShopId                    
                    ", (a, s) => {
                         a.Shop = s;
                         return a;
                     },
                     splitOn: "ShopId"
                     ).AsQueryable();


A parent object with its child objects

And the other way around: find the shop with all its accounts. It's a little more complicated, as each row is given as (Shop s, Account a), but Shop s is a new object every time. So we have to remember one shop to add all accounts to.



public class Shop {
  public int? Id {get;set;}
  public string Name {get;set;}
  public string Url {get;set;}
  public IList<Account> Accounts {get;set;}
}

public class Account {
  public int? Id {get;set;}
  public string Name {get;set;}
  public string Address {get;set;}
  public string Country {get;set;}
  public int ShopId {get;set;}
}

var lookup = new Dictionary<int, Shop>()
conn.Query<Shop, Account, Shop>(@"
                    SELECT s.*, a.*
                    FROM Shop s
                    INNER JOIN Account a ON s.ShopId = a.ShopId                    
                    ", (s, a) => {
                         Shop shop;
                         if (!lookup.TryGetValue(s.Id, out shop)) {
                             lookup.Add(s.Id, shop = s);
                         }
                         if (shop.Accounts == null) 
                             shop.Accounts = new List<Account>();
                         shop.Accounts.Add(a);
                         return shop;
                     },
                     ).AsQueryable();

var resultList = lookup.Values;

I got this dapper test ParentChildIdentityAssociations:   https://code.google.com/p/dapper-dot-net/source/browse/Tests/Tests.cs#1343


Insert and update

Insert and update is not part of the default Dapper file. However, these is an extension for these functions -- which usually are the most tedious to maintain, so it is more than welcome.

Mark you identifier with [KeyAttribute]
public class Account {
  [KeyAttribute]
  public int? Id {get;set;}
  public string Name {get;set;}
  public string Address {get;set;}
  public string Country {get;set;}
  public int ShopId {get; set;}
}
And then you can create a simple Persist function:
        public void Persist(IDbConnection conn, Account acc) {
            if (acc.Id == null) {
                SqlMapperExtensions.Insert(conn, acc);
            }
            else {
                SqlMapperExtensions.Update(conn, acc);
            }
        }



Interested in more blogposts?

Add your mail address below and you'll be notified about our new developer posts.
*  Your Email Address:
 
 

Reacties

Jeroen
10-2-2014 13:40

Hi Randy, I think I meant this obscures Dapper functionality by mixing it with the default functionality. I hope this is sufficient reason for you.

Randy
14-12-2013 22:14

I'd be curious to know what your justification for this statement is: "...because Dapper extents the normal IDbConnection interface (which is somewhat of a bad practice imho)." One should not make such statements without providing reasoning. I'm not saying that you're right or wrong, I'd just like to know what you mean by that.

kom bij ons werken

 

 

Nog meer leren?

Wil jij meer leren terwijl je aan mooie projecten werkt?

 

Kom dan bij ons in Leiden werken als :

- Medior C# .Net Developer

- Junior C# .Net Developer

Plaats een reactie

Vul hieronder de * verplichte velden in. Reacties zullen pas zichtbaar worden wanneer ze goedgekeurd zijn.




"> %>