Friday, 22 April 2011

How to use Dapper & PetaPoco, Micro ORMs

With Microsoft now firmly pushing Entity Framework (currently at version 4) as their preferred data access solution, it’s refreshing to see a couple of new players on the scene albeit perhaps not in direct competition.

Dapper (Dynamic Query Mapper) and PetaPoco are Mirco ORMs.  An ORM is an Object Relational Mapper which sit between your business logic and database to abstract away the direct link between the two.  Micro ORMs are a simplier and more efficient option to larger ORM frameworks such as EF4 and NHibernate.

In this demo (full source code downloadable from the links at the end of this article) I’ll be giving examples of how to use each of the frameworks against a SQL Server database for your CRUD (create/insert, read/select, update, delete) commands.  I’m using the tempdb database so there' is no need to run any database scripts.

It’s important to note that for performance you still get a marginal benefit creating a hand coded DAL with SQLConnection, SQLCommand and all those SQLParameter objects like this:

public Customer Select()
var customer = null;

using (var connection = SqlHelper.GetOpenConnection())
using (var command = new SqlCommand(SqlHelper.ReadStatement, connection)
{ CommandType = CommandType.Text })
command.Parameters.Add("@Id", SqlDbType.Int);
command.Parameters[0].Value = SqlHelper.TestId;

var reader = command.ExecuteReader();

while (reader.Read())
customer = new Customer()
CustomerId = reader.GetString(0),
CompanyName = reader.GetString(1)


return customer;

But to hand code your own entire DAL is pretty time consuming and hard to maintain particularly when you’ve got commands with plenty of parameters.  Now you could use T4 to generate your DAL based on your database schema and that’s probably still your best bet if performance is everything to your application.  Or, for a minimal perf overhead (most LOB apps wouldn’t notice) you could use one of our new Micro ORMs!

To carry out the same task as the hand coded DAL above, Dapper requires only two lines of code:

var mapperConnection = SqlHelper.GetOpenConnection();
mapperConnection.Query<Customer>(SqlHelper.ReadStatement, new { Id = SqlHelper.TestId });

Note: This is using the (customer) typed Query, you can also use a dynamic version that omits the generic parameter of Customer to return a Dynamic Object.  Dynamic Objects were introduced in .NET Framework 4.

For PetaPoco only three lines of code are required:

var petaPoco = new PetaPoco.Database(SqlHelper.ConnectionString, "System.Data.SqlClient");
var customer = petaPoco.Fetch<Customer>(SqlHelper.ReadStatement, SqlHelper.TestId);

Note: This example is using the default execution options, you can also tweak the options to boost performance shown later in this blog.

You can see from a RAD point of view Micro ORMs really come into their own compared to hand coding your DAL.  Since the start of .NET plenty of frameworks have been created to generate your Sql commands and parameters (based on your DAL method attributes or parameters) but these Micro ORM frameworks are special. 

Firstly, other than one file for each framework no other code is required to pollute your solution.  No attributes clogging up your DAL or DTOs or Business Layers.  But now for the really special bit, both frameworks are actually generating code dynamically using Dynamic Methods and ILGenerators which is pretty cool and inspiring stuff.  The source code for both frameworks are included in the source code download to this blog post and I encourage you to take a look – kudos to the guys that put these frameworks together!

In terms of performance, the full Dapper project (link also at the end of the post) includes a test project that runs a simple select statement 500 times and compares performance against various data access solutions, the results of which can be seen here:


Note: Dapper is named as Mapper Query when using the typed/generic read method and Dynamic Mapper Query when using the non generic method.

2nd Note: This code was produced on a travel netbook (I’m writing this blog post whilst stuck in Delhi airport for the night being eaten alive by mosquitos :) ).  I would expect the figures to be much faster on a newer PC or laptop.

Examples for C, R, U and D code


private static void DapperExamples()
Console.WriteLine("Running Dapper examples");

SqlConnection mapperConnection = SqlHelper.GetOpenConnection();

Id = SqlHelper.TestId,
CompanyName = SqlHelper.InsertCompanyName

// READ Mapper Query
IEnumerable<Customer> customer1 =
mapperConnection.Query<Customer>(SqlHelper.ReadStatement, new {Id = SqlHelper.TestId});

// READ Dynamic Object Mapper Query
var customer2 = mapperConnection.Query(SqlHelper.ReadStatement, new {Id = SqlHelper.TestId});

Id = SqlHelper.TestId,
CompanyName = SqlHelper.UpdateCompanyName

mapperConnection.Execute(SqlHelper.DeleteStatement, new {Id = SqlHelper.TestId});


private static void PetaPocoExamples()
Console.WriteLine("Running PetaPoco examples");

var petapoco = new Database(SqlHelper.ConnectionString, "System.Data.SqlClient");

petapoco.Execute(SqlHelper.CreateStatementWithIndexedParams, SqlHelper.TestId, SqlHelper.InsertCompanyName);

