Dapper - a simple object mapper for .Net
C# Other
Failed to load latest commit information.
.nuget Update 3rd party libs; package for 1.39 Mar 18, 2015
Dapper.Contrib 1.50.2 Jul 22, 2016
Dapper.EntityFramework.StrongName Repackage Dapper.EF to fix System.Runtime; deploy as .52; fix xunit r… Jul 24, 2016
Dapper.EntityFramework Repackage Dapper.EF to fix System.Runtime; deploy as .52; fix xunit r… Jul 24, 2016
Dapper.Rainbow 1.50.2 Jul 22, 2016
Dapper.SqlBuilder 1.50.2 Jul 22, 2016
Dapper.StrongName 1.50.2 Jul 22, 2016
Dapper.Tests.Contrib Repackage Dapper.EF to fix System.Runtime; deploy as .52; fix xunit r… Jul 24, 2016
Dapper.Tests Merge branch 'master' of github.com:StackExchange/dapper-dot-net Jul 26, 2016
Dapper 1.50.2 Jul 22, 2016
tools .Net Core 1.0 RTM update Jun 28, 2016
.gitattributes .gitattributes: * text=auto Oct 15, 2015
.gitignore Update .gitgnore for build pathing Apr 17, 2016
Dapper.sln This .0 addition happens automatically...make it explicit instead. May 24, 2016
Dapper.sln.DotSettings Core: peripheral cleanup Nov 25, 2015
Dapper.snk Added Dapper.StrongName Sep 29, 2014
License.txt added license and github info Apr 14, 2011
NuGet.Config Failing test for #552 Jul 20, 2016
Readme.md Readme.md: update performance tests link Feb 20, 2016
appveyor.yml It's time for appveyor.yml May 19, 2016
build.ps1 Switch version suffix to be a build parameter May 24, 2016
build.sh Bash build: fix test runner on OS X Apr 17, 2016
game git blame game May 25, 2016
global.json .Net Core 1.0 RTM update Jun 28, 2016

Readme.md

Dapper - a simple object mapper for .Net

Release Notes

link

Features

Dapper is a NuGet library that you can add in to your project that will extend your IDbConnection interface.

It provides 3 helpers:

Execute a query and map the results to a strongly typed List

Note: all extension methods assume the connection is already open, they will fail if the connection is closed.

public static IEnumerable<T> Query<T>(this IDbConnection cnn, string sql, object param = null, SqlTransaction transaction = null, bool buffered = true)

Example usage:

public class Dog
{
    public int? Age { get; set; }
    public Guid Id { get; set; }
    public string Name { get; set; }
    public float? Weight { get; set; }

    public int IgnoredProperty { get { return 1; } }
}            

var guid = Guid.NewGuid();
var dog = connection.Query<Dog>("select Age = @Age, Id = @Id", new { Age = (int?)null, Id = guid });

dog.Count()
    .IsEqualTo(1);

dog.First().Age
    .IsNull();

dog.First().Id
    .IsEqualTo(guid);

Execute a query and map it to a list of dynamic objects

public static IEnumerable<dynamic> Query (this IDbConnection cnn, string sql, object param = null, SqlTransaction transaction = null, bool buffered = true)

This method will execute SQL and return a dynamic list.

Example usage:

var rows = connection.Query("select 1 A, 2 B union all select 3, 4");

((int)rows[0].A)
   .IsEqualTo(1);

((int)rows[0].B)
   .IsEqualTo(2);

((int)rows[1].A)
   .IsEqualTo(3);

((int)rows[1].B)
    .IsEqualTo(4);

Execute a Command that returns no results

public static int Execute(this IDbConnection cnn, string sql, object param = null, SqlTransaction transaction = null)

Example usage:

