dapper-dot-net - Simple SQL object mapper for SQL Server - Google Project Hosting

Dapper - a simple object mapper for .Net

Features

Dapper is a single file you can drop 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

public static List<T> ExecuteMapperQuery<T>(this IDbConnection cnn, string sql, object param = null, SqlTransaction transaction = null)

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.ExecuteMapperQuery<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 List<dynamic> ExecuteMapperQuery (this IDbConnection cnn, string sql, object param = null, SqlTransaction transaction = null)

This method will execute SQL and return a dynamic list.

Example usage:

 var rows = connection.ExecuteMapperQuery("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 ExecuteMapperCommand(this IDbConnection cnn, string sql, object param = null, SqlTransaction transaction = null)

Example usage:

connection.ExecuteMapperCommand(@"  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);

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
Dapper ExecuteMapperQuery<Post>	49ms
PetaPoco	52ms	Can be faster
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)	52ms
Massive	52ms

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: http://code.google.com/p/dapper-dot-net/source/browse/PerformanceTests.cs , 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)

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

Advanced features

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

For example:

connection.ExecuteMapperQuery<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

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 two ConcurrentDictionary objects. These objects 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 lifecycle, it assumes the connection it gets is open AND has no existing datareaders enumerating (unless MARS is enabled)

Who is using this?

Dapper is in production use at Stack Overflow

via code.google.com

Quicker than subsonic.... a couple of other similar ORM's like this. more to follow

Posted via email from solution revolution