Repository files navigation

link

Dapper is aNuGet library that you can add in to your project that will extend yourIDbConnection interface.

It provides 3 helpers:

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

publicstaticIEnumerable<T>Query<T>(thisIDbConnectioncnn,stringsql,objectparam=null,SqlTransactiontransaction=null,boolbuffered=true)

Example usage:

publicclassDog{publicint?Age{get;set;}publicGuidId{get;set;}publicstringName{get;set;}publicfloat?Weight{get;set;}publicintIgnoredProperty{get{return1;}}}varguid=Guid.NewGuid();vardog=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);

publicstaticIEnumerable<dynamic>Query(thisIDbConnectioncnn,stringsql,objectparam=null,SqlTransactiontransaction=null,boolbuffered=true)

This method will execute SQL and return a dynamic list.

Example usage:

varrows=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);

publicstaticintExecute(thisIDbConnectioncnn,stringsql,objectparam=null,SqlTransactiontransaction=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);

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.

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 benchmarks are availablehere.

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'sRawDataAccessBencher test suite orOrmBenchmark.

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

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=newint[]{1,2,3}});

Will be translated to:

select*from(select1asId unionallselect2union allselect3)as Xwhere Idin(@Ids1,@Ids2,@Ids3)"// @Ids1 = 1 , @Ids2 = 2 , @Ids2 = 3

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.

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:

Consider 2 classes:Post andUser

classPost{publicintId{get;set;}publicstringTitle{get;set;}publicstringContent{get;set;}publicUserOwner{get;set;}}classUser{publicintId{get;set;}publicstringName{get;set;}}

Now let us say that we want to map a query that joins both the posts and the users table. Until now if we needed to combine the result of 2 queries, we'd need a new object to express it but it makes more sense in this case to put theUser object inside thePost object.

This is the user case for multi mapping. You tell dapper that the query returns aPost and aUser object and then give it a function describing what you want to do with each of the rows containing both aPost and aUser object. In our case, we want to take the user object and put it inside the post object. So we write the function:

(post,user)=>{post.Owner=user;returnpost;}

The 3 type arguments to theQuery method specify what objects dapper should use to deserialize the row and what is going to be returned. We're going to interpret both rows as a combination ofPost andUser and we're returning back aPost object. Hence the type declaration becomes

<Post,User,Post>

Everything put together, looks like this:

varsql=@"select * from #Posts pleft join #Users u on u.Id = p.OwnerIdOrder by p.Id";vardata=connection.Query<Post,User,Post>(sql,(post,user)=>{post.Owner=user;returnpost;});varpost=data.First();post.Content.IsEqualTo("Sams Post1");post.Id.IsEqualTo(1);post.Owner.Name.IsEqualTo("Sam");post.Owner.Id.IsEqualTo(99);

Dapper is able to split the returned row by making an assumption that your Id columns are namedId orid, if your primary key is different or you would like to split the wide row at point other thanId, use the optionalsplitOn parameter.

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

Example:

varsql=@"select * from Customers where CustomerId = @idselect * from Orders where CustomerId = @idselect * from Returns where CustomerId = @id";using(varmulti=connection.QueryMultiple(sql,new{id=selectedId})){varcustomer=multi.Read<Customer>().Single();varorders=multi.Read<Order>().ToList();varreturns=multi.Read<Return>().ToList();   ...}

Dapper fully supports stored procs:

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

If you want something more fancy, you can do:

varp=newDynamicParameters();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);intb=p.Get<int>("@b");intc=p.Get<int>("@c");

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=newDbString{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.

Usually you'll want to treat all rows from a given table as the same data type. However, there are some circumstances where it's useful to be able to parse different rows as different data types. This is whereIDataReader.GetRowParser comes in handy.

Imagine you have a database table named "Shapes" with the columns:Id,Type, andData, and you want to parse its rows intoCircle,Square, orTriangle objects based on the value of the Type column.

varshapes=newList<IShape>();using(varreader=connection.ExecuteReader("select * from Shapes")){// Generate a row parser for each type you expect.// The generic type <IShape> is what the parser will return.// The argument (typeof(*)) is the concrete type to parse.varcircleParser=reader.GetRowParser<IShape>(typeof(Circle));varsquareParser=reader.GetRowParser<IShape>(typeof(Square));vartriangleParser=reader.GetRowParser<IShape>(typeof(Triangle));vartypeColumnIndex=reader.GetOrdinal("Type");while(reader.Read()){IShapeshape;vartype=(ShapeType)reader.GetInt32(typeColumnIndex);switch(type){caseShapeType.Circle:shape=circleParser(reader);break;caseShapeType.Square:shape=squareParser(reader);break;caseShapeType.Triangle:shape=triangleParser(reader);break;default:thrownewNotImplementedException();}shapes.Add(shape);}}

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. It worries about the 95% scenario, and gives you the tools you need most of the time. It doesn't attempt to solve every problem.

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

Dapper has a comprehensive test suite in thetest project

Dapper is in production use at:

Stack Overflow,helpdesk

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