connection.Execute(@"
  set nocount on 
  create table #t(i int) 
  set nocount off 
  insert #t 
  select @a a union all select @b 
  set nocount on 
  drop table #t", new {a=1, b=2 })
   .IsEqualTo(2);

Execute a Command multiple times

The same signature also allows you to conveniently and efficiently execute a command multiple times (for example to bulk-load data)

Example usage:

connection.Execute(@"insert MyTable(colA, colB) values (@a, @b)",
    new[] { new { a=1, b=1 }, new { a=2, b=2 }, new { a=3, b=3 } }
  ).IsEqualTo(3); // 3 rows inserted: "1,1", "2,2" and "3,3"

This works for any parameter that implements IEnumerable for some T.

Performance

A key feature of Dapper is performance. The following metrics show how long it takes to execute 500 SELECT statements against a DB and map the data returned to objects.

The performance tests are broken in to 3 lists:

  • POCO serialization for frameworks that support pulling static typed objects from the DB. Using raw SQL.
  • Dynamic serialization for frameworks that support returning dynamic lists of objects.
  • Typical framework usage. Often typical framework usage differs from the optimal usage performance wise. Often it will not involve writing SQL.

Performance of SELECT mapping over 500 iterations - POCO serialization

Method Duration Remarks
Hand coded (using a SqlDataReader) 47ms Can be faster
Dapper ExecuteMapperQuery 49ms
ServiceStack.OrmLite (QueryById) 50ms
PetaPoco 52ms
BLToolkit 80ms
SubSonic CodingHorror 107ms
NHibernate SQL 104ms
Linq 2 SQL ExecuteQuery 181ms
Entity framework ExecuteStoreQuery 631ms

Performance of SELECT mapping over 500 iterations - dynamic serialization

Method Duration Remarks
Dapper ExecuteMapperQuery (dynamic) 48ms  
Massive 52ms
Simple.Data 95ms

Performance of SELECT mapping over 500 iterations - typical usage

Method Duration Remarks
Linq 2 SQL CompiledQuery 81ms Not super typical involves complex code
NHibernate HQL 118ms  
Linq 2 SQL 559ms  
Entity framework 859ms  
SubSonic ActiveRecord.SingleOrDefault 3619ms  

Performance benchmarks are available here.

Feel free to submit patches that include other ORMs - when running benchmarks, be sure to compile in Release and not attach a debugger (ctrl F5).

Alternatively, you might prefer Frans Bouma's RawDataAccessBencher test suite.

Parameterized queries

Parameters are passed in as anonymous classes. This allow you to name your parameters easily and gives you the ability to simply cut-and-paste SQL snippets and run them in Query analyzer.

new {A = 1, B = "b"} // A will be mapped to the param @A, B to the param @B 

List Support

Dapper allow you to pass in IEnumerable and will automatically parameterize your query.

For example:

connection.Query<int>("select * from (select 1 as Id union all select 2 union all select 3) as X where Id in @Ids", new { Ids = new int[] { 1, 2, 3 });

Will be translated to:

select * from (select 1 as Id union all select 2 union all select 3) as X where Id in (@Ids1, @Ids2, @Ids3)" // @Ids1 = 1 , @Ids2 = 2 , @Ids2 = 3

Buffered vs Unbuffered readers

Dapper's default behavior is to execute your sql and buffer the entire reader on return. This is ideal in most cases as it minimizes shared locks in the db and cuts down on db network time.

However when executing huge queries you may need to minimize memory footprint and only load objects as needed. To do so pass, buffered: false into the Query method.

Multi Mapping

Dapper allows you to map a single row to multiple objects. This is a key feature if you want to avoid extraneous querying and eager load associations.

Example:

var sql = 
@"select * from #Posts p 
left join #Users u on u.Id = p.OwnerId 
Order by p.Id";

var data = connection.Query<Post, User, Post>(sql, (post, user) => { post.Owner = user; return post;});
var post = data.First();

post.Content.IsEqualTo("Sams Post1");
post.Id.IsEqualTo(1);
post.Owner.Name.IsEqualTo("Sam");
post.Owner.Id.IsEqualTo(99);

important note Dapper assumes your Id columns are named "Id" or "id", if your primary key is different or you would like to split the wide row at point other than "Id", use the optional 'splitOn' parameter.

Multiple Results

Dapper allows you to process multiple result grids in a single query.

Example:

var sql = 
@"
select * from Customers where CustomerId = @id
select * from Orders where CustomerId = @id
select * from Returns where CustomerId = @id";

using (var multi = connection.QueryMultiple(sql, new {id=selectedId}))
{
   var customer = multi.Read<Customer>().Single();
   var orders = multi.Read<Order>().ToList();
   var returns = multi.Read<Return>().ToList();
   ...
} 

Stored Procedures

Dapper fully supports stored procs:

var user = cnn.Query<User>("spGetUser", new {Id = 1}, 
        commandType: CommandType.StoredProcedure).SingleOrDefault();

If you want something more fancy, you can do:

var p = new DynamicParameters();
p.Add("@a", 11);
p.Add("@b", dbType: DbType.Int32, direction: ParameterDirection.Output);
p.Add("@c", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

cnn.Execute("spMagicProc", p, commandType: CommandType.StoredProcedure); 

int b = p.Get<int>("@b");
int c = p.Get<int>("@c"); 

Ansi Strings and varchar

Dapper supports varchar params, if you are executing a where clause on a varchar column using a param be sure to pass it in this way:

Query<Thing>("select * from Thing where Name = @Name", new {Name = new DbString { Value = "abcde", IsFixedLength = true, Length = 10, IsAnsi = true });

On SQL Server it is crucial to use the unicode when querying unicode and ansi when querying non unicode.

Limitations and caveats

Dapper caches information about every query it runs, this allow it to materialize objects quickly and process parameters quickly. The current implementation caches this information in a ConcurrentDictionary object. The objects it stores are never flushed. If you are generating SQL strings on the fly without using parameters it is possible you will hit memory issues. We may convert the dictionaries to an LRU Cache.

Dapper's simplicity means that many feature that ORMs ship with are stripped out, there is no identity map, there are no helpers for update / select and so on.

Dapper does not manage your connection's lifecycle, it assumes the connection it gets is open AND has no existing datareaders enumerating (unless MARS is enabled)

Will Dapper work with my DB provider?

Dapper has no DB specific implementation details, it works across all .NET ADO providers including SQLite, SQL CE, Firebird, Oracle, MySQL, PostgreSQL and SQL Server.

Do you have a comprehensive list of examples?

Dapper has a comprehensive test suite in the test project

Who is using this?

Dapper is in production use at:

Stack Overflow, helpdesk

(if you would like to be listed here let me know)