// READ with all default options
List<Customer> customer1 = petapoco.Fetch<Customer>(SqlHelper.ReadStatementWithIndexedParams,

// READ with some "smart" functionality disabled
var petapocoFast = new Database(SqlHelper.ConnectionString, "System.Data.SqlClient");
petapocoFast.EnableAutoSelect = false;
petapocoFast.EnableNamedParams = false;
petapocoFast.ForceDateTimesToUtc = false;
List<Customer> customer2 = petapocoFast.Fetch<Customer>(SqlHelper.ReadStatementWithIndexedParams,

petapoco.Execute(SqlHelper.UpdateStatementWithIndexedParams, SqlHelper.TestId, SqlHelper.UpdateCompanyName);

petapoco.Execute(SqlHelper.DeleteStatementWithIndexedParams, SqlHelper.TestId);

SqlHelper class

internal static class SqlHelper
public static readonly string ConnectionString =
@"Data Source=.;Initial Catalog=tempdb;Integrated Security=True";

public static readonly string CreateStatement =
@"INSERT dbo.Customers (CustomerID, CompanyName) SELECT @id, @companyName";

public static readonly string ReadStatement = @"SELECT * FROM dbo.Customers WHERE CustomerId = @id";

public static readonly string UpdateStatement =
@"UPDATE dbo.Customers SET CompanyName = @companyName WHERE CustomerId = @id";

public static readonly string DeleteStatement = @"DELETE FROM dbo.Customers WHERE CustomerId = @id";

// PetaPoco parameters are named based on index
public static readonly string CreateStatementWithIndexedParams =
@"INSERT dbo.Customers (CustomerID, CompanyName) SELECT @0, @1";

public static readonly string ReadStatementWithIndexedParams =
@"SELECT * FROM dbo.Customers WHERE CustomerId = @0";

public static readonly string UpdateStatementWithIndexedParams =
@"UPDATE dbo.Customers SET CompanyName = @1 WHERE CustomerId = @0";

public static readonly string DeleteStatementWithIndexedParams =
@"DELETE FROM dbo.Customers WHERE CustomerId = @0";

public static readonly string TestId = "TEST";
public static readonly string InsertCompanyName = "Inserted company";
public static readonly string UpdateCompanyName = "Updated company";

internal static void EnsureDbSetup()
using (var cnn = GetOpenConnection())
var cmd = cnn.CreateCommand();
cmd.CommandText = @"
if (OBJECT_ID('Customers') is null)
CREATE TABLE [dbo].[Customers](
[CustomerID] [nchar](5) NOT NULL,
[CompanyName] [nvarchar](40) NOT NULL,
[ContactName] [nvarchar](30) NULL,
[ContactTitle] [nvarchar](30) NULL,
[Address] [nvarchar](60) NULL,
[City] [nvarchar](15) NULL,
[Region] [nvarchar](15) NULL,
[PostalCode] [nvarchar](10) NULL,
[Country] [nvarchar](15) NULL,
[Phone] [nvarchar](24) NULL,
[Fax] [nvarchar](24) NULL)
cmd.Connection = cnn;

public static SqlConnection GetOpenConnection()
var connection = new SqlConnection(ConnectionString);
return connection;

SQL Profiler

Using SQL Profiler we can view the TSQL that is being sent by Dapper and PetaPoco:


I hope you find this article helpful, happy coding!


Blog source code (including source for Dapper and PetaPoco):

Dapper (includes performance tests comparing Data Access solutions):



  1. Great post. Definately interested in using a micro-ORM...

    How well do you think dapper works with .NET 3.5?

    The link to your source code doesn't appear to work. I took out the www in the file path and i found a file, but it doesn't have the example code...

  2. Hi Kevin,

    Both work frameworks use the System.Dynamic. Expando object from .NET 4.0 so they won't immediately work out of the box with .NET 3.5. You have access to the source code so you could strip this part out if you wanted to.

    Dapper has recently updated it's interface so I've updated the blog post to reflect the change.

    My sample project now uses tempdb rather than Northwind so you run the code straight away. Oh and I fixed the link, thanks for the heads up :)


  3. we actually just backported dapper to .net 3.5, see the source...

  4. Steven, thanks for the post! Happy to hear the word get out :)

  5. hi, i have a question, i hope you can help me.
    i just start with and petapoco, actually its my first approach.
    so i want to know how to display the info obtained from the example
    List customer1 = petapoco.Fetch(SqlHelper.ReadStatementWithIndexedParams,

    so i want to print the info gathered from customer 1, but didnt
    i use response.write(customer1);

    thanks in advance

    1. the method is Fetch so return type is collection/list, better to use foreach or anyother looping mechanism

  6. Hi, Examples with Petapoco subquery available...? I need to assigning Count(*) records to subqery to the PetaPoco Result column, but it always returns to 0 ....With the documentaion of Petapoco there is not any example to assign with result column of subquery count only main query count assingning to result columns availalble...


  7. Great post. Thanks a lot!

  8. Really Nice Information,Thank You Very Much For Sharing.
    Web Development Company

  9. Really Nice Information,Thank You Very Much For Sharing.
    Web Development Company